=IF(I6-E7=0,B3)+D7+I5
created the above formula where if I6-E7=0 to place the value of B3 plus values in D7 and I5. That works, however, my data also has other possibilities other than 0; 1, 2 , 3 ,4, & 5. For each possibility wanting to associate a different value for the result, say if I6-E7= 1, C3, etc. The D7 and I5 additions remain constant. Getting nothing but errors in trying to add the additional IF statements. Any help?

You need to nest the IF statements:

=IF(I6-E7=0,B3,IF(I6-E7=1,C3,IF(I6-E7=2,D3)+D7+I5)+D7+I5)+D7+I5)

etc.

Adjust as necessary. You can have a maximmun of seven nested IF statements (although there are ways to get around that limit).

was wondering if somebody out there would be able to help me. Currently working with a spreadsheet that has date values (ex. 3/2/02), wanted to find out how to use an IF function to get excel to recognize a date as being in a specific quarter....for example

10/1-12/31 = Q1
1/1-3/31 = Q2
4/1-6/30 = Q3
7/1-9/30 = Q4

The formula i came up with is below:

=IF(D105>9/30,"Q1", IF(D105>12/31,"Q2",IF(D105>3/31, "Q3", IF(D105>6/30, "Q4"))))

D105 is the date (3/15/2002)....using the formula, the end result is "Q1" which is incorrect. (The result for all dates is "Q1" using the current formula).

What am I doing wrong?? Please help!!

rework your formula to include months
as what you have in your first statement is looking for everything over 9/30 i.e. 0.3
and all your dates will be over this mark

try something like
=IF(MONTH(D105)<=3,"Q2",IF(MONTH(D105)<=6,"Q3",IF( MONTH(D105)<=9,"Q4","Q1")

i cant remember if there is a quarters function within excel but you could work with that if there is (I use star office at home),

HTH
Dave

Same question was asked in previous thread.

We came up with approximately the same answer.

http://www.dbforums.com/t1119633.html

When Excel calculats the dates isnt it using the number format? Like may 1, 2007 is really "3423432" or whatever? Does this make a difference in the computation?

-C.W.
Excel Statement

I worked out the formula below to calculate the quarter. If someone can think of why it might fail, give your advice on this. Attached is proof that the formula works for every day of the year.
Jerry

="Q" & INT(MONTH(A1)/3.1)+1

Hi, I was trying to figuire out this whole If thing and after your post I could at least understand something, thanks a lot you guys!



You can switch encoding parameters of end files such as example frequencyp

Attached is my alternate solution to the nested IF statements question.
Use of the OFFSET function makes the formula shorter than nested IFs.

As I understand the question, (I6-E7) will always be a value between 0 and 5, and these 6 categories will be used to reference values in columns B through G. The sum of (+D7+I5) is always included in the result.

The formula:
=OFFSET(A15,-12, (I6-E7)+1,1,1)+D7+I5