Find Highest Score In List Formula

[Ron]

Hello all,

I'm looking to return the highest score for a users with multiple
scores in a list of other users with multiple scores.

Thank you,
Ron

 

[Gary''s Student]

Say the data is like:

frank 56
joe 9
frank 74
frank 101
jim 143
jim 146
joe 200
frank 164
joe 135
joe 127
joe 177
jim 10
jim 135
jim 53
frank 190
joe 109
jim 193
jim 29
jim 8
jim 107
joe 93
joe 9
jim 153
jim 186
joe 36
jim 174
jim 141
frank 55
jim 92
frank 141
joe 15
frank 5
frank 34
joe 161
jim 103
joe 88
and we want the max score for frank:

=MAX(IF(A1:A36="frank",B1:B36,""))
will return 190

NOTE this is an array formula that must be entered with CNTRL-SHFT-ENTER
rather than just the ENTER key.

 

[B Lynn B]

A perfect and simple learning exercise for a pivot tables novice. Google
"Excel Pivot Table Tutorial" - there are lots of them out there. Once you
have the names in the "Row Labels" section and the scores in the "data"
section, then right-click the data section, pick Field Settings, then Max.

 

[Ron]

Hi Gary's Student, this is exactaly what I"m looking for. Thank you
for your simple solution. This is the final formula =MAX(IF('P-card
data'!$E$2:$E$7284=A2,'P-card data'!$F$2:$F$7284,"")) entered as an
array. Thank you, Ron

 

[Ron]

Hi Lynn, thanks for the info. However, a PivotTable is not suitable
for this project. I plan on taking a look at your suggested reading.
Thank you for your assistance, Ron

 

[Ron]

Hi Gary's Student, I tried using a range name in the formula and it
returned #NUM!. {=MAX(IF(NAME=A2,TransAmount,""))} should this
formula work as an Array Formula.

Thanks for your assistance, Ron