MINIMUM OF POSITIVE VALUES only

[davidm]

Just out of curiousity, in finding the MINIMUM of positive values in a
range
(say, A1:A10), can anyone explain why the ARRAY FORMULA

=MIN(($A1:$10>0,$A1:$10) returns zero, regardless; but
=MAX(($A1:$10>0,$A1:$10) gives correct results.

The logic of Array construct in Formula suggests to me the formula
should work just as well for MIN as it does MAX and SUM (with AVERAGE
and STDEV being exceptions for obvious reasons).

PS: I have no problem with the IF qualification which never fails viz.
=MIN(IF($A1:$10>0,$A1:$10))


TIA
..

 

[Tom Ogilvy]

Making those array formulas would return an array of True's or False's for
the first argument and then all the actual values for the second argument.
There is no conditioning involved.

 

[davidm]

Tom,

I appreciate your point. The curious thing is that the formula work
with the MAX function. Again, even if the TRUE,FALSE are coerced to 1
and 0s (using *1 or --), the MIN formula still returns 0.

Assuming the data in A1:A6 are 6,0,8,0,0,3
=MIN((A1:A6)>0,A1:A6) --
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}{6;0;8;0;0;3}
and this evaluates to 0 instead of 3
=MAX((A1:A6)>0,A1:A6) --
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}{6;0;8;0;0,3}
but correctly evaluates to 8 (in spite of the TRUEs & FALSEs)

Again,
=MIN(--(A1:A6)>0,A1:A6) --> {1;0;1;0;0;1}{6;0;8;0;0;3}
and still evaluates to 0 instead of 3
=MAX(--(A1:A6)>0,A1:A6) --> {1;0;1;0;0;1}{6;0;8;0;0,3}
and yields 8, as before.

I am still baffled

 

[Tom Ogilvy]

Both MAX and MIN ignore True or False or text values. So Max just looks at
the cells that contain numeric values. Try making all your values negative
or zero and MAX will return zero rather than the highest negative value as
would be the case if it is "working" as you claim.

so
=MAX((A1:A6)>0,A1:A6)

is no different than

=MAX(A1:A6)

Same for MIN, so it returns the minimum value which is zero.

I think you have just got yourself wrapped up in a knot and need to step
back and think about this a bit more.

 

[MrShorty]

It looks to me like the MAX and MIN functions are working just as they
are designed to. Let me see if I can explain.

As noted: the array formula (A1:A6)>0,A1:16) returns the arrays
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}{6;0;8;0;0;3}. The MIN function
looks at each element of each input array and returns the smallest
value, ignoring boolean and text values. So, to the MIN function, the
array looks like {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;6;0;8;0;0;3}. With
this array as input, and ignoring Boolean values, the smallest
numerical value in the array is 0. The largest numerical value is 8.
(Aside: If you really think the MAX function is working correctly, see
what happens if you switch to negative numbers and try to return the
largest value that is less than 0. In this scenario, the MIN function
will appear to work correctly, but the MAX function won't).

You noted that the function MIN(IF(A1:A6>0,A1:A6)) works. If we look
at this carefully, we see that the array returned by the inner IF
function would be {6;FALSE;8;FALSE;FALSE;3}. With this array as input,
and ignoring boolean values, the smallest value is indeed 3.

Does that make sense?

I'm not an expert on array formulas, but I'm reasonably confident that
that's why it isn't working for you.