Get MAX value according to TRUE/FALSE flag
 

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.
---
Tetsuya Oguma, Singapore

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

"Tetsuya Oguma" <Tetsuya wrote in message
...
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.
---
Tetsuya Oguma, Singapore

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

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

Regards,
KL

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

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

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


"Tetsuya Oguma" wrote in message
...
sorry, because the second formula I gave you IS an ARRAY formula
(Ctrl+Shift+Enter)
Great! ONE more, why MIN function does NOT work against the same set of
data, do you know?