Formulas for performance color formatting with benchmarks

I want one of the five benchmarks to be highlighted in correlation to what is “performance”. For this example, how can I get Benchmarks – 2 Star background color changed? The benchmark ranges are 0-54%, 55-63%, 64-70%, 71-77%, and 78-100%. I have 9 metrics to do this every month.

Open to other ideas as well. I just need to have an indicator of which benchmark the performance falls into.

Thank you!



  • Mark Rhodes,

    I think I might have a solution for you. The Performance cells with the % sign is actually convertible to a decimal and then it is just a couple simple "Conditional Formatting" equations.

    Here is the Conditional Formatting ...

    Let me know if that worked.

    Hope this helps!

    Heath Hilton

  • Thank for the quick response!

    It sort of worked.

    The first month there was one that didn't follow the conditional formatting.

    Later in the year 3 didn't follow the formatting.

    Here's the formatting rules.

    What are your thoughts? Should I create a sheet for each month rather than just one?

  • Mark Rhodes,

    I am not exactly sure what you are doing to try and be able to guide you. The conditional formatting looks like it did what it was supposed to.

    What validator are you using to determine that it isn't working? If it is your "validated" column, I am not sure what is powering that one.

    Hope this helps!

    Heath Hilton

  • Nothing is powering the "validated" column... that was me manaually making sure each cell in the "performance" column was placed in the right benchmark.

    Think I just realized my issue.

    Each row is going to need a different conditional format since each metric (performance) has a different benchmark.

    Example: 83% on some rows would be placed in the 5 Star benchmark while in other rows in would be a 3.

    oops. :/