SOLVED

New Contributor

# Conditional formatting with a formula-cell

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 ?

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

# Re: Conditional formatting with a formula-cell

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.

# Re: Conditional formatting with a formula-cell

Thanks for your response Mr. Sergei
Problem solved.

# Re: Conditional formatting with a formula-cell

@OGamal you are welcome

# Re: Conditional formatting with a formula-cell

Very interesting.

Thanks.

# Re: Conditional formatting with a formula-cell

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.

# Re: Conditional formatting with a formula-cell

Thanks a lot. Smart and interesting.