Excel- Nested IF/AND Function

February 10, 2009 at 14:02:18
Specs: Windows XP, 1GB

OK got a problem ive been pounding at for some time now with no luck. I am having trouble creating nested IF functions for excel. Here is the list and the desired result to be posted in the cell.

A1:
15-22 = "EX20"
23-28 = "EX24"
29-34 = "EX32"
35-40 = "EX36"
41-48 = "EX44"
49-52 = "EX52"

Example: if cell A1 contains a number between 15-22 EX20 will show in the cell the formula is placed. But is 23-28 is in cell A1 EX24 will show.


This is what I have come up with so far and I must be inputting the formula incorrectly because it says I have too many arguments.
=IF(AND(A1>14,A1<23),"EX20",AND(A1>22,A1<29), "EX24")

Lastly is it true you can only nest 7 IF functions in one cell? Thanks



See More: Excel- Nested IF/AND Function

Report •


#1
February 10, 2009 at 20:17:35

Here's what you're looking for:

=IF(AND(A1>14,A1<23),"EX20",IF(AND(A1>22,A1<29),"EX24",IF(AND(A1>28,A1<35),"EX32",IF(AND(A1>34,A1<41),"EX36",IF(AND(A1>40,A1<49),"EX44",IF(AND(A1>48,A1<53),"EX52"))))))


Report •

#2
February 10, 2009 at 20:37:12

Also...

You can do this:

Matrix starting at D1:

15 EX20
23 EX24
29 EX32
35 EX36
41 EX44
49 EX52

Then use this code:

=OFFSET(E1,MATCH(A1,D1:D6)-1,,)

This approach eliminates nested if statements, they are confusing to debug...


Report •

#3
February 10, 2009 at 21:17:10

re: This approach eliminates nested if statements, they are confusing to debug...

Just an FYI for those that aren't aware of these 2 debugging tools:

The "Evaluate Formula" feature under Formula Auditing under the Tools menu can be very useful in debugging Nested If's and other complicated formulae.

Single stepping through a formula can be very enlightening.

You can also highlight a section of a formula in the formula bar and hit F9 to see the result of just that section. The highlighted section must be something that Excel could evaluate if it could stand alone.

For example, in the suggested solution:

=IF(AND(A1>14,A1<23),"EX20",IF(AND(A1>22,A1<29),"EX24",IF(AND(A1>28,A1<35),"EX32",IF(AND(A1>34,A1<41),"EX36",IF(AND(A1>40,A1<49),"EX44",IF(AND(A1>48,A1<53),"EX52"))))))

you could highlight AND(A1>14,A1<23) and hit F9 to see if it was TRUE or FALSE

Undo or Ctrl-z returns the formula to its original form.


Report •

Related Solutions

#4
February 11, 2009 at 11:25:21

quackadilly - Thanks for the help the formula you provided works perfectly. After seeing your example I feel more comfortable making my own.

DerbyDad03 - Thats a useful tool ill use thanks for the heads up.

I still have the other question:

is it true you can only nest 7 IF functions in one cell?


Report •

#5
February 12, 2009 at 02:10:08

yes, on excel 2003 there are only 7 levels of nesting. Excel 07 can go further (not 100% sure how far, but it is a long way) :)

Report •

#6
February 12, 2009 at 09:30:26

jon k - Thanks for the answer might upgrade if thats the case.

Report •

#7
February 12, 2009 at 12:56:28

There are workarounds for the 7 nested-if limits in pre-2007 versions.

Google something like Excel 7 nested if for some suggestions.


Report •

#8
February 12, 2009 at 13:10:43

DerbyDad03- Ill look into it. Thanks.

Report •


Ask Question