## Finding lowest non-zero value

### Finding lowest non-zero value

I'd like to find the lowest non-zero value in a cell range which may or may not have one or more zero-value cells. I've managed the following, which seems to work:

MIN(A1:A27;COUNTIF(A1:A27;0)+1)

Is there a better way? Are there any hidden gotchas in the above?
NCJECulver

Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

### Re: Finding lowest non-zero value

Your formula may return the COUNTIF result, which does not occur in the list. What about negative numbers?
 Edit: Attached small demo with array formulas

 Edit: Attached a second demo with DMIN
Attachments
DMIN_gt_0.ods
conditional_min.ods
Micro tutorial on arrays
Last edited by Villeroy on Tue Jun 18, 2019 11:18 am, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Finding lowest non-zero value

There's no chance of negative numbers.

I'm not sure what you mean by "return the COUNTIF result"? The COUNTIF result should be the number of zeroes in the range, which is what i want returned.

Thanks for the examples. I haven't had time to study them in detail yet.

CJ
NCJECulver

Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

### Re: Finding lowest non-zero value

0
9
8
7
6

MIN(A1:A27) => 0
COUNTIF(A1:A27;0)+1 => 2

MIN( A1:A27 ; COUNTIF(A1:A27;0)+1 ) <=> MIN( 0 ; 2 ) => 0
Seems to fail in any case.

My solution converts certain values to "", which is an empty string. Could be any other string as well. MIN, MAX, COUNT, AVERAGE and similar functions disregard string values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Finding lowest non-zero value

MIN( A1:A27 ; COUNTIF(A1:A27;0)+1 ) <=> MIN( 0 ; 2 ) => 0

Doh! My apologies. You're right of course that MIN fails in exactly the way you describe. However, I carelessly typed MIN when in fact I'm using SMALL. Mea culpa.

My formula is actually SMALL(A1:A27;COUNTIF(A1:A27;0)+1), which in my tests seems to fare much better than MIN, up until all values in the range are zero, at which point I get a #VALUE error.

Sorry to have wasted your time on that.

CJ
NCJECulver

Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

### Re: Finding lowest non-zero value

This caught my attention.

Data in column A

In B1: =RANK(A1;\$A\$1:\$A\$11)
Copy down column B.

Somewhere: =LOOKUP((MAX(B1:B11)-1);A1:A11)

David.
Dave

Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

### Re: Finding lowest non-zero value

What's wrong with the formula in Villeroy's sample file?

{=MIN(IF(A1:A27>0;A1:A27))}
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: Finding lowest non-zero value

acknak wrote:What's wrong with the formula in Villeroy's sample file?

{=MIN(IF(A1:A27>0;A1:A27))}

Nothing.

What is wrong with alternate examples, as happens often enough, thank goodness? What is wrong with trying to be of assistance? And what is wrong with alternate opinions?

David.
Dave

Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

### Re: Finding lowest non-zero value

Dave wrote:What is wrong with alternate examples, as happens often enough, thank goodness? What is wrong with trying to be of assistance? And what is wrong with alternate opinions?

Relax, my friend, I'm certain it was an honest question. I'm sure acknak is referring back to NCJECulver's post prior to yours, in which NCJECulver apologizes for typing MIN, when SMALL was intended.
Cheers!
---Fox

OOo 3.2.0 Portable, Windows 7 Home Premium 64-bit

foxcole
Volunteer

Posts: 1507
Joined: Mon Oct 08, 2007 1:31 am
Location: Minneapolis, Minnesota

### Re: Finding lowest non-zero value

acknak wrote:What's wrong with the formula in Villeroy's sample file?

{=MIN(IF(A1:A27>0;A1:A27))}

This is not my formula! It's {=MIN(IF(A1:A27>0;A1:A27;""))}
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Finding lowest non-zero value

Sorry, yes, I misquoted it (although both work).

And sorry, Dave (and others), I simply liked Villeroy's suggestion and thought perhaps it had been overlooked, or that it didn't do something that the OP needed.

I didn't mean to imply there was anything wrong with the other answers.
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: Finding lowest non-zero value

acknak wrote:Sorry, yes, I misquoted it (although both work).

No offence taken I just wanted to stomp my foot on the ground as well. Interestingly, your version of the formula works. In my opinion it should not work because a missing 3rd IF-argument evaluates to zero (FALSE), which is exactly the value we want to exclude. I thought my formula works because it substitutes values<=0 with a string. The following are equivalent since MIN ignores all strings:
Code: Select all   Expand viewCollapse view
`=MIN(IF(\$A2:\$A11>0;\$A2:\$A11;""))=MIN(IF(\$A2:\$A11>0;\$A2:\$A11;"Foo"))`

BUT:
Code: Select all   Expand viewCollapse view
`=MIN(IF(\$A2:\$A11>0;\$A2:\$A11;FALSE()))=MIN(IF(\$A2:\$A11>0;\$A2:\$A11;0))=MIN(IF(\$A2:\$A11>0;\$A2:\$A11))`

should be equivalent, but they are not. The last one disregards zero although IF(0>0;TRUE()) always returns zero (FALSE).
Filed issue http://www.openoffice.org/issues/show_bug.cgi?id=91061
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Finding lowest non-zero value

Hmm, I thought an empty branch in an array-IF was (more-or-less) equivalent to an empty cell, which MIN ignores.

However, that can't be right because =COUNTA(IF(D1:D10>0;D1:D10)) always returns 10, so there must be something in the array cells.

Maybe if the array approach is dependent on Calc's internal inconsistencies, or a particular quirk of it's MIN function (ignoring strings), one of the other, more explicit, approaches would be better.

In the spirit of diverse solutions, my first thought was something like this:
=IF(MIN(A1:A27)>0; MIN(A1:A27); SMALL(A1:A27; 2))
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3