i will try to make this understandable , i am trying to highlight the lowest value in column D ,between D5 & D167 , the column necessarily contains some zeros . I need to highlight the lowest value above zero but the formula I,m using D5=MIN($D$9:$D$167) always highlights the zeros. Is there a way to ignore the zeros and highlight the lowest value? Hope that makes sense to someone. Any help greatly appreciated
A few formulae to get the minimum excluding zero values:
{=MIN(IF($D$9:$D$28=0;"";$D$9:$D$28))}
=SUMPRODUCT(MIN(IF(D9:D28>0;D9:D28;"N")))
{=1/MAX(IFERROR(1/D9:D9;"N"))}
You surely know that the curly brackets are not to type in, but are displayed by Calc to give notice of the fact that the formula was entered for array-evaluation with Ctrl+Shift+Enter. SUMPRODUCT forces array-evaluation for its parameters without depending on this “trick”.
The first two formulae above are very similar and utilise the fact that text is ignored by MIN(). The third excludes zero values with the help of the error they generate if used as a divisor.
There are surely many variants. What they have in common will be that they are questionable concerning the distinction between a real number with a very small amount and an exact zero value.
hi guys thanks a lot for your time but none of the above seem to work =MINIFS($D$9:$D$167;$D$9:$D$167;">0") highlights everything in the column, {=MIN(IF($D$9:$D$28=0;"";$D$9:$D$28))} and {=1/MAX(IFERROR(1/D9:D9;“N”))} don’t highlight anything while =SUMPRODUCT(MIN(IF(D9:D28>0;D9:D28;“N”))) has a very strange effect and highlights values from row 5-24 and 145-164. I really hope i’m not making some fundamental error in inputting the formulae because i’m new to this but I don’t think so.
You need to apply a conditional formatting to the $D$9:$D$167 range, with condition equal to Formula =D9=MINIFS($D$9:$D$167;$D$9:$D$167;">0")
thanks a lot Mike that seems to have done the trick