Home

Conditional formatting is not following simple rules

%3CLINGO-SUB%20id%3D%22lingo-sub-1127228%22%20slang%3D%22en-US%22%3EConditional%20formatting%20is%20not%20following%20simple%20rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127228%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3CBR%20%2F%3E%3CBR%20%2F%3EConditional%20formatting%20has%20been%20bugging%20me%20for%20months.%20I%20have%20a%20KPI%20that%20I%20make%20a%20nice%20overview%20for%20once%20a%20month%2C%20where%20I%20want%20the%20following%20to%20happen%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EIf%20the%20cell%20has%20a%20value%20%3CSTRONG%3Eat%20or%20above%2066.00%25%3C%2FSTRONG%3E%2C%20give%20it%20a%20nice%20green%20checkmark%20icon.%3C%2FLI%3E%3CLI%3EIf%20the%20cell%20has%20a%20value%20%3CSTRONG%3Ebetween%2050.00%25%20and%2066.00%25%3C%2FSTRONG%3E%2C%20give%20it%20a%20slightly%20less%20nice%20yellow%20exclamation%20point%20icon.%3C%2FLI%3E%3CLI%3EIf%20the%20cell%20has%20a%20value%20%3CSTRONG%3Eat%20or%20below%2050.00%25%3C%2FSTRONG%3E%2C%20give%20it%20a%20%22you%20could%20do%20better%22%20red%20cross%20icon.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI've%20set%20up%20this%20super%20simple%20rule%20as%20seen%20in%20my%20attachment%20(%3CEM%3Econditional%20formatting%20setup%3C%2FEM%3E)%20and%20selected%20the%20range%20I%20want%20as%20seen%20in%20the%20other%20attachment%20(%3CEM%3Econditional%20formatting%20range%3C%2FEM%3E).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStill%2C%20as%20you%20can%20see%20in%20%22%3CEM%3Econditional%20formatting%20setup%3C%2FEM%3E%22%2C%2066.67%25%20still%20gets%20a%20yellow%20exclamation%20point.%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20try%20to%20tweak%20the%20rule%20in%20order%20to%20see%20where%20it%20goes%20wrong%2C%20it%20doesn't%20really%20behave%20logically%20at%20all.%20I've%20tried%20tweaking%20between%2066%20and%2067%20to%20see%20if%20it%20does%20anything%20at%20all%2C%20but%20it%20doesn't%20affect%20anything.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%20it%20looks%20like%20it%20will%20give%3A%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3Ea%20green%20icon%20to%20anything%20%26gt%3B%3D80.00%25%3C%2FLI%3E%3CLI%3Ea%20yellow%20icon%20to%20anything%20between%2066%25%20and%2080%25%3C%2FLI%3E%3CLI%3Ea%20red%20icon%20to%20anything%20%26lt%3B66%25%3C%2FLI%3E%3C%2FUL%3E%3CP%3E...%20despite%20that%20not%20being%20the%20parameters%20set%20in%20the%20rule.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThere%20are%20no%20other%20active%20conditional%20formatting%20rules%20in%20the%20sheet%2C%20or%20in%20the%20workbook.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%20tried%20to%20select%20the%20range%20as%20a%20continuous%20selection%20from%20the%20first%20blank%20cell%20to%20the%20last%20one%20I%20want%20the%20rule%20to%20be%20applied%20to.%3C%2FLI%3E%3CLI%3EI%20have%20tried%20to%20select%20each%20cell%20with%20content%20individually.%20It%20doesn't%20change%20the%20result.%3C%2FLI%3E%3CLI%3EI%20have%20tried%20to%20enter%20the%20percentage%20manually%20as%200.6667%3C%2FLI%3E%3CLI%3EI%20have%20tried%20doing%20a%20simple%20%3D(2%2F3)%3C%2FLI%3E%3CLI%3EI%20have%20tried%20doing%20a%20simple%20referral%20formula%20%3D(R33%2FP33)%3C%2FLI%3E%3C%2FUL%3E%3CP%3ENothing%20of%20the%20above%20affects%20the%20invisible%20rule%20that%20gives%20green%20at%2080%2B%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EWhat%20am%20I%20doing%20wrong%3F%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSTRONG%3E%3C%2FU%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EMy%20work%20computer%20has%20Windows%2010%20and%20the%20Office%20365%20package.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1127228%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1127265%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20is%20not%20following%20simple%20rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127265%22%20slang%3D%22en-US%22%3EBased%20on%20the%20rule%20you%20set%20as%20seen%20in%20your%20attachment%2C%20any%20values%20that%20is%20%22less%20than%22%2066%25%20will%20return%20Yellow%20checkmark...%20Definitely%2C%20values%20that%20equals%20or%20greater%20than%2066%25%20percent%20will%20return%20Green%20checkmark.%3CBR%20%2F%3E%3CBR%20%2F%3ESee%20the%20attached%20caption%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1128006%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20is%20not%20following%20simple%20rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1128006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531329%22%20target%3D%22_blank%22%3E%40lisauh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfraid%20you%20miss%20source%20data%20type%20and%20rule%20settings%20in%20percent.%20Let%20say%20you%20have%203%20values%3A%2049%25%2C70%25%2C80%25.%3C%2FP%3E%0A%3CP%3EWhen%20you%20say%20to%20apply%20green%20icon%20to%20values%20with%20more%20than%2066%20with%20type%20percent%2C%20you%20say%20that%20all%20values%20which%20are%20more%20than%20or%20equal%20to%2049%25%2B(80%25-49%25)*.66%20%60(which%20is%20equal%2069.46%25)%20will%20be%20with%20green%20icon.%3C%2FP%3E%0A%3CP%3EThat%20means%2070%25%20will%20be%20green%20and%2069%25%20will%20be%20yellow.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20cell%20values%20are%20in%20per%20cents%2C%20easier%20and%20more%20convenient%20in%20conditional%20formatting%20rule%20use%20type%20Number%20and%20set%20rule%20as%20%26gt%3B%3D0.66%20for%20green%20icon%2C%20rest%20are%20similar.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20in%20your%20source%20data%2066%25%20will%20be%20green%20and%2065%25%20will%20be%20yellow.%3C%2FP%3E%3C%2FLINGO-BODY%3E
lisauh
Occasional Visitor

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:

 

  1. If the cell has a value at or above 66.00%, give it a nice green checkmark icon.
  2. If the cell has a value between 50.00% and 66.00%, give it a slightly less nice yellow exclamation point icon.
  3. If the cell has a value at or below 50.00%, give it a "you could do better" red cross icon.

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: 

  • a green icon to anything >=80.00%
  • a yellow icon to anything between 66% and 80%
  • a red icon to anything <66%

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

 

  • I have tried to select the range as a continuous selection from the first blank cell to the last one I want the rule to be applied to.
  • I have tried to select each cell with content individually. It doesn't change the result.
  • I have tried to enter the percentage manually as 0.6667
  • I have tried doing a simple =(2/3)
  • I have tried doing a simple referral formula =(R33/P33)

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.

2 Replies
Highlighted
Based on the rule you set as seen in your attachment, any values that is "less than" 66% will return Yellow checkmark... Definitely, values that equals or greater than 66% percent will return Green checkmark.

See the attached caption
Highlighted

@lisauh 

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.

Related Conversations