# Need formula using ifs

Have a range of numbers 1 - 60. I want to make a choice depending on a range of numbers i.e. If the number is between 1 and 10 then do THIS if the number is between 11and 20 do THAT if the number is between 21 and 40 do SOMETHING ELSE if the number is between 41 and 50 do XYZ if the number is between 51 and 60 do ABC

The THIS/THATetc names relate to formula.

edit retag close merge delete

Sort by » oldest newest most voted

You are in vain trying to apply the function of the IFS() for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.

=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")


or

=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)


There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case

Update Other variants for writing the last solution:

=HLOOKUP(A1;{1;11;21;41;51|"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"};2;1)
=LOOKUP(A1;{1;11;21;41;51};{"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"})


Perhaps with this arrangement of values and actions the formula will be easier to edit.

Considering comment of esteemed colleague @robleyd, I bring also the variant of the formula using a set of nested IF().

=IF(A1<1;NA();IF(A1<11;"THIS";IF(A1<21;"THAT";IF(A1<41;"SOMETHING ELSE";IF(A1<51;"XYZ";IF(A1<61;"ABC";NA()))))))


You have to admit that the work of editing the formula very difficult in such a record.

more

I wonder if the question meant using a number of nested IF statements ?

( 2017-09-08 10:19:17 +0200 )edit

WRT your updated multiple-IFs variant: (nerd mode on) for your brilliant *LOOKUP solutions to be equivalent to it, you should put them into IF(A1>60;NA();VLOOKUP(...)) ;-)

( 2017-09-08 15:26:28 +0200 )edit

@Mike Kaganski You are absolutely right. But when I read "The THIS/THATetc names relate to formula", I thought that "ABC" may well turn out to be the same NA(). And why do we need so many NA() in a single formula? ;-)

( 2017-09-08 15:47:13 +0200 )edit

Thx - I had an ah-hah moment in the middle of the night and came up with the vlookup solution.

more