Pivot Table that calculates Win/Loss record

**bebiba** · 01-05-2018, 12:39 PM

Is there any way that a pivot table can calculate the win/loss record for a team where the result is the "Win" or "Loss" and the player is the ROWS, and the opponent is the COLUMNS?

I have attached a spreadsheet that shows the data in the format that it comes in as. (columns A-C)
The pivot table that displays the data in the only way that I could figure out how to do it (columns G-J)
and how I would like the results to be displayed (columns L-P)

A Team has a number of players. Those players play against each other. I have created a pivot table that can determine the wins and losses as separate columns, but the players and opponents both have to be in the ROWS section of the pivot table. I would like the Players to be the ROWS, and the opponent be the COLUMNS with the results displayed as "0-3" or "2-1" (W-L).

If anyone has any ideas as to how to do this, I would be eternally grateful!

**Glenn Kennedy** · 01-05-2018, 12:48 PM

I don't like Pivot Tables. give me a formula. Well, four to be exact. Variants of this:

=COUNTIFS($A:$A,$L3,$B:$B,M$2,$C:$C,"Win")&" - "&COUNTIFS($A:$A,$L3,$B:$B,M$2,$C:$C,"Loss")

diiferent shades = different formula in thesheet.

**bebiba** · 01-05-2018, 01:04 PM

Thanks Glen!

I was heading down that path, but the problem is that I need the pivot table to auto-generate the Rows and Columns (there are a variable number of Players and Opponents. Is there a way to use a formula to auto-generate the Players and Opponents as the row headers and column headers?

**sandy666** · 01-05-2018, 01:27 PM

something like that ?

this is idea only

**Glenn Kennedy** · 01-05-2018, 01:53 PM

H3, an array formula, copied down:
=IFERROR(INDEX(Table1[Player],MATCH(0,COUNTIF(Table1[Player],"<"&Table1[Player])-SUM(COUNTIF(Table1[Player],"="&$H$2:H2)),0)),"")

I2, an array formula copied across:
=IFERROR(INDEX(Table1[Opponent],MATCH(0,COUNTIF(Table1[Opponent],"<"&Table1[Opponent])-SUM(COUNTIF(Table1[Opponent],"="&$H$2:H2)),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

the other formulae have been slightly modified to return blanks when required. The array formulae will return the player names in ALPHABETICAL ORDER.

**bebiba** · 01-05-2018, 02:36 PM

Thank you so much Glen! You've been most helpful! I tried to add to your reputation, but you must have answered another question for me b/c I was told to spread it around, but you deserve more reputation

**josephteh** · 01-05-2018, 09:37 PM

Post deleted.

Pivot Table that calculates Win/Loss record

LinkBack

Thread Tools

Rate This Thread

Display

Pivot Table that calculates Win/Loss record

Re: Pivot Table that calculates Win/Loss record

Re: Pivot Table that calculates Win/Loss record

Re: Pivot Table that calculates Win/Loss record

Re: Pivot Table that calculates Win/Loss record

Re: Pivot Table that calculates Win/Loss record

Re: Pivot Table that calculates Win/Loss record

Thread Information

Users Browsing this Thread

Similar Threads

Need pivot table which calculates daily outage %

Pivot Table, use VBA to get use back to record in source data table

Won/Loss record in a billiards tournament.

[SOLVED] Calculate Opponents Win Loss Record

Pivot table refresh causing loss of formatting on worksheet

Ranking a W/L record (win/loss)

loss of accuracy in pivot table

Bookmarks

Bookmarks

Posting Permissions