Prev: countif
Next: Copying daily data from one tab to a weekly summary table
From: CAMPLI on 2 Jun 2010 21:57
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.
From: Mike Middleton on 2 Jun 2010 22:25
CAMPLI -

> WHAT IS THE SOLUTION FOR THIS <

VLOOKUP

- Mike
http://www.MikeMiddleton.com
Mike(a)DecisionToolworks.com


"CAMPLI" <CAMPLI(a)discussions.microsoft.com> wrote in message
news:F9411040-FD20-4358-9729-DAB249E8A542(a)microsoft.com...
> 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.

From: Ron Rosenfeld on 2 Jun 2010 22:25
On Wed, 2 Jun 2010 18:57:01 -0700, CAMPLI
<CAMPLI(a)discussions.microsoft.com> wrote:

>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.
From: Steve Dunn on 3 Jun 2010 06:48
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.


"Ron Rosenfeld" <ron(a)nospam.net> wrote in message
news:gc4e06hl888jbho3o04cs7b27v5qf285c1(a)4ax.com...
> On Wed, 2 Jun 2010 18:57:01 -0700, CAMPLI
> <CAMPLI(a)discussions.microsoft.com> wrote:
>
>>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.

 | 
Pages: 1
Prev: countif
Next: Copying daily data from one tab to a weekly summary table