Complete amateur trying to put a Soccer Statsheet together for my Team.

E

ExcelNoob2022

New Member
Joined
Apr 3, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am very new with Excel and trying to use my Googlefu to help as much as possible but finally got stuck and I found this website and am hoping someone here is nice enough to lend a hand.

For my stats sheet, I want to create a list of the Top Players in several categories. I would like their names to be included in the list.

So far, I have used the LARGE command to successfully get a list of Goals, Assists and Saves in a top 3 order. However, I can't figure out how to get the matching names.

The next issue I have is with the "Points" category. This is calculated by Goals(x2)+Assists. I can get the Goals(x2) with the following command: =LARGE($B$3:$B$17*2,ROWS(A$3:A4)) but whenever I attempt to add in the Assists, I get a !NUM error.

Any advice or help would be greatly appreciated.

Team Statsheet.xlsx
ABCDEFGHIJKLMN
1FULL SEASON STATSWINLOSSTIE
2PlayerGoalsAssistsShotsOffsidesFoulsGoals AllowedSavesShut Out HalvesHat Tricks200
3Adelyn Holexa101002520110
4Alice Felton102000000
5Blakely Haines000000000
6Brooke Risser326001610Team Leaders
7Charlie Zetterberg000000000
8Dempsey Meyers305000000Points
9Emery Schmitz31400371012
10Grace Hart0000000006
11Hayden Haines0000000006
12Holly Beckman000000000
13Hope Hoffman101000000Goals
14Jenna Thurlkill0000000006
15Lauren Martinsen3070000003
16Nia Reilly6080000013
17Zuzu Gilman000000000
18TOTAL213340061841Assists
192
201
210
22
23Saves
247
256
265
Main
Cell Formulas
RangeFormula
L2:N3L2=SUM(Plains:Kalispell!W2)
B3:J17B3=SUM(Plains:Kalispell!B3,Plains:Kalispell!M3,Plains:Kalispell!M22,Plains:Kalispell!B22,)
B18:J18B18=SUM(B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,)
N9:N11N9=LARGE($B$3:$B$17*2,ROWS(A$3:A3))
N14:N16N14=LARGE($B$3:$B$17, ROWS(A$3:A3))
N19:N21N19=LARGE($C$3:$C$17, ROWS(A$3:A3))
N24:N26N24=LARGE($H$3:$H$17, ROWS(A$3:A3))
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
I messed around awhile longer and made some progress. I think I managed to get the names associated but in the "Points" and "Goals" section, the 3rd bronze line only populates the 2nd place person for some reason. Can't figure that out still.

Also struggling to get the Goals(X2)+Assists working for my "Points"

Team Statsheet.xlsx
ABCDEFGHIJKLMN
1FULL SEASON STATSWINLOSSTIE
2PlayerGoalsAssistsShotsOffsidesFoulsGoals AllowedSavesShut Out HalvesHat Tricks200
3Adelyn Holexa101002520110
4Alice Felton102000000
5Blakely Haines000000000
6Brooke Risser326001610Team Leaders
7Charlie Zetterberg000000000
8Dempsey Meyers305000000Points
9Emery Schmitz314003710Nia Reilly12
10Grace Hart000000000Brooke Risser6
11Hayden Haines0000000006
12Holly Beckman000000000
13Hope Hoffman101000000Goals
14Jenna Thurlkill000000000Nia Reilly6
15Lauren Martinsen307000000Brooke Risser3
16Nia Reilly6080000013
17Zuzu Gilman000000000
18TOTAL213340061841Assists
19Brooke Risser2
20Emery Schmitz1
21Adelyn Holexa0
22
23Saves
24Emery Schmitz7
25Brooke Risser6
26Adelyn Holexa5
Main
Cell Formulas
RangeFormula
L2:N3L2=SUM(Plains:Kalispell!W2)
B3:J17B3=SUM(Plains:Kalispell!B3,Plains:Kalispell!M3,Plains:Kalispell!M22,Plains:Kalispell!B22,)
B18:J18B18=SUM(B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,)
L9L9=INDEX(A3:A17,MATCH(LARGE(B3:B17,1),B3:B17,0))
L10L10=INDEX(A3:A17,MATCH(LARGE(B3:B17,2),B3:B17,0))
N9:N11N9=LARGE($B$3:$B$17*2,ROWS(A$3:A3))
L14L14=INDEX(A3:A17,MATCH(LARGE(B3:B17,1),B3:B17,0))
L15L15=INDEX(A3:A17,MATCH(LARGE(B3:B17,2),B3:B17,0))
N14:N16N14=LARGE($B$3:$B$17, ROWS(A$3:A3))
L19L19=INDEX(A3:A17,MATCH(LARGE(C3:C17,1),C3:C17,0))
N19:N21N19=LARGE($C$3:$C$17, ROWS(A$3:A3))
L20L20=INDEX(A3:A17,MATCH(LARGE(C3:C17,2),C3:C17,0))
L21L21=INDEX(A3:A17,MATCH(LARGE(C3:C17,3),C3:C17,0))
L24L24=INDEX(A3:A17, MATCH(LARGE(H3:H17,1), H3:H17,0))
N24:N26N24=LARGE($H$3:$H$17, ROWS(A$3:A3))
L25L25=INDEX(A3:A17, MATCH(LARGE(H3:H17,2), H3:H17,0))
L26L26=INDEX(A3:A17, MATCH(LARGE(H3:H17,3), H3:H17,0))
Named Ranges
NameRefers ToCells
Nia_Reilly=Main!$B$16N9:N11, N14:N16, B18
 
Upvote 0
Here's the latest version of it. The errors are visible on the right hand side under "Points"

The other issue is that the repeated player name under "Goals"

I think those are my last two issues.

Team Statsheet.xlsx
ABCDEFGHIJKLMN
1FULL SEASON STATSWINLOSSTIE
2PlayerGoalsAssistsShotsOffsidesFoulsGoals AllowedSavesShut Out HalvesHat Tricks200
3Adelyn Holexa101002520110
4Alice Felton102000000
5Blakely Haines000000000
6Brooke Risser326001610Team Leaders
7Charlie Zetterberg000000000
8Dempsey Meyers305000000Points
9Emery Schmitz314003710Nia Reilly#NUM!
10Grace Hart000000000Brooke Risser#NUM!
11Hayden Haines000000000#NUM!
12Holly Beckman000000000
13Hope Hoffman101000000Goals
14Jenna Thurlkill000000000Nia Reilly6
15Lauren Martinsen307000000Brooke Risser3
16Nia Reilly608000001Brooke Risser3
17Zuzu Gilman000000000
18TOTAL213340061841Assists
19Brooke Risser2
20Emery Schmitz1
21Adelyn Holexa0
22
23Saves
24Emery Schmitz7
25Brooke Risser6
26Adelyn Holexa5
Main
Cell Formulas
RangeFormula
L2:N3L2=SUM(Plains:Kalispell!W2)
B3:J17B3=SUM(Plains:Kalispell!B3,Plains:Kalispell!M3,Plains:Kalispell!M22,Plains:Kalispell!B22,)
B18:J18B18=SUM(B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,)
L9L9=INDEX(A3:A17,MATCH(LARGE(B3:B17,1),B3:B17,0))
L10L10=INDEX(A3:A17,MATCH(LARGE(B3:B17,2),B3:B17,0))
N9:N11N9=LARGE($B$3:$B$17*2,ROWS(A$3:A3)=LARGE($C$3:$C$17,ROWS(A$3:A3)))
L14L14=INDEX(A3:A17,MATCH(LARGE(B3:B17,1),B3:B17,0))
N14:N16N14=LARGE($B$3:$B$17, ROWS(A$3:A3))
L15L15=INDEX(A3:A17,MATCH(LARGE(B3:B17,2),B3:B17,0))
L16L16=INDEX(A3:A17,MATCH(LARGE(B3:B17,3),B3:B17,0))
L19L19=INDEX(A3:A17,MATCH(LARGE(C3:C17,1),C3:C17,0))
N19:N21N19=LARGE($C$3:$C$17, ROWS(A$3:A3))
L20L20=INDEX(A3:A17,MATCH(LARGE(C3:C17,2),C3:C17,0))
L21L21=INDEX(A3:A17,MATCH(LARGE(C3:C17,3),C3:C17,0))
L24L24=INDEX(A3:A17, MATCH(LARGE(H3:H17,1), H3:H17,0))
N24:N26N24=LARGE($H$3:$H$17, ROWS(A$3:A3))
L25L25=INDEX(A3:A17, MATCH(LARGE(H3:H17,2), H3:H17,0))
L26L26=INDEX(A3:A17, MATCH(LARGE(H3:H17,3), H3:H17,0))
Named Ranges
NameRefers ToCells
Nia_Reilly=Main!$B$16N14:N16, N9:N11, B18
 
Upvote 0
AhoyNC said:
Maybe this for your cell N3:
=LARGE((B3:B17*2)+(C3:C17),ROWS($A$3:A3))
Click to expand...
Thanks for the reply. I tried it out and there was no error but it still isn't quite right. Maybe the names are messed up as well?

Team Statsheet.xlsx
ABCDEFGHIJKLMN
1FULL SEASON STATSWINLOSSTIE
2PlayerGoalsAssistsShotsOffsidesFoulsGoals AllowedSavesShut Out HalvesHat Tricks200
3Adelyn Holexa101002520110
4Alice Felton102000000
5Blakely Haines000000000
6Brooke Risser326001610Team Leaders
7Charlie Zetterberg000000000
8Dempsey Meyers305000000Points
9Emery Schmitz314003710Nia Reilly12
10Grace Hart000000000Brooke Risser12
11Hayden Haines000000000Brooke Risser8
12Holly Beckman000000000
13Hope Hoffman101000000Goals
14Jenna Thurlkill000000000Nia Reilly6
15Lauren Martinsen307000000Brooke Risser3
16Nia Reilly608000001Brooke Risser3
17Zuzu Gilman000000000
18TOTAL213340061841Assists
19Brooke Risser2
20Emery Schmitz1
21Adelyn Holexa0
22
23Saves
24Emery Schmitz7
25Brooke Risser6
26Adelyn Holexa5
Main
Cell Formulas
RangeFormula
L2:N3L2=SUM(Plains:Kalispell!W2)
B3:J17B3=SUM(Plains:Kalispell!B3,Plains:Kalispell!M3,Plains:Kalispell!M22,Plains:Kalispell!B22,)
B18:J18B18=SUM(B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,)
L9L9=INDEX(A3:A17,MATCH(LARGE(B3:B17,1),B3:B17,0))
N9:N11N9=LARGE((B3:B17*2)+(C3:C17),ROWS($A$3:A3))
L10L10=INDEX(A3:A17,MATCH(LARGE(B3:B17,2),B3:B17,0))
L11L11=INDEX(A3:A17,MATCH(LARGE(B3:B17,3),B3:B17,0))
L14L14=INDEX(A3:A17,MATCH(LARGE(B3:B17,1),B3:B17,0))
N14:N16N14=LARGE($B$3:$B$17, ROWS(A$3:A3))
L15L15=INDEX(A3:A17,MATCH(LARGE(B3:B17,2),B3:B17,0))
L16L16=INDEX(A3:A17,MATCH(LARGE(B3:B17,3),B3:B17,0))
L19L19=INDEX(A3:A17,MATCH(LARGE(C3:C17,1),C3:C17,0))
N19:N21N19=LARGE($C$3:$C$17, ROWS(A$3:A3))
L20L20=INDEX(A3:A17,MATCH(LARGE(C3:C17,2),C3:C17,0))
L21L21=INDEX(A3:A17,MATCH(LARGE(C3:C17,3),C3:C17,0))
L24L24=INDEX(A3:A17, MATCH(LARGE(H3:H17,1), H3:H17,0))
N24:N26N24=LARGE($H$3:$H$17, ROWS(A$3:A3))
L25L25=INDEX(A3:A17, MATCH(LARGE(H3:H17,2), H3:H17,0))
L26L26=INDEX(A3:A17, MATCH(LARGE(H3:H17,3), H3:H17,0))
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1FULL SEASON STATSWINLOSSTIE
2PlayerGoalsAssistsShotsOffsidesFoulsGoals AllowedSavesShut Out HalvesHat Tricks200
3Adelyn Holexa101002520110
4Alice Felton102000000
5Blakely Haines000000000
6Brooke Risser326001610Team Leaders
7Charlie Zetterberg000000000
8Dempsey Meyers305000000Points
9Emery Schmitz314003710Nia Reilly12
10Grace Hart000000000Brooke Risser8
11Hayden Haines000000000Emery Schmitz7
12Holly Beckman000000000
13Hope Hoffman101000000Goals
14Jenna Thurlkill000000000Nia Reilly6
15Lauren Martinsen307000000Brooke Risser3
16Nia Reilly608000001Dempsey Meyers3
17Zuzu Gilman000000000
18TOTAL213340061841Assists
19Brooke Risser2
20Emery Schmitz1
21Adelyn Holexa0
22
23Saves
24Emery Schmitz7
25Brooke Risser6
26Adelyn Holexa5
Data
Cell Formulas
RangeFormula
M9:N11M9=INDEX(SORT(CHOOSE({1,2},A3:A17,B3:B17*2+C3:C17),2,-1),SEQUENCE(3),{1,2})
M14:N16M14=INDEX(SORT(A3:B17,2,-1),SEQUENCE(3),{1,2})
M19:N21M19=INDEX(SORT(A3:C17,3,-1),SEQUENCE(3),{1,3})
M24:N26M24=INDEX(SORT(A3:H17,8,-1),SEQUENCE(3),{1,8})
Dynamic array formulas.


BUT these formula will not work in merged cells.
 
Upvote 0
Solution
Fluff said:
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1FULL SEASON STATSWINLOSSTIE
2PlayerGoalsAssistsShotsOffsidesFoulsGoals AllowedSavesShut Out HalvesHat Tricks200
3Adelyn Holexa101002520110
4Alice Felton102000000
5Blakely Haines000000000
6Brooke Risser326001610Team Leaders
7Charlie Zetterberg000000000
8Dempsey Meyers305000000Points
9Emery Schmitz314003710Nia Reilly12
10Grace Hart000000000Brooke Risser8
11Hayden Haines000000000Emery Schmitz7
12Holly Beckman000000000
13Hope Hoffman101000000Goals
14Jenna Thurlkill000000000Nia Reilly6
15Lauren Martinsen307000000Brooke Risser3
16Nia Reilly608000001Dempsey Meyers3
17Zuzu Gilman000000000
18TOTAL213340061841Assists
19Brooke Risser2
20Emery Schmitz1
21Adelyn Holexa0
22
23Saves
24Emery Schmitz7
25Brooke Risser6
26Adelyn Holexa5
Data
Cell Formulas
RangeFormula
M9:N11M9=INDEX(SORT(CHOOSE({1,2},A3:A17,B3:B17*2+C3:C17),2,-1),SEQUENCE(3),{1,2})
M14:N16M14=INDEX(SORT(A3:B17,2,-1),SEQUENCE(3),{1,2})
M19:N21M19=INDEX(SORT(A3:C17,3,-1),SEQUENCE(3),{1,3})
M24:N26M24=INDEX(SORT(A3:H17,8,-1),SEQUENCE(3),{1,8})
Dynamic array formulas.


BUT these formula will not work in merged cells.
Click to expand...
THANK YOU! I believe this is it. You rock! I really appreciate it.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
U
  • Question Question
Information overwriting when transferring
Replies
7
Views
485
UTB
U
M
  • Question Question
Dynamic Cell Editing from Another Sheet
Replies
3
Views
410
Cubist
Cubist
ajjava
  • Question Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
2K
ajjava
ajjava

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top