Conditional Formatting "" Value showing as Greater Than!

[Jonny Edo]

Hi,
I have a formula in a cell which i need conditional formatting. The formula is as follows;

=IF(ISERROR(AVERAGEIFS($J$17:$J$34,$A$17:$A$34,"Day",$B$17:$B$34,"PF18")),"",(AVERAGEIFS($J$17:$J$34,$A$17:$A$34,"Day",$B$17:$B$34,"PF18")))

My conditional formatting basically states that;
>=$H$39 = Green
<$H$39 = Red
=$H$39="" = Blank (No formatting)

When there is an instance where an error value is returned, i.e. "", the cell is changed to green indicating that it reads the "", as greater than the value in H39!

Any idea why the conditional format would read "" as greater than a number value?

Thanks

 

[Marcelo Branco]

Hi and welcome to Mr Excel Formula

Excel evaluates any text greater than a number

Try the formula option (assumes A1 as the cell containing the formula . Adjust to suit)

=AND(A1<>"",A1>=$H$39) ---> Green

=AND(A1<>"",A1<$H$39) ---> Red

M.

 

[AlphaFrog]

You're not comparing the Cell value to H39 with this...
=$H$39=""
You are comparing H39 to ""

I think you don't want any conditional format for blank if you don't want a colored cell.

 

[Marcelo Branco]

oops...I forgot the 3rd condition H39 = blank ---> no formatting

Try these

=AND(A1<>"",A1>=$H$39,$H$39<>"") ---> Green

=AND(A1<>"",A1<$H$39,$H$39<>"") ---> Red

M.

 

[Marcelo Branco]

Sorry, my formulas are not correct. Try

=AND(ISNUMBER(A1),ISNUMBER($H$39),A1>=$H$39) ---> green

=AND(ISNUMBER(A1), ISNUMBER($H$39), A1<$H$39) ---> red

Change A1 to the cell of interest

M.

 

[Jonny Edo]

oops...I forgot the 3rd condition H39 = blank ---> no formatting

Try these

=AND(A1<>"",A1>=$H$39,$H$39<>"") ---> Green

=AND(A1<>"",A1<$H$39,$H$39<>"") ---> Red

M.

What a legend you are!!!
It worked.
Can I ask why my original formula was not working?

 

[Marcelo Branco]

What a legend you are!!!
It worked.
Can I ask why my original formula was not working?

Because "" or any text is greater than any number. So when the formula returns "" the CF (green) is triggered.

M.