2

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.

2
  • Do you mean if cell < 500 && >= 400 then "Medium"? Also, are you meaning to leave the <400 && > 300 range uncovered?
    – heavyd
    Aug 16, 2010 at 14:12
  • @heavyd: exactly, i meant if cell < 500 && >= 400 then "Medium". the third point should actually be < 400
    – nmuntz
    Aug 16, 2010 at 14:15

3 Answers 3

5

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

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

=IF(A1>=500,"Critical",IF(A1>=400,"Medium","Normal"))
1
  • 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, 2010 at 14:33
-2

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

=IF(A1>499,"Critical",IF(A1>399,"Medium","Normal"))

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

1
  • 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, 2013 at 17:21

You must log in to answer this question.

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