Baseball Results: Master Player List & Dynamic Stats

[Eric Alan]

Hello,

I am working a project to keep track of my own baseball statistics for this season. My goal is that every day I am going to paste the results for each player from the previous day's game to the bottom of my running results list and have that information update a master list of stats for each player. Here are the bumps I am running into in creating that master list and am looking at a way to solve them:

1. Multiple players with the same name (playing on different teams - thankfully there aren't any players with the same name on the same team currently).
2. Multiple results for the same players (for different day's games).
3. Different criteria for stats I am creating (average hits in last 30 games, average at bats in last 10 games, etc).
4. Not every player plays every day.

The first issue is pretty straight forward - I would just add a column and use the CONCATENATE formula to ensure players with the same name are treated separately. EDIT: Unique ID for each player created.

The other issues are where I'm running into my roadblock. How do I:

1. Create a master list of unique players that...
2. ...calculates statistics based on the most recent "n" games played by each individual player in my running results list?

It would be simple to do this if I were interested in only the most recent "n" days, but that wouldn't account for differing number of games played by each player within a given date range.

Per Ali's request, I have removed my original attachment and provided a clean copy in the below reply. Thanks!

Any suggestions? Thank you in advance!

Best regards,
Eric

[AliGW]

Welcome to the forum!

A few observations. Firstly, you say this:

It would be simple to do this if I were interested in only the most recent "n" days, but that wouldn't account for differing number of games played by each player within a given date range.

yet you have provided only one day's worth of statistics in the sample file. I think you can probably see that this won't be sufficient.

Secondly, please have a read through this advice for providing a good sample workbook:

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Thirdly, the best way to deal with players of the same name is to create a master list somewhere and allocate each player a unique ID (this could be a number or a code). This can then be used across the workbook to identify players.

[Eric Alan]

Thank you for the welcome, Ali!

I have attached an updated .xlsx file to this reply with a more representative sample of the data I am working with (Tab: Sample Input Data). I have also created a mock-up of the desired output (Tab: Desired Solution). I hope this is exactly what you have requested.

On the "Sample Input Data" tab:

• Data from most recent game played is pasted at the bottom of the sheet. No data is deleted at any point.

On the "Desired Solution" tab:

• Column A: Unique ID (all IDs pasted from the "Sample Data Input" tab and duplicates removed using the Data menu)
• Column B: Average Hits/Game for the most recent 30 games played by that player (column F from the "Sample Data Input" tab)
• Column C: Average Hits/Game for the most recent 3 games played by that player (column F from the "Sample Data Input" tab)
• Column D: Average ABs/Game for the most recent 10 games played by that player (column D from the "Sample Data Input" tab)
• For those cells that do not meet the minimum number of games played, a "-" is displayed until sufficient data becomes available.

Thank you again for your help!

Best regards,
Eric

[AliGW]

May I just ask whether your version of Excel is a 365 subscription version? It might make a difference to the solution offered.

[Eric Alan]

May I just ask whether your version of Excel is a 365 subscription version? It might make a difference to the solution offered.

Yes, it is.

[AliGW]

Good. And would you be willing to add a helper column to the games data sheet (this could be hidden)?

[AliGW]

Please find attached.

I have added a helper column (D) to the source data sheet (this could be hidden, if desired):

Excel 2016 (Windows) 32 bit

	D
1	Games
2	=COUNTIFS('Sample Input Data'!$H$2:H2,"<>",'Sample Input Data'!$B$2:B2,B2)

Sheet: Sample Input Data

Then I have used the following formulae on the outcomes sheet:

Excel 2016 (Windows) 32 bit

	B	C	D
2	=IF(COUNTIFS('Sample Input Data'!G:G,"<>",'Sample Input Data'!B:B,'Desired Solution'!A2)<30,"-",AVERAGEIFS('Sample Input Data'!G:G,'Sample Input Data'!B:B,'Desired Solution'!A2,'Sample Input Data'!D:D,">"&MAXIFS('Sample Input Data'!D:D,'Sample Input Data'!B:B,'Desired Solution'!A2)-30))	=IF(COUNTIFS('Sample Input Data'!G:G,"<>",'Sample Input Data'!B:B,'Desired Solution'!A2)<3,"-",AVERAGEIFS('Sample Input Data'!G:G,'Sample Input Data'!B:B,'Desired Solution'!A2,'Sample Input Data'!D:D,">"&MAXIFS('Sample Input Data'!D:D,'Sample Input Data'!B:B,'Desired Solution'!A2)-3))	=IF(COUNTIFS('Sample Input Data'!E:E,"<>",'Sample Input Data'!B:B,'Desired Solution'!A2)<10,"-",AVERAGEIFS('Sample Input Data'!E:E,'Sample Input Data'!B:B,'Desired Solution'!A2,'Sample Input Data'!D:D,">"&MAXIFS('Sample Input Data'!D:D,'Sample Input Data'!B:B,'Desired Solution'!A2)-10))

Sheet: Desired Solution

There may be a more elegant solution, but this one works.

[Eric Alan]

Good. And would you be willing to add a helper column to the games data sheet (this could be hidden)?

Yes, of course.

Can I just check that the numbers you have added in your dummy data are the numbers (averages) that you expect? I have a solution, but it is not giving the same numbers, so I want to check this first.

Yes, they are all the exact numbers I expect. I just double checked them all and there are no fat-finger errors in my manual entry.

Something to consider is that each row indicates that the player played in that game and the most recent games are at the bottom of the list. I am essentially looking to count games from bottom to top for each player to get to the required number of games for each stat.

[AliGW]

Have you looked at the solution I have offered?

[AliGW]

Something to consider is that each row indicates that the player played in that game and the most recent games are at the bottom of the list. I am essentially looking to count games from bottom to top for each player to get to the required number of games for each stat.

So my formulae can be shortened to this:

Excel 2016 (Windows) 32 bit

	B	C	D
2	=IF(COUNTIF('Sample Input Data'!B:B,'Desired Solution'!A2)<30,"-",AVERAGEIFS('Sample Input Data'!G:G,'Sample Input Data'!B:B,'Desired Solution'!A2,'Sample Input Data'!D:D,">"&MAXIFS('Sample Input Data'!D:D,'Sample Input Data'!B:B,'Desired Solution'!A2)-30))	=IF(COUNTIF('Sample Input Data'!B:B,'Desired Solution'!A2)<3,"-",AVERAGEIFS('Sample Input Data'!G:G,'Sample Input Data'!B:B,'Desired Solution'!A2,'Sample Input Data'!D:D,">"&MAXIFS('Sample Input Data'!D:D,'Sample Input Data'!B:B,'Desired Solution'!A2)-3))	=IF(COUNTIF('Sample Input Data'!B:B,'Desired Solution'!A2)<10,"-",AVERAGEIFS('Sample Input Data'!E:E,'Sample Input Data'!B:B,'Desired Solution'!A2,'Sample Input Data'!D:D,">"&MAXIFS('Sample Input Data'!D:D,'Sample Input Data'!B:B,'Desired Solution'!A2)-10))

Sheet: Desired Solution

[Eric Alan]

Have you looked at the solution I have offered?

I have yes, and it looks like a FANTASTIC start!

There were three instances though (godleza01, rayro02, shiplbr01) where the counter was duplicating games due to missing data within the input data. Once I entered zeros where there were blanks, the results were exactly as expected.

Can the formula be modified to avoid this?

[AliGW]

It's up to you. Either you add zeroes into the source data as a matter of course, or we can look at tweaking the formula. It's up to you - let me know - but the former would be easier.

EDIT: There's an error in the helper column formula - sorry!

Try this instead:

=COUNTIFS('Sample Input Data'!$G$2:G2,"<>",'Sample Input Data'!$B$2:B2,B2)

[Eric Alan]

It's up to you. Either you add zeroes into the source data as a matter of course, or we can look at tweaking the formula. It's up to you - let me know - but the former would be easier.

EDIT: There's an error in the helper column formula - sorry!

Try this instead:

=COUNTIFS('Sample Input Data'!$G$2:G2,"<>",'Sample Input Data'!$B$2:B2,B2)

That worked! Thank you so much!

[AliGW]

Glad to have helped!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Eric Alan]

This was SOLVED, but I have another request.

The previous formulas work perfectly for calculating only the last "n" number of games as specified. Now, I'm looking to calculate it when those minimums are not met. I know it's right there in front of me, but for some reason I can't see it. I'm looking for a formula that will do the following:

• Calculate Avg Hits/Game ONLY when Games Played < 30 (and output "n >= minG" when Games Played >= 30)
• Calculate Avg ABs/Game ONLY when Games Played < 10 (and output "n >= minG" when Games Played >= 10)

I have attached a sample of my data and the output I'm looking for highlighted in yellow. Thank you again!!!

Best regards,
Eric

[AliGW]

This is a different query, so I suggest you start a new thread with a title that outlines the new problem. Thanks!

[Eric Alan]

This is a different query, so I suggest you start a new thread with a title that outlines the new problem. Thanks!

Done. See here: https://www.excelforum.com/excel-for...ames-10-a.html. Thanks!