Excel - problem with IF

[cyberuser]

Hello can someone help me with the following problem :

I need to classify if the values below :

less than 1 receives value low
between than 1 and 2 receives value mid
more than 2 receives value hig


0,1 low
1,3 mid
0,2 mid
5,1 hig


any help is appreciated - thanks !

[JBeaucaire]

You'll have to edit this for your country layout, but a LOOKUP() seems to be the ticket here:


=LOOKUP(A1, {0,1,2}, {"low", "mid", "high"})
...or
=LOOKUP(A1; {0;1;2}, {"low"; "mid"; "high"})

[cyberuser]

i think i have expressed myself not clear....


0.001 LOW
1.051 MID
5.001 HIG


any chance to do it with IF or any other function ?

[zbor]

What's wrong with upper solution?

[cyberuser]

I used on cell N3 :

=LOOKUP(L3, {0,1,2}, {"low", "mid", "high"})


for a L3 cell with value : 1,1813


and got: "The formula you typed contains an error"

[zbor]

Did you try second one?

=LOOKUP(L3; {0;1;2}, {"low"; "mid"; "high"})

[cyberuser]

yep... same error.....

[NBVC]

There should be another semi-colon in there:


=LOOKUP(L3; {0;1;2}; {"low"; "mid"; "high"})

[bentleybob]

What''s wrong with

=IF(B6<1,"low",IF(B6<=2,"mid","high"))

?

[JBeaucaire]

Bentley, the "what's wrong" question misses the main point, efficiency. IF/THEN is fine for a couple of tests, but starting with 3 and up I always switch to one of the many lookup functions available. Once you start using them for longer IF/THEN scenarios, you'll find they're easier to read and edit.

LOOKUP() functions with inline arrays like we've shown in this thread is good for smaller sets of ranged options, 3-6. But around 6 I always put the actual ranges into a table on the workbook which makes updating the table a breeze.

=LOOKUP(A1; $P$1:$P$6; $Q$1:$Q$6)

[bentleybob]

Not wanting to get into a p***g match with you Jerry, but:

(a) The OP only specified low, medium, and high, so it hardly qualifies as something in need of "efficiency", as you put it;
(b) My formula can hardly be considered hard to read or edit; and
(c) The use of arrays is rather complex for some users.

That all being said, I do agree that your solution is more efficient and certainly more elegant.

Cheers.

[cyberuser]

ok... the formula =LOOKUP(L3; {0;1;2}; {"low";"mid";"high"}) worked now...

noted that it used ; for the entire formula.....

the another example with IF, didn't worked yet... not sure why

anyway i appreciated your attention !

[cyberuser]

where do i mark this thread as solved ???

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[JBeaucaire]

Not wanting to get into a p***g match with you Jerry...

No p***g match going on that I can see. Everyone should feel free to posit solutions and opinions and support them. We all learn, and we all then choose what works best for us. Everybody wins.

[cyberuser]

sorry guys ... i'm trapped again... with my own formula tuning now....

using : =LOOKUP(L4; {0,4999;1,2999;2,4999;2,5}; {"A";"B";"C";"D"})

i'm getting a 1,5931 value classified as B ...

if value is from 0 - 0,4999 should be A
if value is from 0,5 - 1,2999 should be B
if value is from 1,3 - 2,4999 should be C
if value is from 2,5 - all higher should be D

any hint ?

[shg]

=lookup(l4; {0;0,5;1,3;2,5}; {"a";"b";"c";"d"})

[JBeaucaire]

If it's not obvious yet, the LOOKUP() formula lists only the start of each range. Compare SHG's formula to your own and you'll see what I mean.