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.

--
Regards,
Tom Ogilvy



"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> .
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=375644
>

[davidm]

Tom,

I appreciate your point. The curious thing is that the formula works with the MAX function. Again, even if the TRUE,FALSE are coerced to 1s 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.

--
Regards,
Tom Ogilvy


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> Tom,
>
> I appreciate your point. The curious thing is that the formula works
> with the MAX function. Again, even if the TRUE,FALSE are coerced to 1s
> 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.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=375644
>

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