Finding lowest non-zero value

Discuss the spreadsheet application
Post Reply
NCJECulver
Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

Finding lowest non-zero value

Post by NCJECulver »

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?
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Post by Villeroy »

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
(17.54 KiB) Downloaded 169 times
conditional_min.ods
Micro tutorial on arrays
(15.15 KiB) Downloaded 523 times
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
NCJECulver
Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

Re: Finding lowest non-zero value

Post by NCJECulver »

Thanks for the reply.

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
NCJECulver
Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

Re: Finding lowest non-zero value

Post by NCJECulver »

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
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Finding lowest non-zero value

Post by Dave »

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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding lowest non-zero value

Post by acknak »

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

{=MIN(IF(A1:A27>0;A1:A27))}
AOO4/LO5 • Linux • Fedora 23
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Finding lowest non-zero value

Post by Dave »

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.
User avatar
foxcole
Volunteer
Posts: 1507
Joined: Mon Oct 08, 2007 1:31 am
Location: Minneapolis, Minnesota

Re: Finding lowest non-zero value

Post by foxcole »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Post by Villeroy »

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

{=MIN(IF(A1:A27>0;A1:A27))}
:evil: 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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding lowest non-zero value

Post by acknak »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Post by Villeroy »

acknak wrote:Sorry, yes, I misquoted it (although both work).
No offence taken :P 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

=MIN(IF($A2:$A11>0;$A2:$A11;""))
=MIN(IF($A2:$A11>0;$A2:$A11;"Foo"))
BUT:

Code: Select all

=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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding lowest non-zero value

Post by acknak »

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
Post Reply