Conditional Formatting - Zeros

[hrad]

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?

 

[BuddieB]

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]

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]

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

 

[KleptoOne]

Try this formula instead.

=AND(A1<>" ",A1<400)

 

[Dutchy]

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