entering more than seven levels of functions with in a function

[CAMPLI]

I WANT TO PUT MORE THAN SEVEN LEVELS OF FUNCTIONS WITH A FUNCTION. FOR
EXAMPLE
=IF(B14=10000,14500,IF(B14=10470,15100,IF(B14=10940,15700,IF(B14=11410,16300,IF(B14=11880,16900,IF(B14=12350,17500,IF(B14=12820,18100,IF(B14=13320,18700,IF(B14=13820,19400,IF(B14=14320,20100,IF(B14=14880,20900,IF(B14=15440,21700,IF(B14=16000,22500,IF(B14=16560,23300,IF(B14=17120,24100,IF(B14=17680,24900,IF(B14=18240,25700,IF(B14=18800,26500,IF(B14=19360,27300,IF(B14=19920,28100,IF(B14=20480,28900,IF(B14=21040,29700,IF(B14=21660,30600,IF(B14=22280,31500,IF(B14=22900,32400,IF(B14=23520,33300,0))))))))))))))
BUT EXCEL IS NOT ALLOWING ME TO PUT MORE THAN SEVEN FUNCTIONS IN A FUNCTION.
THEN WHAT I SHOULD DO. WHAT IS THE SOLUTION FOR THIS.

 

[Mike Middleton]

CAMPLI -

WHAT IS THE SOLUTION FOR THIS <

VLOOKUP

- Mike
http://www.MikeMiddleton.com
(e-mail address removed)

 

[Ron Rosenfeld]

I WANT TO PUT MORE THAN SEVEN LEVELS OF FUNCTIONS WITH A FUNCTION. FOR
EXAMPLE
=IF(B14=10000,14500,IF(B14=10470,15100,IF(B14=10940,15700,IF(B14=11410,16300,IF(B14=11880,16900,IF(B14=12350,17500,IF(B14=12820,18100,IF(B14=13320,18700,IF(B14=13820,19400,IF(B14=14320,20100,IF(B14=14880,20900,IF(B14=15440,21700,IF(B14=16000,22500,IF(B14=16560,23300,IF(B14=17120,24100,IF(B14=17680,24900,IF(B14=18240,25700,IF(B14=18800,26500,IF(B14=19360,27300,IF(B14=19920,28100,IF(B14=20480,28900,IF(B14=21040,29700,IF(B14=21660,30600,IF(B14=22280,31500,IF(B14=22900,32400,IF(B14=23520,33300,0))))))))))))))
BUT EXCEL IS NOT ALLOWING ME TO PUT MORE THAN SEVEN FUNCTIONS IN A FUNCTION.
THEN WHAT I SHOULD DO. WHAT IS THE SOLUTION FOR THIS.

For your problem, you could use VLOOKUP.

You could set up a table someplace. Given your values:

10000 14500
10470 15100
10940 15700
11410 16300
11880 16900
12350 17500
12820 18100
13320 18700
13820 19400
14320 20100
14880 20900
15440 21700
16000 22500
16560 23300
17120 24100
17680 24900
18240 25700
18800 26500
19360 27300
19920 28100
20480 28900
21040 29700
21660 30600
22280 31500
22900 32400
23520 33300

Then use:

=VLOOKUP(B14,Tbl,2,FALSE)

Where Tbl refers to the range where your table is stored.

Note that your IF formula leaves undefined any value of B14 that is
not exactly equal to a value in column 1.

The function will return #N/A in that event.

If that is not what you really want, change the FALSE to TRUE in the
VLOOKUP formula; and also look at HELP for VLOOKUP to understand what
that does.

 

[Steve Dunn]

I would also use VLOOKUP with a table (much easier to handle), but if you
insist on a lone function:

=LOOKUP(B14,{1000,10470,10940,11410,11880,12350,12820,
13320,13820,14320,14880,15440,16000,16560,17120,17680,18240,
18800,19360,19920,20480,21040,21660,22280,22900,23520},
{14500,15100,15700,16300,16900,17500,18100,18700,19400,
20100,20900,21700,22500,23300,24100,24900,25700,26500,
27300,28100,28900,29700,30600,31500,32400,33300})

HTH
Steve D.