How to avoid dozen of nested IFs

[Boris]

Hello,

is there in Excel a function that works like "case" command that I remember
from basic. I need to check a cell which can contain several integer values
(1-10), and to have different formula for each value in another cell. So far
I am using a large IF functions which simpliest example is below:
=IF(A3>4,E3,IF(A3=4,IF(E3>E$92,E3-E$92,0),IF(A3=3,IF(E3>E$91,E3-E$91,0),IF(A
3=2,IF(E3>E$90,E3-E$90,0),IF(A3=1,IF(E3>E$89,E3-E$89,0),IF(B3="kiosk",IF(E3>
E$88,E3-E$88,0),IF(E3>E$87,E3-E$87,0)))))))
(here "0" value has two cases and above "4" is all the same)

It works, so it is not a big problem, beside that I need about half hour to
make one of them, not to mention using of links to another sheets.

Boris

 

[Andy B]

Boris

Having looked at your formula, without the sheet, it's difficult to see what
you are aiming at. However, to make life a little easier you could use the
MAX() function rather than testing for > all the time.
Instead of IF(E3>E$93,E3-E$93,0) you could use MAX(E3-E$93,0)

Andy.

 

[Jebradley2003]

It doesnt really answer your question but have you tried using excel's VBA?
I use IF, ELSEIF statements quite a lot in macros and this should do the trick.
But I'm pretty sure theres a case function too - worth a check

Jonathan B

 

[Chip Pearson]

Boris,

Take a look at the CHOOSE function.


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

 

[Felix]

Look at the Choose Function

-----Original Message-----
Hello,

is there in Excel a function that works like "case" command that I remember
from basic. I need to check a cell which can contain several integer values
(1-10), and to have different formula for each value in another cell. So far
I am using a large IF functions which simpliest example is below:
=IF(A3>4,E3,IF(A3=4,IF(E3>E$92,E3-E$92,0),IF(A3=3,IF (E3>E$91,E3-E$91,0),IF(A
(B3="kiosk",IF(E3>
E$88,E3-E$88,0),IF(E3>E$87,E3-E$87,0)))))))
(here "0" value has two cases and above "4" is all the same)

It works, so it is not a big problem, beside that I need about half hour to
make one of them, not to mention using of links to another sheets.

Boris


.

 

[Harlan Grove]

It doesnt really answer your question but have you tried using excel's VBA?
I use IF, ELSEIF statements quite a lot in macros and this should do the trick.
But I'm pretty sure theres a case function too - worth a check

There's already a CHOOSE worksheet function in Excel itself that accepts the
maximum number of arguments Excel can pass to functions. Since it's impossible
to pass VBA functions called as user-defined functions from worksheet cell
formulas, how would VBA provide any benefits compared to the already available
CHOOSE function?