Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to create an excel formula based on the following:

1. If Cell >= 500 Then the Cell text should be "Critical"
2. If Cell >= 400 Then the cell text should be "Medium"
3. If cell < 400 then the cell text should be "Normal"

I currently have

=IF(OFFSET(H2;0;-1)>500;"Critical"; IF(OFFSET(H2;0;-1)<=500;"Normal";))

But as soon as i add the third condition it doesn't accept it and gives me errors and messes up the whole thing.

share|improve this question
Do you mean if cell < 500 && >= 400 then "Medium"? Also, are you meaning to leave the <400 && > 300 range uncovered? – heavyd Aug 16 '10 at 14:12
@heavyd: exactly, i meant if cell < 500 && >= 400 then "Medium". the third point should actually be < 400 – nmuntz Aug 16 '10 at 14:15
up vote 5 down vote accepted

You don't need a third condition. You need to do this:

Is value >= 500? If yes then "Critical" else continue:
Is value >= 400? If yes then "Medium" else "Normal"

So you need to do the following:

IF( DESREF(H2;0;-1)>=500 ; "Critical" ; IF( DESREF(H2;0;-1)>=400 ; "Medium" ; "Normal ) )

And, with a line-break for readability:

IF( DESREF(H2;0;-1)>=500 ; "Critical" ; 
                             IF( DESREF(H2;0;-1)>=400 ; "Medium" ; "Normal ) )
share|improve this answer
This worked out great ! Thank you very much. – nmuntz Aug 16 '10 at 14:31

Assuming you're evaluating cell A1, type in the following formula and it will work for your conditions:

share|improve this answer
Yes, in this case the "Normal" is the "Else" for this comparison, and part of any IF statement for Excel. If Condition 1, do X. If Condition 2, do Y. Anything else, do Z. – Michael Aug 16 '10 at 14:33

No need to use complex formula. just use following simple logic.

Assume that the numbers are in Cell range A1 to A10

Simply put the following formula in cell besides A1 i.e in Cell B1


To get similar results just copy the above into the other desired cells.

share|improve this answer
First: This is the same sort of answer as @MehperC. Palavuzlar already gave. Second: A1>499 isn't as good as A1>=500 because floating numbers between 499 and 500 aren't treated the right way – nixda Jul 14 '13 at 17:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .