How to avoid dozen of nested IFs

  • Thread starter Thread starter Boris
  • Start date Start date
B

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
 
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.
 
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
 
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


.
Click to expand...
 
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
Click to expand...

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?
 
Back
Top