How does this formula work ?

[kittronald]

Why does the following formula work the way it does ?

=MIN(IF({0,1,2,3}>0,{0,1,2,3}))

Using the Evaluate Formula dialog box, you see the IF test resolve
to {FALSE,TRUE,TRUE,TRUE}.

But the IF TRUE result returns {FALSE,1,2,3}.

How does this formula replace the 0 with FALSE and leave the
numbers unaffected ?


- Ronald K.

 

[joeu2004]

Why does the following formula work the way it does ?
      =MIN(IF({0,1,2,3}>0,{0,1,2,3})) [....]
How does this formula replace the 0 with FALSE and leave
the numbers unaffected ?

Every IF expression has two parts: value if true, and a value if
false. If the value-if-false is missing, it is implicitly FALSE.

So your MIN expression is effectively:

=MIN(IF({0,1,2,3}>0,{0,1,2,3},{FALSE,FALSE,FALSE,FALSE}))

Now, the operation of that expression is as if you wrote (but this is
not legal syntax):

=MIN({IF(0>0,0,FALSE),IF(1>0,1,FALSE},IF(2>0,2,FALSE),
IF((3>0,3,FALSE)})

Since 0>0 is false, that conceptual IF expression is replaced with the
value-if-false part, which is FALSE. Since 1>0, 2>0 and 3>0 are all
true, those conceptual IF expressions are replaced with the value-if-
true part, which 1, 2 and 3 respectively.

Thus, you effectively get the formula =MIN({FALSE,1,2,3}).

Now, looking at the MIN help page, you will see that MIN ignores logic
values. So the effect of the original MIN expression is return the
MIN of the values greater than zero, namely the MIN of 1, 2 and 3.

 

[kittronald]

Joe,

Thanks for the detailed explanation - now I understand a bit more.

However, I've been trying for days, with no avail, to get this
concept to work with the formula below:

Search for my post in this group for "Getting smallest number > 0
in an array constant".

- OR -

http://groups.google.com/group/micr...ber+>+0+in+an+array+constant#978835d828ae629a



-Ronald K.