Dynamic Tracking of Players

[devil4mheaven]

I’m making a sheet which can track the daily match stats & amount earned.

There are in total 7 people who have to be divided into 2 teams. Yes, 1 team will be short of a player.

There are almost 60 matches between different teams.

Each week the team changes randomly. What I need is how will I be able to keep a tracking of how much each player has won, as the team changes per week.

I have maintained a sheet for

1) weekwise matches which will give me either Team A or Team B as winner

2) Team for a current week.

So to rephrase the question again, how will I dynamically check weekwise, whether a player belonged to Team A || Team B & update individual stats accordingly

 

[jason]

So a player ("Bob") could be on team A one week and then on team B another week? but these 2 teams always play eachother?! sounds confusing.


do you (or do you want to) keep track of each weeks stats in the same spreadsheet? or would you have a new spreadsheet for each weeks stats?

 

[Faseeh]

Hi devil4mheaven,


Can you upload a sample file with some sample data? There are few other threads on Score Keeping you can also follow them (but don't cross post please).


Faseeh

 

[devil4mheaven]

Yes Jason, thats the case. It would be better if its in the same spreadsheet.


@Faseeh. Ill upload my excel file when i reach home. Sites are blocked at work.


I did search the forums for score keeping, but could not find a proper solution.


Thanks

 

[jason]

to start you'd want your first column to represent the date of the game (or however, you wish to track the date) then your players listed (this will mean that you'll have the day in A2 repeated each time for however many players you have, a filed for which team they played on, a field to indicate the winning team.

 

[Sajan]

Hi,

There are many ways you could setup the required data... here is one suggested layout.


Table 1: Weekly player assignments, and team wins

[pre]

Week	P1	P2	P3	P4	P5	P6	P7	TA	TB
25-Jan	TA	TA	TA	TA	TB	TB	TB	25	35
2-Feb	TB	TB	TA	TA	TB	TB	TA	45	15
15-Feb	TA	TB	TA	TB	TA	TB	TA	50	10
In the above table, P1, P2, etc. stands for the players.  TA and TB stands for the two teams.


For ease of reference, name the data range for P1 to P7 in the heading as "Players".


In the heading, the two right most cells are the list of teams.  For ease of reference, name those two cells as "Teams".


Name the data range for the dates as "GameWeeks".


Name the data range for the player assignments as "PlayerAssignments".


Name the data range for the winner statistics (such as 25, 35, etc. above) as "WinnerStats". 


Then, use the following formula to create table 2 as shown below, showing the number of wins per player per week.

=INDEX(WinnerStats, MATCH($A9,GameWeeks,0), MATCH(INDEX(PlayerAssignments, MATCH($A9,GameWeeks,0), MATCH(B$8,Players, 0)),Teams, 0))


In the formula, A9 stands for the date in the left column.  B8 refers to the player name (P1, P2, etc.)


Table 2: Stats of winnings by player by week

Week	P1	P2	P3	P4	P5	P6	P7
25-Jan	25	25	25	25	35	35	35
2-Feb	15	15	45	45	15	15	45
15-Feb	50	10	50	10	50	10	50

[/pre]
(The formula is for calculating the per player stats, such as 25 wins for P1 on Jan 25, etc. If you have another table that indicates the amount to payout for a given week / player, etc., it would be straightforward to calculate the winning amounts by player by week.)


(Due to firewall restrictions on my side, I am not able to upload files or view uploaded files. As such, feel free to recreate the worksheet using the instructions above.)


Cheers,

Sajan.

 

[NARAYANK991]

Hi ,


Another possibility :


https://www.dropbox.com/s/wclhaexee1rea27/Player_Stats.xlsx


Narayan