A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

entering more than seven levels of functions with in a function



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 02:57 AM posted to microsoft.public.excel.worksheet.functions
campli
external usenet poster
 
Posts: 4
Default entering more than seven levels of functions with in a function

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=1094 0,15700,IF(B14=11410,16300,IF(B14=11880,16900,IF(B 14=12350,17500,IF(B14=12820,18100,IF(B14=13320,187 00,IF(B14=13820,19400,IF(B14=14320,20100,IF(B14=14 880,20900,IF(B14=15440,21700,IF(B14=16000,22500,IF (B14=16560,23300,IF(B14=17120,24100,IF(B14=17680,2 4900,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.
Ads
  #3  
Old June 3rd, 2010, 03:25 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 9
Default entering more than seven levels of functions with in a function

On Wed, 2 Jun 2010 18:57:01 -0700, CAMPLI
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=109 40,15700,IF(B14=11410,16300,IF(B14=11880,16900,IF( B14=12350,17500,IF(B14=12820,18100,IF(B14=13320,18 700,IF(B14=13820,19400,IF(B14=14320,20100,IF(B14=1 4880,20900,IF(B14=15440,21700,IF(B14=16000,22500,I F(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=2228 0,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.
  #4  
Old June 3rd, 2010, 11:48 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default entering more than seven levels of functions with in a function

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,12 820,
13320,13820,14320,14880,15440,16000,16560,17120,17 680,18240,
18800,19360,19920,20480,21040,21660,22280,22900,23 520},
{14500,15100,15700,16300,16900,17500,18100,18700,1 9400,
20100,20900,21700,22500,23300,24100,24900,25700,26 500,
27300,28100,28900,29700,30600,31500,32400,33300})

HTH
Steve D.


"Ron Rosenfeld" wrote in message
...
On Wed, 2 Jun 2010 18:57:01 -0700, CAMPLI
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=10 940,15700,IF(B14=11410,16300,IF(B14=11880,16900,IF (B14=12350,17500,IF(B14=12820,18100,IF(B14=13320,1 8700,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(B1 4=19360,27300,IF(B14=19920,28100,IF(B14=20480,2890 0,IF(B14=21040,29700,IF(B14=21660,30600,IF(B14=222 80,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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.