Show a specific result if the number is inferior, superior or between with a formula in an excel report

It happens that the cell contains a number or a percentage, for instance, if I am exporting the SLA data. To know in a quick way if it is good or not, I can use the conditional formatting option by putting colors. The other way is to use a formula telling it by a word. This is especially useful when for instance I have to do a pie chart just to show “good” and “bad”.

formula excel

 

When I use the formula ?

When I want to show by a word a result than by a percentage or number.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formulas ?

=IF()

=AND()

This formula without between:

=IF(B2>95%,"good",IF(B2<90%,"bad","correct"))

This formula with between:

=IF(B2>0.95,"good",IF(AND(B2>0,B2<0.9),"bad","correct"))

For instance:

  • If in B2 I have 98%, it will show “good”
  • If in B2 I have 89%, it will show “bad”
  • If in B2 I have 92%, it will show “correct”
  • If in B2 I have 95%, it will show “correct” (not “good”)

As you can read in the last example, if I have 95%, it is not showing “good” but “correct”. If I want to include the 95%, I have to add in the formula the = sign, for example:

=IF(B2>=95%,"good",IF(B2<=90%,"bad","correct"))

Take note that for some functions like SUMIF, COUNTIF, etc., it will be:

  • Without a cell reference: ">=95%". For instance: =SUMIF(A2:A3,">=95%")
  • With a cell reference: ">="&K2. For instance: =SUMIF(A2:A3,">="&K2)

Interesting Management