how to do more than 7 if

[crunchh]

hello I have seen that it is impossible to nest more than 7 if

I tryed to find if there wheere something counter this proble like a switch
case function in other language something like that
swith
case A1<1 do A2=3
case A1<2 do A2=8
case A1<4 do A2=28
....

 

[Bob Phillips]

Do a lookup, or a VLOOKUP

=VLOOKUP(A1,{0,3;.99,8;7.99,28},2)

just add extra condition/value pairs

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Duke Carey]

Post more about your requirements & people here will give you several options
that will work.

For instacne, is there a set relationship between your pairs of values

is it always if <1 then 3
is it always if <2 then 8
is it always if <4 then 28
etc

 

[crunchh]

hello thanks for all your replies.
unfortunately there is no given relationship (at least simple ) that I can see
It was more a general problem
I was aiming at getting rid of encapsulating many if (and also sometimes i
have more than 7 case possible)

the lookup function works just fine but if there is another way around i
would be glad to know it

to be more precise a basic example would be a rating system
if a student has between 0-2 he gets a comment
if he has between 2-4 a different one etc up to grade 20
which makes 10 case possible and not only 7 like possible with if function

anyway as I said before lookup works but i m wondering if there is another
possibility (maybe just for the sake of it , I tend to be a bit maniac)

anyway once again thank you all for the time you are spending and for your
help.
Thank you very much

 

[Fredrik Wahlgren]

A general solution would be to create a User Defined Function. There's no
problem with nested if's and the code is easier to understand. Also, if ypou
use lots of nested if's that do the same thing, you don't need to worry
about the possibility that some of them have some error.

/Fredrik

 

[Duke Carey]

If you put your data into a small, 2 column table, like this
Col1 Col2
0 3
1.001 8
2.001 28
4.001 next #
-- all the way through your list of combinations

You can then use a Vlookup formula on the table.

Anything less than 1.001 will return a 3
Anything less than 2.001 but >=1.001 will return an 8
etc.
No limit on the number of entries in the table

 

[Myrna Larson]

Also check out the CHOOSE function, which allows for 29 or 30 choices, but the
logical test used in the beginning must return an integer which determines
which of the choices to return.