Nested If Functions Help +7 variables

[Andrew]

I have a problem where I have more than 7 variables for a
if function. Is there any way around this?

 

[Frank Kabel]

Hi
no way around it but in most cases it would be better to use a lookup
table and VLOOKUP. Maybe you can provide some more details about your
conditions?

 

[Chip Pearson]

There is no good way around it. Depending on what you need, a
VLOOKUP or a CHOOSE function must be a viable substitute for
nested IFs. Perhaps you could provide more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Andrew]

I work for an apartment complexe and I am evaluating rate
increases. The rate of increase is based on the distance
from what the market rate is. I have 9 different tiers.
This is the formula that I have been using, however, if
the rate does not fall with in the first 7 variables, I am
forced to manually correct with subtracting out because of
the addition after number 7. Does any of this make since?
=IF(G4<=-126,40,IF(G4<=-101,30,IF(G4<=-91,25,IF(G4<=-
81,22,IF(G4<=-71,20,IF(G4<=-51,18,IF(G4<=-37,15,IF(G4<=-
27,13))))))))+IF(G4<=-10,10,IF(G4>=0,0))-3

I tried the VLOOKUP and was having touble and not a lot of
time to figure out.

Thanks for the help.

Andrew

 

[Frank Kabel]

Hi
try the following on your sheet. e.g. in cells X1:Y10
X Y
1 -200 40
2 -127 30
3 -102 25
....

Now use
=VLOOKUP(G4,X1:Y10,2,1)

 

[Andrew]

Thank you Frank!! You just simplified my life by 10 fold!
Andrew