Finding lowest non-zero value
-
- Posts: 18
- Joined: Tue Mar 04, 2008 7:17 am
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?
MIN(A1:A27;COUNTIF(A1:A27;0)+1)
Is there a better way? Are there any hidden gotchas in the above?
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
- (17.54 KiB) Downloaded 169 times
-
- conditional_min.ods
- Micro tutorial on arrays
- (15.15 KiB) Downloaded 527 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 18
- Joined: Tue Mar 04, 2008 7:17 am
Re: Finding lowest non-zero value
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
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
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 18
- Joined: Tue Mar 04, 2008 7:17 am
Re: Finding lowest non-zero value
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.MIN( A1:A27 ; COUNTIF(A1:A27;0)+1 ) <=> MIN( 0 ; 2 ) => 0
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
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.
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.
Re: Finding lowest non-zero value
What's wrong with the formula in Villeroy's sample file?
{=MIN(IF(A1:A27>0;A1:A27))}
{=MIN(IF(A1:A27>0;A1:A27))}
AOO4/LO5 • Linux • Fedora 23
Re: Finding lowest non-zero value
Nothing.acknak wrote:What's wrong with the formula in Villeroy's sample file?
{=MIN(IF(A1:A27>0;A1:A27))}
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.
Re: Finding lowest non-zero value
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.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?
Cheers!
---Fox
OOo 3.2.0 Portable, Windows 7 Home Premium 64-bit
---Fox
OOo 3.2.0 Portable, Windows 7 Home Premium 64-bit
Re: Finding lowest non-zero value
This is not my formula! It's {=MIN(IF(A1:A27>0;A1:A27;""))}acknak wrote:What's wrong with the formula in Villeroy's sample file?
{=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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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.
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
Re: Finding lowest non-zero value
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:acknak wrote:Sorry, yes, I misquoted it (although both work).
Code: Select all
=MIN(IF($A2:$A11>0;$A2:$A11;""))
=MIN(IF($A2:$A11>0;$A2:$A11;"Foo"))
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))
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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))
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