Solved can a if formula handle 16 nested if formulas

July 7, 2011 at 14:22:49
Specs: Windows XP
=IF($T$4>=$A$13,$T$4*$C$13,IF($T$4>=$A$12,$T$4*$C$12,IF($T$4>=$A$11,$T$4*$C$11,IF($T$4>=$A$10,$T$4*$C$10,IF($T$4>=$A$9,$T$4*$C$9,IF($T$4>=$A$8,$T$4*$C$8,IF($T$4>=$A$7,$T$4*$C$7,IF($T$4>=$A$6,$T$4*$C$6,$T$4*$C$5)))))))).

I want to add more rows all the way to if$T$4>=$A$20, $T$4*$C$20. There will be altogether 16 nested IF's. Will 2003 Excel be able to handle that?


See More: can a if formula handle 16 nested if formulas

Report •


#1
July 7, 2011 at 16:07:00
2003 can not handle 16 Nested IF's. The limit is 7.

2007 and above can handle 16 (I think) but there are typically better ways to accomplish whatever it is you are trying to do.

I think VLOOKUP should work for you.

If your data looks something like this:

	
	A	B	C
5	1		11
6	2		22
7	3		33
8	4		44
9	5		55
10	6		66
11	7		77
12	8		88
13	9		99
14	10		11
15	11		222
16	12		333
17	13		444
18	14		555
19	15		666
20	16		777

Then this should work if T4 will always be an exact match of a value in A5:A20.

=$T$4 * VLOOKUP($T$4,$A$5:$C$20, 3, 0)

If T4 might be something like 6.6 or 22.35, then try this:

=$T$4 * VLOOKUP($T$4,$A$5:$C$20, 3, 1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 7, 2011 at 16:36:30
No, it is a range. Basically, what is saying is: if it is more than 1, choose 362, if it is more than 6, choose 278, etc.

1 1 to 5 362
6 6 to 10 278
11 11 to 15 194
16 16 to 20 156
21 21 to 35 117
36 36 to 50 81
51 51 to 65 45
66 66 to 75 40
76 76 to 100 35
101 101 to 125 30
126 126 to 150 27
151 151 to 175 24
176 176 to 200 21
201 201 to 250 19
251 251 to 300 17
300 300+ 15


Report •

#3
July 7, 2011 at 18:46:55
✔ Best Answer
Imagine actually trying the suggested formulas and seeing if one of them works...

=$T$4 * VLOOKUP($T$4,$A$5:$C$20, 3, 1)

Works for me.

1 returned 362
66 returned 2640
213 returned 4047

Look up the VLOOKUP formula in the Excel help files and you'll see why it works.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 8, 2011 at 09:28:57
Thanks. It works as magic. :)

Report •


Ask Question