Using Min Function with a qualifier
Posted by Martin on July 25, 2001 5:32 PM
I need to find out the minimum from a range of numbers but I don't want to pick out a zero number.
For example in the range of 1, 3, 0, 4 , 8 the minimum should return 1
Posted by Mark W. on July 25, 2001 5:37 PM
{=MIN(IF({1,3,0,4,8},{1,3,0,4,8},""))}
This is an array formula which must be entered
using the Control+Shift+Enter key combination.
The outermost braces, {}, are not entered by
you -- they're supplied by Excel in recognition
that the formula uses or returns an array.
Posted by Aladin Akyurek on July 26, 2001 12:39 AM
The 3rd arg in IF is not required, is it?
Do you have any info on , although a tiny matter,which one would evaluate faster, the IF or the rel op as in the equivalent formula follows?
{=MIN((A1:A5<>0)*(A1:A5))}, where A1:A5 contains the elements of {1,3,0,4,8}.
Posted by Mark W. on July 26, 2001 11:01 AM
> The 3rd arg in IF is not required, is it?
No, it's not.
> ...which one would evaluate faster...
I'm not sure, but {=MIN(({1,3,0,4,8}<>0)*({1,3,0,4,8}))}
will result in 0 since 0 is one of the multiplicands.
{1,1,0,1,1}*{1,3,0,4,8} = 0.
Posted by Mark W. on July 26, 2001 11:02 AM
Correction...
Posted by Aladin Akyurek on July 26, 2001 12:48 PM
Re: Correction...
Slept right thru it I see. But, my question 'IF vs rel op' stays, unless "I'm not sure" was an answer to it.
Posted by Mark W. on July 27, 2001 6:19 AM
IF v. Operator
I believe we'd have to know more about how the
compiler treats IF(). Would it be a branch
instruction? Is that faster than multiplication?
I'm afraid that the difference may be so small
that we'd be hard pressed to evaluate it.
