Menu
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
248
If your formula refers to a wrong cell, a mismatch between the active cell and the formula will occur, which will result in conditional formatting highlighting wrong cells.
Create a custom formatting rule In a worksheet, select the range of cells in which you'll be entering negative percentages. On the Home tab, click Conditional Formatting > New Rule. In the New Formatting Rule box, select Format only cells that contain.
Select cells in column. Conditional formatting -> New Rule->Format all cells based on their values->Format Style: 2-Colour Scale->Min-Type: Percent,Min-Value:0; Max-Type:Percent, Max-Value:100->OK.
The Advanced tab of the Excel Options dialog box. Make sure the Enable Automatic Percent Entry check box is selected. Click on OK.
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 as 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.
EDIT: It looks like the behaviour is different in Excel 2016 and one needs to actually put 99 and 95 (and not 0.99 and 0.95) as values. I guess Microsoft received some complaints or something and decided to change it.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
