问题:

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

回答1:

When you pick "percentage", this takes the values as a percentage of all the values. Since you're formatting based on the value itself, and not the value a a percentage of all the other values, you need to use 'Number' for the type and use 0.99 for the upper bound and 0.95 for the lower bound.

回答2:

While the answer from @Jerry shows the solution which @James Deadman possibly needs, it should be mentioned how Excel will calculate percentage for conditional formatting using icon sets.

Assuming the following sheet:

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

So how is Excel calculating the percentages? It assumes the minimum of A1:A6 as 0% and the maximum of A1:A6 as 100%. So the percentages in column B are calculated:

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

as formula in B1 copied downwards.

To be clear how this is related to the question:

In the example given with the picture it seems the conditional formatted values are percentages itself. So the maximum is 100% = 1 but the minimum is the lowest percentage value given, maybe 90% = 0.9. So 1 = 100% but 0.90 is taken as 0% since it is the minimum value.