how do I wright the =if for this


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
    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!

    MATAYLOR ✭✭✭

    Nick Korna ✭✭✭✭ thank you so much that's excellent

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!