Conditional Formatting on empty cells

[mgccoop]

I'm trying to apply conditional formatting on column g where the formula in
each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a
percent value. I want those values greater than $G$4 to be highlighted in
red. The value in G4 is 10%.

Two problems.
1) My highlighting returns all my blank cells in column G in red
2) If I try to format the 'cell value > $G$4" and the value is greater than
10%, the highlighting doesn't show. If I use the formatting of 'cell value >
..1' then it works. I want to be able to change the value in G4 so I need it
to work the first way.

I hope this makes sense.

 

[T. Valko]

The problem is due to your formulas returning a formula blank ("") which is
a TEXT value.

In Excel, a text value will *always* evaluate to be greater than any number.

"text" > 1,000,000,000,000 = TRUE

So: "" > $G$4 = TRUE and the formatting is being applied.

Use the Formula Is option and test that the cell(s) do in fact contain a
number:

=AND(COUNT(A1),A1>$G$4)

 

[mgccoop]

OK I get the info about text always being greater than any value now.
I'm sorry I don't understand the reference to the Count A1. How does that
impact my text value?

If I want to apply your formula below to all the cells in column g (g8:g38)
how do I do that? Can I highlight the range and apply once formula or do I
have to do it for each cell in the range?

Thanks for your help

 

[T. Valko]

I used A1 as generic reference.

If the cell(s) that contain your formulas return the formula blank COUNT
tests those cells to make sure there is a number returned by that formula.
If the formula returns the formula blank COUNT = 0 and this causes the AND
function to be FALSE and not apply the format. If the formula returns a
number then COUNT = 1. If the number is >G4 then the format is applied.

I want to apply your formula below to all the cells in column g (g8:g38)

You can set the formatting all at one time:

Select the range G8:G38 so that G8 is the active cell. The active cell is
the one that's not highlighted.
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=AND(COUNT(G8),G8>$G$4)

Enter the formula *exactly* as above. Don't change it!

Click the Format button
Select the desired style(s)
OK out

 

[T. Valko]

P.S.

If you want to make this really robust you might also want to test and make
sure G4 does in fact contain a number.

As it is, if G4 is empty and Gn contains a number the formatting will be
applied.

So:

=AND(COUNT($G$4,G8)=2,G8>$G$4)

 

[mgccoop]

Thank you - that is working for me now!

I used A1 as generic reference.

If the cell(s) that contain your formulas return the formula blank COUNT
tests those cells to make sure there is a number returned by that formula.
If the formula returns the formula blank COUNT = 0 and this causes the AND
function to be FALSE and not apply the format. If the formula returns a
number then COUNT = 1. If the number is >G4 then the format is applied.


You can set the formatting all at one time:

Select the range G8:G38 so that G8 is the active cell. The active cell is
the one that's not highlighted.
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=AND(COUNT(G8),G8>$G$4)

Enter the formula *exactly* as above. Don't change it!

Click the Format button
Select the desired style(s)
OK out

 

[T. Valko]

You're welcome. Thanks for the feedback!