Conditional Formatting - Ignore Zeroes

[JMH022]

I am trying to highlight the lowest value in a column of numbers, but I want the conditional formatting to ignore zeroes as the lowest value. Is that possible?

Small Sampling of my column:
$60.17
$69.82
$78.44
$77.93
$77.09
$-
$-
$75.30
$60.17
$75.30
$-
$87.38
$98.16
$97.52
$-
$96.47
$75.33
$75.33

Conditional formatting highlights the 4 "zero" lines. I want it to highlight the lowest number above zero (in this case, the two lines showing 60.17).

Your help is always appreciated,

 

[shg]

Maybe ...

cell value is equal to =small(range, countif(range, 0) + 1)

You could put that whole formula in one cell to avoid computing it for every cell in the range, and use instead

cell value is equal to =thatCell

 

[jmthompson]

Try

=AND(A1>0,A1=MIN(A:A))

 

[JMH022]

Thanks for your replies.

shg: I don't follow what you are telling me to try.



jmthompson: I tried the following

column A
$60.17
$60.17
$69.82
$78.44
$77.93
$77.09
$-
$-

in CF; Using the Range =$A$1:$A$8 and
the formula =AND(A1>0,A1=MIN(A1:A8))
it doesnt work.

If I try Using the Range =$A$1:$A$6 and
the formula =AND(A1>0,A1=MIN(A1:A6)) to exclude the "zeroes"
from the range, it works.

I think it is still recognizing the zeroes as the min value.

 

[jeffreybrown]

Hi Jim,

Try setting the absolutes for the Min range

=AND(A1>0,A1=MIN($A$1:$A$48))

 

[JMH022]

no effect

 

[shg]

Jeffrey,

If 0 is the smallest value, then there is no cell > 0 and that is also the min ...

 

[jeffreybrown]

Yes shg, as usual I did not test properly. The cells were empty and not actually zero giving me an inaccurate result.

I tested with you suggested method and as always perfect...thanks

 

[shg]

Jeffery, I am frequently guilty of the same. You would help many fewer people if you tested everything exhaustively, rather than post what you believe to be correct.

I am never insulted when people say, "It worked fine after I changed Rangle to Range."