Formula Help

T

Timothy Wright

I have the following formula in a cell:

=IF(C2<=30,0.27,IF(C2<=50,0.232,XXXXXXXIF(C2<=60,0.232,IF(C2<=80,0.22,IF(C2<=90,0.22,IF(C2<=100,0.21,IF(C2<=110,0.21,IF(C2<=500,0.2))))))))

I tried to add in the following: if(c2<=55,.24

It is to be inserted where I haveXXXXXXX in the above formula.

However, I keep getting an error, and excel won't let me add anything.
Is it because I have too many things going on? I think if I take out
one and put in another it works. Anyway, is there a way to write this
so I can put in as many functions as I want, or any help at all would
be appreciated.

Thanks,

Timothy
 
F

Frank Kabel

Hi
Excel has a limit of 7 nested functions. This limit is reached with
your existing formula. Try the following:
1. Create a lookup table (lets say on a separate sheet called
'lookup'):
A B
1 0 0.27
2 30.1 0.232
3 50.1 0.24
4 55.1 0.232
....

Now use the following formula
=INDEX('lookup'!$B$1:$B$10,MATCH(C2,'lookup'!$A$1:$A$10,1))
 
D

David McRitchie

Hi Timothy,
If this is going to get more complicate you might want to switch
to VLOOKUP. http://www.mvps.org/dmcritchie/excel/vlookup.htm

-- Just saw Frank's answer MATCH is probably better than VLOOKUP.

You are limited to 7 nesting levels, you can reduce levels by
combining then separating them. Start at IF(C2<=80
and work 30.0 and 50 and 55 and 80 into a 1st, 2nd, 3rd level, 4th level
and the rest into a 1st 2nd and 3rd level, and 4th level

Another way would be to used named cells a la Chip Pearson
http://www.cpearson.com/excel/nested.htm which I hope was done
more as a demonstration, because for most people if there is a tradeoff
between readability and updateability with simplicity I think you want to keep
it simple so you can update it. Usually simpler is faster but certainly not
always. VLOOKUP is certainly going to take longer but for most uses
it probably won't take more time than all the time you would waste trying
to update and unravel long complicated formulas.
 
T

Timothy Wright

Hi
Excel has a limit of 7 nested functions. This limit is reached with
your existing formula. Try the following:
1. Create a lookup table (lets say on a separate sheet called
'lookup'):
A B
1 0 0.27
2 30.1 0.232
3 50.1 0.24
4 55.1 0.232
...

Now use the following formula
=INDEX('lookup'!$B$1:$B$10,MATCH(C2,'lookup'!$A$1:$A$10,1))
Click to expand...


Hi, thanks to all who responded. The above answer seems to work OK.
It's a bit of work, but solves the problem. Thanks much.
 
Top