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

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"))))))

Also... You can do this:

Matrix starting at D1:

15 EX20

23 EX24

29 EX32

35 EX36

41 EX44

49 EX52Then use this code:

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

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

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.

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?

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) :)

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

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

Excel 7 nested iffor some suggestions.

DerbyDad03- Ill look into it. Thanks.

Ask Your Question

Weekly Poll

Do you think broadband providers should eliminate all data caps?

Discuss in The Lounge

Poll History