Conditional formatting to ignore zeros

T

Teethless mama

Select your range
conditional formatting

Formula is: =A1<>0

format any colors you like
 
J

Jock

Thanks Mama,
In theory, ignoring zeros, there will be a minimum value in the column
(D4:D15) somewhere. This is what I need to make obvious by conditional
formatting.
So, every month will have a running total. At the beginning of June for
instance, one of the previous five months will have the max value of all 5
months and similarly, one will have the min value.
The MAX bit is sorted, but I am having trouble showing the MIN because of
these bloomin' zeros!
Cheers
 
P

Peo Sjoblom

Assuming that you don't have any negative values

=$D4=SMALL($D$4:$D$15,COUNTIF($D$4:$D$15,0)+1)


--


Regards,


Peo Sjoblom
 
D

David Biddulph

Your syntax looks wrong (though it is legal). The first parameter for
COUNTIF is the range, and the second is the criterion. [Look it up in Excel
help.]

You have asked it to count how many cells in the specfiied range meet the
criterion. The range you have specified is the one cell $D4, and the
criterion is that the value in the cell be equal to the minimum of the range
$D$4 to $D$15. You'd therefore get an answer of 0 or 1 from your count.
You'd get the same answer from
=IF($D4=MIN($D$4:$D$15),1,0) or from =--($D4=MIN($D$4:$D$15))

What are you really trying to calculate, where do you want zeros ignored,
and do you realise that conditional Formatting (which you mention in the
subject line) affects only the display, not the underlying value in the
cell?
 
J

Jock

That's got it exactly. Although the zeros are displayed, they're ignored.
Thank you
--
Traa Dy Liooar

Jock


Peo Sjoblom said:
Assuming that you don't have any negative values

=$D4=SMALL($D$4:$D$15,COUNTIF($D$4:$D$15,0)+1)


--


Regards,


Peo Sjoblom
Click to expand...
 
Top