1. Registered User
Join Date
Feb 2004
Location
CHICAGO METRO
Posts
1

=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?

2. Registered User
Join Date
Oct 2003
Posts
1,091
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).

3. Registered User
Join Date
Mar 2005
Posts
25
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).

4. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
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

5. Registered User
Join Date
Mar 2004
Location
Minnesota, USA, Earth
Posts
65

We came up with approximately the same answer.

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

6. Registered User
Join Date
Apr 2011
Posts
4

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

7. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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

8. Registered User
Join Date
May 2011
Posts
1

## Thanks

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
Last edited by Pat Phelan; 05-17-11 at 14:55. Reason: removed unrelated URL

9. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•