Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comment Flag Color | Excel Discussion (Misc queries) | |||
retain 'update remote reference' flag | Excel Worksheet Functions | |||
Want to change the color of a true/false logical statement with i. | Excel Worksheet Functions | |||
replacing TRUE/FALSE | New Users to Excel | |||
replacing TRUE/FALSE | New Users to Excel |