FORMULAS

[Tom]

I have the following formula that I'm using and can't seem to get it
accomplished. In column "G", I have a list of numbers that I need to
have matched with the following formula and give me the class it falls
under, but I keep getting an error, due to to many functions. I found
out that there is a limit of only 7 nested functions in excel. Is
there a way around this, or am I stuck? Any ideas????

Thanks

Tom

=IF(G2<1,"400",IF(G2<2,"300",IF(G2<4,"250",IF(G2<6,"150",IF(G2<8,"125",IF(G2<10,"100",IF(G2<12,"92.5",IF(G2<15,"85",IF(G2<22.5,"70",IF(G2<30,"65",IF(G2>=30,"60)))))))))))

 

[Frank Kabel]

Hi
there's a limit of 7 nested functions. In your case you may try the
following
1. Create a lookup table in the following layout (e.g. on the sheet
'lookup')
A B
1 -100 400
2 1 300
3 2 250
4 4 150
....

2. Use the following formula
=VLOOKUP(G1,'lookup'!A1:B20,2,TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany

I have the following formula that I'm using and can't seem to get it
accomplished. In column "G", I have a list of numbers that I need to
have matched with the following formula and give me the class it falls
under, but I keep getting an error, due to to many functions. I found
out that there is a limit of only 7 nested functions in excel. Is
there a way around this, or am I stuck? Any ideas????

Thanks

Tom

=IF(G2<1,"400",IF(G2<2,"300",IF(G2<4,"250",IF(G2<6,"150",IF(G2<8,"125",
IF(G2<10,"100",IF(G2<12,"92.5",IF(G2<15,"85",IF(G2<22.5,"70",IF(G2<30,"
65",IF(G2>=30,"60)))))))))))

 

[Tom Ogilvy]

Split your test into two sides as shown:

=IF(G2<8,IF(G2<1,"400",IF(G2<2,"300",IF(G2<4,"250",IF(G2<6,"150",IF(G2<8,"12
5"))))),IF(G2<10,"100",IF(G2<12,"92.5",IF(G2<15,"85",IF(G2<22.5,"70",IF(G2<3
0,"65","60"))))))

works.

or set up a table on your sheet and use a vlookup function.

--
Regards,
Tom Ogilvy

I have the following formula that I'm using and can't seem to get it
accomplished. In column "G", I have a list of numbers that I need to
have matched with the following formula and give me the class it falls
under, but I keep getting an error, due to to many functions. I found
out that there is a limit of only 7 nested functions in excel. Is
there a way around this, or am I stuck? Any ideas????

Thanks

Tom

=IF(G2<1,"400",IF(G2<2,"300",IF(G2<4,"250",IF(G2<6,"150",IF(G2<8,"125",IF(G2
<10,"100",IF(G2<12,"92.5",IF(G2<15,"85",IF(G2<22.5,"70",IF(G2<30,"65",IF(G2>
=30,"60)))))))))))

 

[Tom Tucker]

I need for the search to look through the following criteria:

Less than 1 =400
1 but less than 2 =300
2 but less than 4 =250
4 but less than 6 =150
6 but less than 8 =125
8 but less than 10 =100
10 but less than 12 =92.5
12 but less than 15 =85
15 but less than 22.5 =70
22.5 but less than 30 =65
30 or greater =60

so if the number is 7.95, I need for it to enter 125.

 

[Frank Kabel]

Hi
and that is what the formula will do
Just enter your criteria:
- in column A ONLY the lower boundary (and for the first row just a
negative number)
- in column B the desired result

And then use VLOOKUP

 

[Tom Ogilvy]

While a Vlookup is certainly an option if you want to take up more real
estate, have you abandoned the idea of using IF statements even though I
showed you how to do it? Just curious why your continuing to struggle when
you could just copy in the solution.