I'm not sure why Conditional Formatting isn't working correctly

%3CLINGO-SUB%20id%3D%22lingo-sub-1443722%22%20slang%3D%22en-US%22%3EI'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443722%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%20I%20hope%20you%20are%20all%20well.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20an%20issue%20with%20a%20worksheet%20and%20the%20Conditional%20Formatting%20I%20set%20up.%26nbsp%3B%20I%20have%20the%20formatting%20set%20to%20turn%20the%20cell%3A%26nbsp%3B%20Red%20if%20the%20value%20in%20the%20cell%20is%20less%20than%2085%25%2C%20Green%20if%20the%20value%20in%20the%20cell%20is%2085%25%20and%20above%2C%20and%20Blue%2C%20if%20the%20value%20in%20the%20cell%20is%20blank.%26nbsp%3B%20The%20cell%20is%20formatting%20to%20%22Percentage%22.%26nbsp%3B%20I%20have%20tried%20clearing%20the%20entire%20cell%2C%20and%20redoing%20everything%20and%20I%20still%20have%20the%20same%20result.%3C%2FP%3E%3CP%3EWhat%20is%20happening%20is%20when%20the%20cell%20is%20blank%20it%20remains%20green%20instead%20of%20blue.%26nbsp%3B%20I%20have%20attached%20a%20screenshot.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20could%20assist%20and%20let%20me%20know%20what%20I%20am%20doing%20wrong%2C%20I'd%20greatly%20appreciate%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1443722%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443756%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443756%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20not%20able%20to%20look%20at%20your%20attachment%20at%20present%20but%20I%20suspect%20that%20you%20have%20the%20conditions%20in%20the%20wrong%20order.%20If%20you%20put%20the%20blank%20check%20first%20(%20you%20can%20move%20it%20up)%20and%20then%20check%20the%20box%20%E2%80%9Cstop%20if%20true%E2%80%9D.%20This%20will%20then%20not%20apply%20the%20other%20checks.%20You%20can%20then%20have%20the%20other%202%20formats%20applied%20in%20any%20order.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20helps%3CBR%20%2F%3E%3CBR%20%2F%3EPetet%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443759%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443759%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432860%22%20target%3D%22_blank%22%3E%40MichaelAntonelli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20value%20of%20the%20blank%20cell%20is%20equivalent%20to%20zero%2C%200.85%20is%20greater%20than%20zero%2C%20green%20condition%20triggers%20the%20color%20and%20next%20rule%20is%20not%20checked.%20You%20shall%20change%20(%26gt%3B0.85%20and%20%26lt%3B%26gt%3B0%20)%20for%20this%20rule%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443805%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20well%20this%20is%20a%20first%20for%20me.%26nbsp%3B%20Sergei%2C%20I%20did%20some%20testing%20and%20%22%22%26lt%3B%26gt%3B0%26nbsp%3B%20and%20in%20fact%20it%20appears%20any%20text%20is%20evaluating%20%26gt%3B%20any%20number.%26nbsp%3B%20For%20example%20%221%22%26gt%3B999999%20results%20TRUE%26nbsp%3B%20(but%20--%221%22%26gt%3B9%20evaluates%20FALSE%20as%20expected)%3C%2FP%3E%3CP%3Eso%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432860%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40MichaelAntonelli%3C%2FA%3E%26nbsp%3B%20change%20the%20rule%20to%20be%20(%26gt%3B0.85%20and%20%26lt%3B%26gt%3B%22%22%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443857%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20exactly%2C%20any%20text%20is%20always%20%22greater%22%20than%20any%20number.%20But%20I%20don't%20see%20why%20do%20we%20shall%20to%20mix%20numbers%20and%20texts%20together.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20variant%2C%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%20suggested%2C%20is%20to%20move%20checking%20of%20blank%20cell%20on%20first%20position.%20%22Stop%20if%20True%22%20not%20necessary%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20733px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196783iEEC900DD5B6AD399%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThis%20one%20is%20simplest%20and%20easiest.%20With%20only%20one%20but.%20In%2095%25%20of%20cases%20it%20works%20perfectly%2C%20but%20sometimes%20Excel%20re-order%20sequence%20of%20rules%20automatically.%20These%20are%20relatively%20complex%20cases%2C%20nonetheless%2C%20where%20possible%20it's%20better%20be%20independent%20of%20rules%20sequence.%20Like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20716px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196784i62990E79655ECC48%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20not%20complex%20formulas%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(G2%26lt%3B0.85)*(G2%26lt%3B%26gt%3B0)%0A%3D(G2%26gt%3B%3D0.85)*(G2%26lt%3B%26gt%3B0)%0A%3DG2%3D0%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448366%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much!%26nbsp%3B%20Yes...that%20worked.%26nbsp%3B%20I%20wasn't%20aware%20that%20the%20conditions%20had%20to%20be%20in%20an%20order.%26nbsp%3B%20You%20taught%20me%20something%20very%20valuable!%26nbsp%3B%20I%20very%20much%20appreciate%20your%20response.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448370%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448370%22%20slang%3D%22en-US%22%3E%3CP%3EI%26nbsp%3Bthank%26nbsp%3Byou%26nbsp%3Bvery%26nbsp%3Bmuch%26nbsp%3Bfor%26nbsp%3Breplying.%26nbsp%3B%20I%20was%20able%20to%20get%20it%20to%20work%20properly%20with%20Peter's%20suggestion.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448375%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448375%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20for%20responding.%20I%20tried%20the%20suggestion%20from%20Peter%20and%20it%20worked.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448381%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448381%22%20slang%3D%22en-US%22%3EThank%20you%20for%20replying.%20I%20did%20try%20Peter's%20suggestion%20and%20it%20seemed%20work%20fix%20the%20issue.%20Much%20appreciated%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448392%22%20slang%3D%22en-US%22%3ERe%3A%20I'm%20not%20sure%20why%20Conditional%20Formatting%20isn't%20working%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432860%22%20target%3D%22_blank%22%3E%40MichaelAntonelli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20also%20the%20comments%20left%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%20especially%20that%20text%20is%20'greater'%20than%20numbers%20-%20I%20should%20have%20mentioned%20this.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eanyway%20good%20luck!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPete%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello,  I hope you are all well.  

I'm having an issue with a worksheet and the Conditional Formatting I set up.  I have the formatting set to turn the cell:  Red if the value in the cell is less than 85%, Green if the value in the cell is 85% and above, and Blue, if the value in the cell is blank. 

I have tried clearing the entire cell, and redoing everything and I still have the same result.

What is happening is when the cell is blank it remains green instead of blue.  I have attached a screenshot. 

If anyone could assist and let me know what I am doing wrong, I'd greatly appreciate it.  

9 Replies
Highlighted
Hi

I’m not able to look at your attachment at present but I suspect that you have the conditions in the wrong order. If you put the blank check first ( you can move it up) and then check the box “stop if true”. This will then not apply the other checks. You can then have the other 2 formats applied in any order.

Hope this helps

Petet
Highlighted

@MichaelAntonelli 

That's since value of the blank cell is equivalent to zero, 0.85 is greater than zero, green condition triggers the color and next rule is not checked. You shall change (>0.85 and <>0 ) for this rule

Highlighted

@Sergei Baklan  well this is a first for me.  Sergei, I did some testing and ""<>0  and in fact it appears any text is evaluating > any number.  For example "1">999999 results TRUE  (but --"1">9 evaluates FALSE as expected)

so 

@MichaelAntonelli  change the rule to be (>0.85 and <>"" )

Highlighted

@mtarler 

Yes, exactly, any text is always "greater" than any number. But I don't see why do we shall to mix numbers and texts together.

 

First variant, as @peteryac60  suggested, is to move checking of blank cell on first position. "Stop if True" not necessary

image.png

This one is simplest and easiest. With only one but. In 95% of cases it works perfectly, but sometimes Excel re-order sequence of rules automatically. These are relatively complex cases, nonetheless, where possible it's better be independent of rules sequence. Like this:

image.png

with not complex formulas

=(G2<0.85)*(G2<>0)
=(G2>=0.85)*(G2<>0)
=G2=0
Highlighted

@peteryac60 Thank you very much!  Yes...that worked.  I wasn't aware that the conditions had to be in an order.  You taught me something very valuable!  I very much appreciate your response.

Highlighted

I thank you very much for replying.  I was able to get it to work properly with Peter's suggestion.@Sergei Baklan 

Highlighted
Thank you very much for responding. I tried the suggestion from Peter and it worked.
Highlighted
Thank you for replying. I did try Peter's suggestion and it seemed work fix the issue. Much appreciated
Highlighted

@MichaelAntonelli 

 

You are welcome.

 

Note also the comments left by @Sergei Baklan  especially that text is 'greater' than numbers - I should have mentioned this.

 

anyway good luck!

 

Pete