SOLVED

Conditional formatting with a formula-cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1098534%22%20slang%3D%22en-US%22%3EConditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098534%22%20slang%3D%22en-US%22%3EHello%20everyone%2C%3CBR%20%2F%3EI%20got%20a%20situation%20with%20conditional%20formatting%3CBR%20%2F%3EI'm%20trying%20to%20highlight%20cells%20that%20contains%20a%20value%20greater%20than%205000%20in%20a%20specific%20column%2C%20the%20problem%20is%20that%20cells%20in%20this%20column%20contains%20a%20specific%20formula%20that%20gives%20a%20number%20in%20result%20or%20a%20blank%20result%3CBR%20%2F%3ECells%20giving%20values%20lower%20than%205000%20aren't%20formatted%20and%20cells%20with%20greater%20value%20are%20formatted%20but%20as%20i%20mentioned%20above%20there%20are%20cells%20with%20a%20blank%20result%2C%20the%20conditional%20formatting%20is%20also%20formatting%20the%20blank%20ones%20while%20it%20should%20leave%20it%20unformatted%3CBR%20%2F%3EWhat%20can%20I%20do%20in%20this%20case%20%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%3CBR%20%2F%3E%3CBR%20%2F%3EEdit%20%3A%20Sorry%20for%20my%20poor%20English%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1098534%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-1098693%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511751%22%20target%3D%22_blank%22%3E%40OGamal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20that%20is%20since%20you%20have%20not%20blanks%20but%20cells%20with%20empty%20string%20(%22%22)%20returned%20by%20formulas.%20Any%20text%20is%20always%20more%20than%20any%20number.%20You%20may%20apply%20conditional%20formatting%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(A1%26gt%3B5000)*(A1%26lt%3B%26gt%3B%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eassuming%20your%20range%20is%20column%20A.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1101731%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1101731%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20response%20Mr.%20Sergei%3CBR%20%2F%3EProblem%20solved.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1101778%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1101778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511751%22%20target%3D%22_blank%22%3E%40OGamal%3C%2FA%3E%26nbsp%3Byou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1922162%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922162%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%3C%2FP%3E%3CP%3EVery%20interesting.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20explain%20the%20logic%20of%20your%20solution%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1922586%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878157%22%20target%3D%22_blank%22%3E%40Practical2k%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EValues%20to%20the%20column%20are%20returned%20by%20formula%2C%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%20condition%2C%20calculated%20number%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThus%20there%20are%20no%20blank%20cells%20here%2C%20only%20numbers%20and%20cells%20with%20empty%20strings.%20If%20use%20in%20logical%20condition%2C%20any%20text%20is%20always%20more%20than%20any%20number.%20That%20means%20that%20%3D%22%22%26gt%3B5000%20returns%20TRUE.%20Not%20to%20color%20cells%20with%20empty%20strings%20we%20add%20(A1%26lt%3B%26gt%3B%22%22)%26nbsp%3B%20using%20AND%20condition.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1925074%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20a%20formula-cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1925074%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%3C%2FP%3E%3CP%3EThanks%20a%20lot.%20Smart%20and%20interesting.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hello everyone,
I got a situation with conditional formatting
I'm trying to highlight cells that contains a value greater than 5000 in a specific column, the problem is that cells in this column contains a specific formula that gives a number in result or a blank result
Cells giving values lower than 5000 aren't formatted and cells with greater value are formatted but as i mentioned above there are cells with a blank result, the conditional formatting is also formatting the blank ones while it should leave it unformatted
What can I do in this case ?

Thanks in advance

Edit : Sorry for my poor English
6 Replies
Best Response confirmed by OGamal (New Contributor)
Solution

@OGamal 

I guess that is since you have not blanks but cells with empty string ("") returned by formulas. Any text is always more than any number. You may apply conditional formatting rule with formula

=(A1>5000)*(A1<>"")

assuming your range is column A.

Thanks for your response Mr. Sergei
Problem solved.

@OGamal you are welcome

@Sergei Baklan 

Very interesting. 

Can you please explain the logic of your solution? 

Thanks. 

@Practical2k 

Values to the column are returned by formula, something like

=IF( condition, calculated number, "")

Thus there are no blank cells here, only numbers and cells with empty strings. If use in logical condition, any text is always more than any number. That means that ="">5000 returns TRUE. Not to color cells with empty strings we add (A1<>"")  using AND condition.

 

@Sergei Baklan 

Thanks a lot. Smart and interesting.