Jan 24 2020 04:17 AM
Hi!
Conditional formatting has been bugging me for months. I have a KPI that I make a nice overview for once a month, where I want the following to happen:
I've set up this super simple rule as seen in my attachment (conditional formatting setup) and selected the range I want as seen in the other attachment (conditional formatting range).
Still, as you can see in "conditional formatting setup", 66.67% still gets a yellow exclamation point.
When I try to tweak the rule in order to see where it goes wrong, it doesn't really behave logically at all. I've tried tweaking between 66 and 67 to see if it does anything at all, but it doesn't affect anything.
For some reason it looks like it will give:
... despite that not being the parameters set in the rule.
There are no other active conditional formatting rules in the sheet, or in the workbook.
Nothing of the above affects the invisible rule that gives green at 80+ etc.
What am I doing wrong? :(
My work computer has Windows 10 and the Office 365 package.
Jan 24 2020 04:48 AM - edited Jan 24 2020 04:52 AM
Jan 24 2020 10:21 AM
Afraid you miss source data type and rule settings in percent. Let say you have 3 values: 49%,70%,80%.
When you say to apply green icon to values with more than 66 with type percent, you say that all values which are more than or equal to 49%+(80%-49%)*.66 `(which is equal 69.46%) will be with green icon.
That means 70% will be green and 69% will be yellow.
If your cell values are in per cents, easier and more convenient in conditional formatting rule use type Number and set rule as >=0.66 for green icon, rest are similar.
When in your source data 66% will be green and 65% will be yellow.