Conditional Max,Min

[Cat]

I would like to know if there is a way to pick the maximum
from two columns for a given value. Here is an example
data set:

Point Col1 Col2
A 2 4
B 3 11
A 5 8
B 3 7
A 9 3
B 3 1

So max of A from both columns would be 9
max of B from both column would be 11.

Similarily can you then pick the 2nd largest using the
large function

Thanks, Cat

 

[Dan E]

Cat,

If your data were in A1:C7

Largest A
=LARGE((A2:A7="A")*(B2:C7),1)
2nd Largest A
=LARGE((A2:A7="A")*(B2:C7),2)

Largest B
=LARGE((A2:A7="B")*(B2:C7),1)
2nd Largest B
=LARGE((A2:A7="B")*(B2:C7),2)

Dan E

 

[Frank Kabel]

Hi,

you can try the following function to get the maximum for A:
=MAX(MAX(IF(A2:A9999="A",B2:B9999,0)),MAX(IF(A2:A9999="A",C2:C9999,0)))
you have to enter this formula as array formula (CTRL-SHIFT-RETURN)

HTH
Frank

 

[Dan E]

All formula's are array formulas!
control + shift + enter

Dan E

 

[Peo Sjoblom]

One way

=MAX(IF(A2:A7="B",B2:C7))

entered with ctrl + shift & enter will return 11 for B
for second largest

=LARGE(IF($A$2:$A$7="A",$B$2:$C$7),2)

also array entered

I would replace the conditions ("A" & "B") and put a cell reference there
that way you won't have to edit the formula, just type another value in the
cell

 

[Harlan Grove]

I would like to know if there is a way to pick the maximum
from two columns for a given value. Here is an example
data set:

Point Col1 Col2
A 2 4
B 3 11
A 5 8
B 3 7
A 9 3
B 3 1

So max of A from both columns would be 9
max of B from both column would be 11.

Similarily can you then pick the 2nd largest using the
large function

If the table above (just the values, not the headers) were in A1:C6, then the
largest A value would be given by the array formula

=MAX(IF(A1:A6="A",B1:C6))

and the second largest A value would be given by the array formula

=LARGE(IF(A1:A6="A",B1:C6),2)

For B values, replace "A" with "B".

 

[Harlan Grove]

you can try the following function to get the maximum for A:
=MAX(MAX(IF(A2:A9999="A",B2:B9999,0)),MAX(IF(A2:A9999="A",C2:C9999,0)))
you have to enter this formula as array formula (CTRL-SHIFT-RETURN)

...

Fails if all values are negative.

 

[Frank Kabel]

Hi Harlan

thanks for the tip you're of course right
Frank

 

[Cat]

Thanks all! Your suggestions worked beautifully. I was
not aware of the array function before. Thanks, cat