Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Conditional Formatting on empty cells

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Conditional Formatting on empty cells

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)

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Conditional Formatting on empty cells

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Conditional Formatting on empty cells

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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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.






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Conditional Formatting on empty cells

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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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.









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Conditional Formatting on empty cells

Thank you - that is working for me now!

"T. Valko" wrote:

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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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.






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Conditional Formatting on empty cells

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
Thank you - that is working for me now!

"T. Valko" wrote:

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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"mgccoop" wrote in message
...
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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting on empty cells mgccoop Excel Discussion (Misc queries) 6 July 4th 08 10:41 PM
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
How can I deactivate conditional formatting if the cell is empty? Vahe Excel Discussion (Misc queries) 3 January 11th 07 10:11 PM
Excel - Autom. Filter "Empty / Non Empty cells" should come first Rom Excel Discussion (Misc queries) 0 August 10th 05 04:32 PM
Conditional Formatting Multiple cells based on 2 cells Louis Markowski Excel Worksheet Functions 2 June 1st 05 05:26 PM


All times are GMT +1. The time now is 11:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"