Niklas Nebel wrote:
Joe Smith wrote:
I thought I understood this at first, but now I surely don't.
Why does this work: =MIN( IF((A5:A9>0)*A5:A9; A5:A9))
While these do not: =MIN( IF((A5:A9>0)*A5:A9; A5:A9; 0))
=MIN( IF((A5:A9>0)*A5:A9; A5:A9; 0=1))
Actually, I understand why the last two _don't_ work, but it makes a
complete mystery of why the first one /does/.
I thought I was starting to get a handle on how array functions work,
but the more I see, the less I understand.
The first case (an empty "if" path) is recognized separately in the
intermediate result array. It's treated much like an empty value, turned
into FALSE (0 with boolean format) only when shown in a cell, converted
to text, etc.
So, in a normal expression, the result of IF( 0; 100 ) would be 0, but
here the result is nil (empty).
So if A5:A9 is {3,2,1,0,-1}, then
{=MIN( IF(A5:A9>0; A5:A9))} --> 1
{=COUNT( IF(A5:A9>0; A5:A9))} --> 3
{=SUM( IF(A5:A9>0; A5:A9))} --> 6
I never saw an IF with less than two arguments before, much less had any
idea that such a thing could produce an empty result in an array formula.
That's very handy! Is this behavior documented anywhere (I couldn't find
it in the online help for IF), or is it a wizard's secret?
Is there any way to make this explicit in the formula? E.g. to write IF(
0; 100; () ) to indicate that the empty result was intended.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
