How do I use Pivot Tables to summarize data in Google Docs
June 4, 2015 8:36 AM Subscribe
I'm trying to figure out how to make a +/- table for my 1 on 1 soccer league. Using Google Docs (link here)
I have a table thats arranged as followed
Date Goal Scored By Goal Against
M Jack Libby
W Jack Frank
F Libby Libby
Multiplied many hundreds of times I would like to spit out a report as follows:
Goals for Goals Against +/-
Jack 2 0 +2
Libby 1 2 -1
Frank 0 1 -1
The first table is constantly being added to, with new names, etc.
I've toyed around with Pivot Tables but I can't get my head around it.
I have a table thats arranged as followed
Date Goal Scored By Goal Against
M Jack Libby
W Jack Frank
F Libby Libby
Multiplied many hundreds of times I would like to spit out a report as follows:
Goals for Goals Against +/-
Jack 2 0 +2
Libby 1 2 -1
Frank 0 1 -1
The first table is constantly being added to, with new names, etc.
I've toyed around with Pivot Tables but I can't get my head around it.
Best answer: Unfortunately, the way your data is set up this isn't really possible, at least not without a lot of gymnastics.
The way this could work is if instead you had a table set up like this instead:
Rows = person
columns = by/against (you'll need to order descending)
values = by/against, summarized by CountA
If you then turn off "show totals" on both row and column, it is easy to set up another column outside the pivot table with by-against.
It looks like it's a little tricky in Google Drive to have it a range or a pivot table automatically expand as you add more rows, so you might need to do that manually or you might need to do a little more research on that.
It's super easy to convert your current data to the above format: Copy rows A:C to E:F so you have two copies of it. In the one on the left, replace the "against" column with the word "by" all the way down (so it says date-personby-"by"), then in the one on the right, delete the "for" column and add the word "against" on the right (so it says date-personagainst-"against", then just cut the whole right-hand table and paste it underneath the left hand table (without the headings) and change the headings. It's a little more work to add the extra row per score, but it will make more data manipulation possible.
posted by brainmouse at 10:24 AM on June 4, 2015 [1 favorite]
The way this could work is if instead you had a table set up like this instead:
Date Person By/Against Opponent M Jack By Libby M Libby Against Jack W Jack By Frank W Frank Against Jack F Libby By Libby F Libby Against LibbyBasically having two rows for each goal. The trick is you need "person" to be one column, and "event type" to be one column. If you have the data like this, you can easily create a pivot table with:
Rows = person
columns = by/against (you'll need to order descending)
values = by/against, summarized by CountA
If you then turn off "show totals" on both row and column, it is easy to set up another column outside the pivot table with by-against.
It looks like it's a little tricky in Google Drive to have it a range or a pivot table automatically expand as you add more rows, so you might need to do that manually or you might need to do a little more research on that.
It's super easy to convert your current data to the above format: Copy rows A:C to E:F so you have two copies of it. In the one on the left, replace the "against" column with the word "by" all the way down (so it says date-personby-"by"), then in the one on the right, delete the "for" column and add the word "against" on the right (so it says date-personagainst-"against", then just cut the whole right-hand table and paste it underneath the left hand table (without the headings) and change the headings. It's a little more work to add the extra row per score, but it will make more data manipulation possible.
posted by brainmouse at 10:24 AM on June 4, 2015 [1 favorite]
I think I'd do two tables, one for goals for, one for goals against, then a vlookup for names.
posted by jeather at 12:04 PM on June 4, 2015
posted by jeather at 12:04 PM on June 4, 2015
The worry with the two separate tables is that if someone has 0 goals for or 0 goals against, they're not going to show up in both tables and a vlookup might miss them.
posted by brainmouse at 12:21 PM on June 4, 2015
posted by brainmouse at 12:21 PM on June 4, 2015
Try putting the Goal Scored in the Rows and the Goal Against in the columns and "Count of Date" in the Values.
Then you have a table like
0 J L F Total
J 0 1 5 6
L 4 0 2 6
F 3 3 0 6
T 7 4 7 18 (this is the totals line)
Now you need a formula on each line that takes the Total column and subtracts cell in the Total Row for that same player. Once you have the formula set up, you should be able to refresh the table without redoing the formulas, but play around with it to be sure. I would set those formulas a few columns over so you have one that pulls the player name and one that does the calculations.
posted by soelo at 12:28 PM on June 4, 2015
Then you have a table like
0 J L F Total
J 0 1 5 6
L 4 0 2 6
F 3 3 0 6
T 7 4 7 18 (this is the totals line)
Now you need a formula on each line that takes the Total column and subtracts cell in the Total Row for that same player. Once you have the formula set up, you should be able to refresh the table without redoing the formulas, but play around with it to be sure. I would set those formulas a few columns over so you have one that pulls the player name and one that does the calculations.
posted by soelo at 12:28 PM on June 4, 2015
Response by poster: Thanks for your help everyone. Glad to see I wasn't struggling for no reason.
Brainmouse, your solution worked wonderfully.
The Pivot-table sizing issue is easy as you just make the range the size of a huge table and hope your data never exceeds it. Thanks again
posted by cacofonie at 8:53 AM on June 5, 2015
Brainmouse, your solution worked wonderfully.
The Pivot-table sizing issue is easy as you just make the range the size of a huge table and hope your data never exceeds it. Thanks again
posted by cacofonie at 8:53 AM on June 5, 2015
« Older How to avoid re-injuring my knee and hip | Avoiding mold on vents from condensation in an old... Newer »
This thread is closed to new comments.
Date Goal Scored By Goal Against
M Jack Libby
W Jack Frank
F Libby Libby
Goals for Goals Against +/-
Jack 2 0 +2
Libby 1 2 -1
Frank 0 1 -1
posted by cacofonie at 8:55 AM on June 4, 2015