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 if for some suggestions.
DerbyDad03- Ill look into it. Thanks.
