Calculating ratio of two columns in Excel pivot table

Question

I have a table, that keeps track of players scores for a board game. The relevant columns in the source table are Player Name, which is just text, Game Number, which is unique for each game played, and 'Won?', which is 1 if the player won that game, and 0 otherwise. TThere are other columns, but they are just used for grouping.

|| Player  |  Game No. | Won? ||
--------------------------------
|| John    |     1     |   1  ||
|| George  |     1     |   0  ||
|| John    |     2     |   0  ||
|| Paul    |     2     |   1  ||
|| Ringo   |     2     |   0  ||
--------------------------------

Relevant value fields in pivot table:

• 'Win Count', calculated as Sum of Won?

• 'Games Played', calculated as Count of Game No.

I want to add a 'Win Rate' column, which would basically be 'Win Count'/'Games Played' or 'Win Count'.

I tried to add a calculated field 'Win Rate' as "=COUNT(Won?)/COUNT('Game No.')", but this made everything 1, regardless of the actual value.

Answer 1

The percentage of True items in a list is the average of zeros and ones, where True is represented by 1 and False by 0.

Just drag the Won field into the values area, and change the aggregation from Sum to Average:

Got this from my good pal Doug Glancy's blog at http://yoursumbuddy.com/percent-of-true-items-in-a-pivot-table-field/