Conditional formatting exclude 0?

**TJ Mustang** · 10-08-2009, 07:15 PM

I want to conditionally format the highest and lowest number in a range of numbers in a single column (Column C). The numbers in Column C are created by a formula that simply adds the data in the two cells in the same row of Columns A & B.

Numbers are entered into columns A & B daily. The column C formula has been filled into enough rows to complete the entire year. Since future days do not have numbers filled in for Columns A & B, Column C for those days results in a sum of 0.

I have set up conditional formatting for Column C so the cell of the highest number in filled in green. I want to conditionally format Column C so the cell with the lowest number is filled red, but it automatically fills all the cells for future days in Column C in red because the sum displayed there is 0.

Is there a way to exclude the 0 sum cells? The date is filled in as simple text, rather than formatted as a day. Is that the answer?

**TJ Mustang** · 10-08-2009, 07:19 PM

I take back what I wrote about the dates being formatted as text. They are, in fact, formatted as dates

**daddylonglegs** · 10-08-2009, 07:34 PM

If you have data in C1:C500 then select that range and use this formula in conditional formatting

=C1=MIN(IF(C$1:C$500>0,C$1:C$500))

format as required

**teylyn** · 10-08-2009, 07:33 PM

Hi,

you could avoid having the zero there in the first place, by creating the sum only when both A and B have values, like

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",B1+A1)

Then you can keep your current conditional formatting. Otherwise, there'd have to be some sophisticated calculations with ranks etc going on in the conditional formula. I think just avoiding the zeros is easier.

hth

**yorkuvir** · 12-17-2018, 02:48 AM

Originally Posted by teylyn

Hi,

you could avoid having the zero there in the first place............. Otherwise, there'd have to be some sophisticated calculations with ranks etc going on in the conditional formula. I think just avoiding the zeros is easier.

hth

Sorry to post in a old thread, but this tip has helped me plenty after having spent a long futile time conditional formatting a column that was having zero values.

Now, having avoided the zeroes using this suggestion, it was a breeze to arrive at a very simple solution.

Thank You, teylyn !