Finding lowest non-zero value

Discuss the spreadsheet application

Finding lowest non-zero value

Postby NCJECulver » Tue Jun 24, 2008 11:30 am

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

Postby Villeroy » Tue Jun 24, 2008 12:35 pm

Your formula may return the COUNTIF result, which does not occur in the list. What about negative numbers?
 Edit: Attached small demo on array formulas 
Attachments
conditional_min.ods
Micro tutorial on arrays
(15.15 KiB) Downloaded 116 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Postby NCJECulver » Tue Jun 24, 2008 2:54 pm

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
NCJECulver
 
Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

Re: Finding lowest non-zero value

Postby Villeroy » Tue Jun 24, 2008 3:13 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Postby NCJECulver » Tue Jun 24, 2008 10:21 pm

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

Postby Dave » Tue Jun 24, 2008 11:34 pm

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

Postby acknak » Wed Jun 25, 2008 5:20 am

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

{=MIN(IF(A1:A27>0;A1:A27))}
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding lowest non-zero value

Postby Dave » Wed Jun 25, 2008 5:26 am

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

Postby foxcole » Wed Jun 25, 2008 7:03 am

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

Re: Finding lowest non-zero value

Postby Villeroy » Wed Jun 25, 2008 3:05 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Postby acknak » Wed Jun 25, 2008 4:23 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding lowest non-zero value

Postby Villeroy » Wed Jun 25, 2008 5:06 pm

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   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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding lowest non-zero value

Postby acknak » Wed Jun 25, 2008 6:39 pm

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))
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: Villeroy and 21 guests