Max function

[dave8]

I have a worksheet with the following data:


Column A Column B
John 60
Ann 80
Joe 50
John 75
Pete 95
Ann 75
Joe 65



I want to be able to get the highest (or Lowest) score in Column B, based on the Name in Column A. For example, I want the highest score for John. In this case, would be 75. Is there a Max function or something similar to get this?

 

[Matty]

Try:

=MAX(IF(A1:A10="John",B1:B10))

Array entered...

Matty

 

[danzon]

=SUMPRODUCT(LARGE((A:A="John")*(B:B),1))

reg formula


then you could change the 1 to a 2 to see who what John's second highest score was... and so on

 

[p45cal]

Excel Workbook
	A	B	C	D	E	F
14	Name	Something		Name	Max	Min
15	John	60		John	75	60
16	Ann	80		Ann	80	75
17	Joe	50		Joe	65	50
18	John	75		Pete	95	95
19	Pete	95
20	Ann	75
21	Joe	65
Sheet

Note that F15 is array-entered (Formula Array above). Then copy the formulae down.

 

[dave8]

Thanks for this tip. Okay, how to translate into VBA? I need to somehow be able to get different scores by changing only the name and not have to redo the formula.

 

[scottylad2]

In what way do you mean different scores?

 

[dave8]

What I mean by that is be able to get the max or min scores using by name.

 

[p45cal]

Thanks for this tip. Okay, how to translate into VBA? I need to somehow be able to get different scores by changing only the name and not have to redo the formula.

vba?! just change the name in any of the rows in my offering, delete the other rows. No formula change required at all.

 

[dave8]

I can't get it to work. This works in VBA:

Selection.FormulaArray = "=Max(IF(B2:B16=""Web Training"",E2:E16))"

But this doesn't work:

' Selection.FormulaArray = "'=Max(IF(B2" & ":B" & num & " = " & "" & "Web Training" & "" & "))"


I'm trying to built the formula by plugging in variables. The name, "Web Training", I want to be able to assign a name. Can you help me with this?

 

[mikerickson]

I've found that, when building formulas, its easier to build range variables and then use .Address than it is to parse the address in the formula creation

Dim BcolumnRange as Range
Dim searchTerm as String

Set BcolumnRange = Range("B2:B16")
searchTerm = "Web Training"

Selection.FormulaArray = "= MAX(IF(" & BcolumnRange.Address(false,false) & "=" & Chr(34) & searchTerm & chr(34) & "," & BcolumnRange.Offset(0, 3).Address(false,false) & "))"