Formula

[Lise]

Hi from Aussie

I have the following formula however only the first two requirements show up
- anything under 12 continues to show the "Warning" sentence - What am I
doing wrong please?

=IF(M2=0,0,IF(M2>=16,"STOP You have reached EXTREME",IF(M2<16>12,"WARNING
High Rating",IF(M2<12>10,"MEDIUM Rating",IF(M2<8>10,"LOW Rating",SUM(M2))))))

 

[T. Valko]

I'm guessing this is what you had in mind:

=IF(M2=0,0,IF(M2>=16,"STOP You have reached EXTREME",IF(M2>=12,"WARNING
High Rating",IF(M2>=10,"MEDIUM Rating",IF(M2>=8,"LOW Rating",M2)))))

 

[T. Valko]

P.S.

IF(M2<8>10

I'm not sure how to interpret that so I guessed.

 

[edvwvw via OfficeKB.com]

P.S.

IF(M2<8>10

I'm not sure how to interpret that so I guessed.

I'm guessing this is what you had in mind:

[quoted text clipped - 11 lines]

Try this formula

=LOOKUP(M2,{0,10,12,16;"LOW RATING","MEDIUM RATING","HIGH RATING","You have
reached EXTREME"})

It will should give the desired result and is easier to read/change

edvwvw

 

[T. Valko]

P.S.

IF(M2<8>10

I'm not sure how to interpret that so I guessed.

I'm guessing this is what you had in mind:

[quoted text clipped - 11 lines]

High Rating",IF(M2<12>10,"MEDIUM Rating",IF(M2<8>10,"LOW
Rating",SUM(M2))))))

Try this formula

=LOOKUP(M2,{0,10,12,16;"LOW RATING","MEDIUM RATING","HIGH RATING","You
have
reached EXTREME"})

It will should give the desired result and is easier to read/change

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200805/1

=IF(M2=0,0,....IF(M2<8>10,"LOW Rating",SUM(M2))))))

My interpretation is:

If M2 = 0 then 0
If M2 >0 and <8 then M2

You're missing that test and a cell reference can't be used in an array
constant.

The easiest way to do this is to create a 2 column table:

...........A..........B..........
1........0..........=M2
2........8.........LOW Rating
3.......10........MEDIUM Rating
4.......12........WARNING High Rating
5.......16........STOP You have reached EXTREME

Then:

=IF(M2="","",VLOOKUP(M2,A1:B5,2)

 

[Lise]

Many thanks to you both - still very green when it comes to the LOOKUP stuff
but the way Biff wrote it works a treat ...Phew! so pleased. Once again the
Discussion group save the day .

 

[Lise]

Many thanks to you both - Very green when it comes to LOOKUP so didn't
venture down that path - Re-did the formula based on the infor Biff sent
through - works a treat I see I was using too many calculations ie < & >.
Once again the discussion group save the day.

 

[T. Valko]

You're welcome. Thanks for the feedback!