Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers 3

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
add comment

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"))
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
add comment

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.

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.