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: 9

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,917
Messages
5,856,268
Members
431,802
Latest member
ProfessorCalculus

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