Conditional formatting of percent is incorrect Excel

Question

I have the following:

So i want

99% and above = Green Between 95% - 99% = Amber Below 95% = Red 

However, above 95% is still red.

Any ideas?

Thanks James

Answer 1

When you select "percent", it takes on a percentage of all values. Since you format based on the value itself, and not the aa value of a percentage of all other values, you need to use "Number" for the type and use 0.99 for the upper border and 0.95 for the lower border.

Answer 2

While the answer from @Jerry shows a solution that @James Deadman might need, mention should be made of how Excel will calculate the percentage of conditional formatting using icon sets.

Assuming the following sheet:

In A1:A6 is the same conditional formatting as in the question.

So how does Excel calculate percentages? It takes a minimum of A1:A6 as 0%, and a maximum of A1:A6 - 100%. Thus, the percentages in column B are calculated:

 =(A1-MIN($A$1:$A$6))/(MAX($A$1:$A$6)-MIN($A$1:$A$6)) 

how the formula in B1 copied down.

To understand how this relates to the question:

In the example shown with the image, it seems that conditionally formatted values ​​are percentages. Thus, the maximum is 100% = 1, but the minimum value is the lowest percentage value, possibly 90% = 0.9. Thus, 1 = 100%, but 0.90 is taken as 0%, since this is the minimum value.