Conditional Max,Min

  • Thread starter Thread starter Cat
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
Click to expand...

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".
 
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)
Click to expand...
...

Fails if all values are negative.
 
Thanks all! Your suggestions worked beautifully. I was
not aware of the array function before. Thanks, cat
 
Back
Top