ExcelColonist
New Member
- Joined
- Feb 1, 2021
- Messages
- 8
- Office Version
- 365
- 2019
- Platform
- Windows
Background: I am "analyzing" data from a large table, for simplicity, I made a dumby excel spreadsheet. My data records races and which place the runner came in.
I have uploaded a photo as a visual aid.
My goal: To create a pivot table the accurately returns the win rate of the race participant based on races they participated in. I would like to organize this data in a pivot table so I can add a timeline to it, and see changes in win rates as the months go by, and also add slicers and so forth since the real table has a few other variables to take into consideration.
My problem: The pivot table I have returns the win rate of a race participant based on all races, including ones the participant didn't race in.
I highlighted Alex as my example, there have been a total of 8 races, and Alex has participated in 5, and has won 4 of them, because the pivot table recognizes all races, it returns a win rate of 50%. But to be accurate, his win rate is 80%
This formula counts the wins of the player, divided by all games that player participated in.
Does anyone have any idea how to incorporate this formula into my pivot table, or is there another way?
I have uploaded a photo as a visual aid.
My goal: To create a pivot table the accurately returns the win rate of the race participant based on races they participated in. I would like to organize this data in a pivot table so I can add a timeline to it, and see changes in win rates as the months go by, and also add slicers and so forth since the real table has a few other variables to take into consideration.
My problem: The pivot table I have returns the win rate of a race participant based on all races, including ones the participant didn't race in.
I highlighted Alex as my example, there have been a total of 8 races, and Alex has participated in 5, and has won 4 of them, because the pivot table recognizes all races, it returns a win rate of 50%. But to be accurate, his win rate is 80%
Excel Formula:
=COUNTIF(MasterTable[1st],[@Name])/(COUNTIF(MasterTable[1st],[@Name])+COUNTIF(MasterTable[2nd],[@Name])+COUNTIF(MasterTable[3rd],[@Name]))
This formula counts the wins of the player, divided by all games that player participated in.
Does anyone have any idea how to incorporate this formula into my pivot table, or is there another way?