Conditional Formatting - Zeros

hrad

New Member
Joined
Feb 1, 2005
Messages
23
I am using the following formula:
=IF(COUNTIF(CA1A!C7:AD7,">0")=0," ",CA1A!AE7/(COUNTIF(CA1A!C7:AD7,">0")))

If this returns >500 then it is conditioned to turn green.
If this returns <400 then it is conditioned to turn red.
If it is zero it will return red, since it is <400, but I need it to stay blank.

For this formula to work in conjunction with another column which gives back the minimum, I need the above formula to use " " so that it doesn't take the 0 into account.

Any suggestions on how to have both of these functions work together?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
Use the VALUE BETWEEN conditional format and set it between 0 and 400 for the red formatting.

you could also use the AND formula using something similar to :

=AND(A1>0,A1<400)
 

hrad

New Member
Joined
Feb 1, 2005
Messages
23
Since the value that I need in the cell is " ", it doesn't recognize it as a value so the value the between format will not work.
Any other ideas?
 

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
if you formula is in B10, try the following conditional formatting to set the colors

Condition1 = Formula is "=B10>500" --> GREEN
Condition2 = formula is "=AND(B10>0,B10<400)" --> RED
 

Dutchy

Well-known Member
Joined
Sep 18, 2004
Messages
560
I am using the following formula:
=IF(COUNTIF(CA1A!C7:AD7,">0")=0," ",CA1A!AE7/(COUNTIF(CA1A!C7:AD7,">0")))

If this returns >500 then it is conditioned to turn green.
If this returns <400 then it is conditioned to turn red.
If it is zero it will return red, since it is <400, but I need it to stay blank.

If A1 contains a space i.e. " " then this will be deemed to be more than 500 not less than 400 so to fulfil your conditions as specified you need

=AND(A1<>" ",A1>500) condition green
and

=AND(A1<>"0",A1<400) condition red
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,806
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top