Ask Your Question
0

highlight lowest value in column

asked 2017-07-30 16:53:07 +0200

m46 gravatar image

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

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2017-07-30 18:21:50 +0200

Lupp gravatar image

updated 2017-07-30 18:25:44 +0200

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.

edit flag offensive delete link more
0

answered 2017-07-30 21:42:15 +0200

librebel gravatar image

updated 2017-07-30 21:42:44 +0200

Hello @m46,

you could use

=MINIFS($D$9:$D$167;$D$9:$D$167;">0")
edit flag offensive delete link more
0

answered 2017-07-31 11:56:00 +0200

m46 gravatar image

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.

edit flag offensive delete link more
0

answered 2017-07-31 12:35:39 +0200

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")

edit flag offensive delete link more

Comments

thanks a lot Mike that seems to have done the trick

m46 gravatar imagem46 ( 2017-07-31 12:57:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-30 16:53:07 +0200

Seen: 617 times

Last updated: Jul 31 '17