Formulas for performance color formatting with benchmarks

Mark Rhodes

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!

Heath Hilton

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.

Mark Rhodes

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?

Heath Hilton

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.

Mark Rhodes

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. :/