how do I wright the =if for this

MATAYLOR

I have a call audit sheet were 12 questions total a percentage in a column call marks i now need the answer in mark to show fail pass or excellent. and highlighting as well if possible.

0 to 69 being fail (showing word and highlighted)

70 to 85 being pass (showing word and highlighted)

85 to 100 being excellent (showing word and highlighted)

Nick Korna

Hi @MATAYLOR ,

This is really 2 things:

A nested IF formula for the results:

=IF(Mark@row < 0.7, "Fail", IF(Mark@row < 0.85, "Pass", IF(Mark@row >= 0.85, "Excellent")))

This is assuming your Mark column is a decimal. If not, change the values by 2 decimal places upwards (e.g. 0.7 becomes 70).

For the highlighting you can then use conditional formatting based on the result:

You can tailor the colours and cells to taste (making it highlight the row instead, for example).

This should give you something like the below:

The % will sometimes be a bit misleading if you leave it only 2 decimal places (for example, 84.9% will display as 85% in "Mark", but the result & formatting will work correctly from the actual value, not the displayed one.

Hope this helps!