Formula
 

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<1612,"WARNING
High Rating",IF(M2<1210,"MEDIUM Rating",IF(M2<810,"LOW Rating",SUM(M2))))))

Formula
 

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)))))

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
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<1612,"WARNING
High Rating",IF(M2<1210,"MEDIUM Rating",IF(M2<810,"LOW
Rating",SUM(M2))))))

Formula
 

P.S.

IF(M2<810

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))))

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
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<1612,"WARNING
High Rating",IF(M2<1210,"MEDIUM Rating",IF(M2<810,"LOW
Rating",SUM(M2))))))

Formula
 

T. Valko wrote:
P.S.

IF(M2<810

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<1210,"MEDIUM Rating",IF(M2<810,"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.a...excel/200805/1

Formula
 

"edvwvw via OfficeKB.com" <u42512@uwe wrote in message
news:846e7c6580db0@uwe...
T. Valko wrote:
P.S.

IF(M2<810

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<1210,"MEDIUM Rating",IF(M2<810,"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.a...excel/200805/1

=IF(M2=0,0,....IF(M2<810,"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)

--
Biff
Microsoft Excel MVP

Formula
 

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 :-).

Formula
 

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. :-)

Formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
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. :-)