Apache OpenOffice Community Forum

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?

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

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

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.

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

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.

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

{=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))}

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 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.

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

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.

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: BUT: 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

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