Is this possible? Adding equations and formulas to pivot tables

ExcelColonist

New Member
Joined
Feb 1, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. 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%

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?
 

Attachments

  • WinRate.PNG
    WinRate.PNG
    35.6 KB · Views: 2

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ExcelColonist

New Member
Joined
Feb 1, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Perhaps if we can make a link between the Master Table to the Accurate Win Rate table?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

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
Top