Get MAX value according to TRUE/FALSE flag

T

Tetsuya Oguma

Hi all,

This is fairly simple yet I am novice on this worksheetfunction using
possibly array version of that.

A B C
1 TRUE TRUE FALSE
2 12 14 16

I would like to get maximum number of the first two in Row 2 above. Because
the third one is flagged as FALSE (C1) I don't want to include 16 (C2) as
part of MAX calculation.

I was thinking of "=MAX(A1*A2,B1*B2,C1*C2)" in array formula but in vain...

Any idea?

Thanks for your brain time.
 
K

KL

Hi there,

Your formula works for me and it doesn't need to be array-entered. Another
way could be: =MAX(A1:C1*A2:C2)

I guess you may have problems with numbers format (row 2) - check if they
are really numbers o text.

Regards,
KL
 
T

Tetsuya Oguma

Your formula works for me and it doesn't need to be array-entered. Another
way could be: =MAX(A1:C1*A2:C2)
Click to expand...
If the first flag (A1) is FALSE the formula gives me zero (0)... Why?
 
K

KL

sorry, because the second formula I gave you IS an ARRAY formula
(Ctrl+Shift+Enter)

Regards,
KL
 
T

Tetsuya Oguma

sorry, because the second formula I gave you IS an ARRAY formula
(Ctrl+Shift+Enter)
Click to expand...
Great! ONE more, why MIN function does NOT work against the same set of
data, do you know?
 
K

KL

try this ARRAY formula:

=MIN(IF(A1:C1,A2:C2))

or if you want to exclude 0's and empty cells then

=MIN(IF(A1:C1*A2:C2,A2:C2))

Regards,
KL
 
Top