Largest number in column

P

peteewheat11

I am working with football stats.

Column A is the player number. Column B is the yards for each play. A
typical sheet could look like:

<Player> <Yards>
10 21
10 14
10 7
14 1
14 3
10 22
14 11

I have a separate sheet that has each player number in a cell. I need to
find and display the longest yard made by each player.

10 (Longest Yards)
14 (Longest Yards)

Any help would be greatly appreciated!
 
J

Jacob Skaria

In Sheet2 try the below formula..and copy down as required

Col A Col B
Player Max Yards
10 =MAX(IF((Sheet1!A1:A10=A2),Sheet1!B1:B10))
14 =

If this post helps click Yes
 
J

Jacob Skaria

Forgot to mention that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes
 
P

peteewheat11

Super! Thanks for the very quick response!

Jacob Skaria said:
Forgot to mention that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes
 
S

Shane Devenshire

Here is a non-array solution:

=SUMPRODUCT(MAX((Sheet1!A2:A12=A1)*Sheet1!B2:B12))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top