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

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 777Then 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.

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

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 4047Look 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.

Thanks. It works as magic. :)

Ask Your Question

Weekly Poll