Ask Your Question
0

Need formula using ifs

asked 2017-09-07 22:52:57 +0100

dogluvver gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-09-08 08:19:20 +0100

JohnSUN gravatar image

updated 2017-09-08 13:02:55 +0100

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. Don't write "Thank you"

edit flag offensive delete link more

Comments

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

robleyd gravatar imagerobleyd ( 2017-09-08 10:19:17 +0100 )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(...)) ;-)

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-08 15:26:28 +0100 )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? ;-)

JohnSUN gravatar imageJohnSUN ( 2017-09-08 15:47:13 +0100 )edit
0

answered 2017-09-08 13:50:05 +0100

dogluvver gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-07 22:52:57 +0100

Seen: 827 times

Last updated: Sep 08 '17