Home

Formulas and Functions

214 Conversations

Latest Activity

Custom List Message Item

Hi

I am sure I have done this before but I can't find it now.  I have a spreadsheet with three lists.  I want to combine the lists into one.  On the sheet attached I have three columns of names A, B and C.  In D, I would like to list all the same names in

... Read More
88 Views
10 Replies

Hi Ian,

 

Try this formula starting from D1 and copy it down

 

=IF(
	ROW()<=COUNTA(A:A),
		INDEX(A:A,ROW()),
		IF(ROW()>COUNTA(A:B),
			IF(ROW()<= COUNTA(A:C),INDEX(C:C,ROW()
... Read More

I'm finding an inconsistency with a formula I often use in my excel sheets. 


The formula is as follows:
=if(sum(a+b)>9,sum(a+b)-9,sum(a+b))
the above formula actually works quite well in certain instances, however It fails to reduce compound values to single

... Read More
66 Views
6 Replies

*


Joshua Conci wrote:

I'm trying to craft a formula that will reduce compound values such as "56" down to a single digit value of "2" by subtracting 9 from the compound val
... Read More
Best Response

I have a set of data with unique identifiers for each person, the dates and amounts of each amount given during a time frame.  Some people have given more than once in the time frame.  I want to obtain the most recent contribution for each person, showing

... Read More
38 Views
2 Replies

Hello,

 

you could build a pivot table with the person ID in the rows and the date in the values. Then set the value field to show Max. That will give you the person ID nex

... Read More
Buonasera,
scrivo per avere un aiuto da voi, questa mattina ho acquistato l'ultima versione di office 2016 pensando di avere le nuove funzioni logiche su Excel ... invece niente...
Qualcuno sa dirmi perché nonostante ho tutti i requisiti non mi compare la f... Read More
16 Views
0 Reply

I am wanting a cell to turn red 3 days prior to the date entered in the cell.  I have entered the following formula but the question mark is me asking you what I should enter here.  I am trying to ask 'if B2 is not empty'.  Please help!!!

 

=AND(B2<>?,B2<TO

... Read More
26 Views
1 Reply

Hi Lisa,

Please try this

=AND(B2>1,B2<TODAY()+3)

It returns FALSE if no date in B2

 

Greetings,

              Apparently I'm rustier than I thought with Excel. Can someone help with the steps in creating these spreadsheets? 

The following two problems should be completed in EXCEL. Show the steps you took to come to the result and return the

... Read More
87 Views
3 Replies

Hello,

 

while we can't do your homework or assignment for you, we're happy to help with specific questions.

 

In Excel, you can type text or numbers into any cell. If you ty

... Read More

Good Morning,

 

I want to do a nested if statement... but am writing the formula wrong I think...
basically I have a list of areas in NYC, Tribeca, midtown west, midtown east etc.. within each area there are also different prices.. based on the area and the

... Read More
39 Views
1 Reply

Hello,

consider the following screenshot:

 

A table contains the price range in ascending order in columns A and B. Across row 1 is a list or the areas. The body of the tabl

... Read More

Here is my array formula

 

{=IF(OR(COUNTBLANK(E3)),"",MIN(IF(K3:P3>TODAY(),K3:P3)))}

 

This formula is in the PAPERWORK DUE column Q

 

I want the cell to remain blank if the user has not yet entered in a date for the E column (or any column E-J)

 

Then I want it

... Read More
67 Views
5 Replies

Hi Heather,

 

I didn't test, my guess if you have something in E3 when MIN() works, and it returns 0 if one of K3:P3 is empty. Zero is Jan 00, 1900, thus you have in Q "Jan

... Read More

I have set up conditional formatting for COLUMN E, to have the font turn red when the date is past TODAY's date. What i need help with now is to ignore that formatting when the user enters a date into Column F.

 

I have the opposite effect right now with  

... Read More
52 Views
4 Replies

Hi @Heather Mosman,

 

Try this

 

=AND(E3<TODAY(),F3="")
Read More

Hello and thanks in advance for any help! 

I have a formula that is driving me mad. I'm sure it will be simple for anyone else.

 

The number in I4 will change.

The numbers in D19:D21 will change dependant on this.

I need the numbers in H19:H21 to show only if

... Read More
64 Views
6 Replies

Hi @Jo Samat,

 

Apply this in H19 Cell

=IF(AND(I4>=75,I4<190),D19,0)

Apply this in H20 Cell

=IF(AND(I4>=190,I4<560),D20,0)

I attached the same in file.

Read More

Hi Jo,

 

If your ranges are in Q and R columsn use for H19

 

=IF(($I$4>=$Q4)*($I$4<=$R4),D19,0)

and copy that cell down to H20 and H21.

 

Read More
Best Response

Hello,

 

My name is Tyler, i would for a company that does several studies in capibility. They want me to make a form so that when we paste in the data the conditional formatting will highlight cells out of spec. to do that i have to use 3 rules per column,

... Read More
75 Views
3 Replies

Hi Tyler,

 

I didn't catch exactly what's what in your tables, let do one "Red" rule. Assume upper bound for Tolerance is in row 11, different for each column. Left up corn

... Read More

Hello everyone!

I am working in a startup company within the real state industry. I would like to know if you could help me figure out a solution for this situation:

I have potential clients who will stay for a certain period of time (from.. to...)

I have la

... Read More
79 Views
5 Replies

Hi Katia,

 

To enter array formula you have to enter it with combination of Ctrl+Shift+Enter, not just Enter

Your formula will look like (English UK notation)

 

=INDEX(A2:D500
... Read More

Im Trying to write a formula in excel that would basically say: IF you find in row15 the cell showing !, THEN copy the date value (4/6) assigned at the top of that column in Row2 to a new cell. Im not knowledgeable enough on excel to write the formula in

... Read More
41 Views
2 Replies

Hello,

 

a formula cannot copy data to another cell. Can you explain to what cell you would like to copy the date?

Hi Tao,

 

Assuming you have one "!" in row 15, stay on the cell to which you would like to copy the data and enter

 

=LOOKUP("!",15:15,2:2)

 

Read More
The Excel file shows cities and what county they are in. Instead of it being seperated, I would like all the cities in one county such as
"West Palm Beach, Riviera Beach" in one cell and Palm Beach County as their county instead of them being separated.

Can... Read More
49 Views
2 Replies

Hi 00264657,

 

Do you need the output for Country as it is there for Zip codes for "West Palm Beach, FL" ?

I am referring cell D5011 as output for Zip codes.

 

Regards,

DILIPan

... Read More

Hello,

Can anyone please help me out?

How do I divide one column by another, making a percentage row?

 

 

 

 

33 Views
2 Replies

Hi Pico,

 

I believe it would be a simple division and then formatting the result cell as 'percentage'.

 

 

Regards,

DILIPandey

Read More

Hi, 

If i understood correctly you'd like to divide the value of the cell in one column on the value in the cell of another column, expand that on entire column and show r

... Read More