Conditional formatting to ignore zeros
 

Hi,
How do I get this:
=COUNTIF($D4,(MIN($D$4:$D$15)))
to ignore zero's?

--
Traa Dy Liooar

Jock

Conditional formatting to ignore zeros
 

Select your range
conditional formatting

Formula is: =A1<0

format any colors you like


"Jock" wrote:

Hi,
How do I get this:
=COUNTIF($D4,(MIN($D$4:$D$15)))
to ignore zero's?

--
Traa Dy Liooar

Jock

Conditional formatting to ignore zeros
 

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
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

Select your range
conditional formatting

Formula is: =A1<0

format any colors you like


"Jock" wrote:

Hi,
How do I get this:
=COUNTIF($D4,(MIN($D$4:$D$15)))
to ignore zero's?

--
Traa Dy Liooar

Jock

Conditional formatting to ignore zeros
 

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



"Jock" wrote in message
...
Hi,
How do I get this:
=COUNTIF($D4,(MIN($D$4:$D$15)))
to ignore zero's?

--
Traa Dy Liooar

Jock

Conditional formatting to ignore zeros
 

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?
--
David Biddulph

"Jock" wrote in message
...
Hi,
How do I get this:
=COUNTIF($D4,(MIN($D$4:$D$15)))
to ignore zero's?
--
Traa Dy Liooar

Jock

Conditional formatting to ignore zeros
 

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

Jock


"Peo Sjoblom" wrote:

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



"Jock" wrote in message
...
Hi,
How do I get this:
=COUNTIF($D4,(MIN($D$4:$D$15)))
to ignore zero's?

--
Traa Dy Liooar

Jock