Numbers- Percentages in Pivot Table

Hi SGIII

I have managed to solve this, of course with your help. The formula stated before in your second answer (SUMIF), I had an error in my table and tests that at first did not yield the results as expected. While cleaning my table, I discovered this error and other issues that prevented the correct data being created.

At the end of my trials and errors, I've finished with this combination:

NO categories whatsoever.

An input table, with no blank rows. Added a Game column (for input the game number), added an S/B column to denote Starter or Bench. Every calculation column is moved at the far right of the table. By doing so, I can paste the game data as provided, deleted every new blank row that comes with it, with minimum or zero re-arrangement of data. A LOT of new columns where needed, a whole bunch of Percentage totals, all created with your example of SUMIF equation. Other total calculation columns. Some IF(COUNTIF) calculations, in order to get some totals correct.

In total, 20 new columns were added, most of them to solve the "per-game" and "percentages" discrepancies in the totals that a Pivot Table cannot resolve by its own nature. That's basically the gist of what needed to be done.

New workflow:

After the game:

Import data will match the table columns perfectly. I then manually add the S and B values to the players (with the help of autofills of course), and proceed to delete the "starter" and "bench" rows (which only contain the text "Starter" or "Bench"). Then it is just a matter of, if needed, auto-filling down the calculations of the 20 columns. Then, go to the Pivot Table sheet, refresh data, and that's it, new calculations are presented and ready to be analyzed. And my original table stays perfectly organize as needed. (No categories to mess with).

This workflow will take, more or less, 5 or 10 minutes per game.

Thank you, SGIII, for your patience. If not for my error, this should have been solved yesterday. Have a great weekend!

Thanks SGIII. I'll try to explain.

As stated before, I receive info per game. That info is organized by Starters and Bench. So player position in the table will vary for each game, depending on who shows, who plays when, etc.. So, problem 1 is that players games are not sorted by player, consecutively down, but by game. Here is a screenshot of partial data for 2 games.

As you can see, there is even a Player 13 in game two that did not play in game 1. But more important, player order is not consistent. SO, even if I could try creating the formula for cell G3 (FGT), it would be manually entered as (Player1FGMGame1 + Player1FGMGame2)/(Player1FGAGame1 + Player1FGAGame2). So far so good, but tedious.

Problem 2 is that if I fill it down, it won't match, because for Player 2 in game one, I would be adding data from Player 6 in game 2, because player 6 now occupies the second position of game 2.

Problem 3, if there was a game three, players in the second game would be receiving info from who knows which player in game three. So there is no way that I can think of for your suggestion of filling down to work.

The only way this approach would work is if I create all formulas for each player from scratch. Then, when game 3 data comes, I would need to go to each player and then manually adding to the formula this new game 3 data. This is obviously tooooo much work.

This is why I was hoping to summarize and group player performances in the Pivot Table, only to find out that percentages total would be all wrong, because it would be adding each game totals separately. This would be easily solved if I could create new columns in a Pivot Table, but that is something that can't be done. So my only option seems to be to copy Pivot Table (data only) to a new sheet and there go on creating the new column for the correct totals. And repeat for each new game :-(

I hope this is clearer now. And of course, I sincerely hope you can find a blatant error or misjudgment on my analysis and show me a better way :-)

Thanks!!

Thanks SGIII

Here is the Pivot Table:

As you can see, 33 of 57 should be 0.57894... not the average of the sum of the 4 individual percentages.

Here is the layout of the table data. Just a small portion (1 game) since all would not fit, but you get the idea. A player, minutes played, points scored, FGM, FGA, and FG% per game:

I would even try without adding the FG% column to the Pivot Table, IF I could make the Pivot Table calculate it from FGM and FGA raw data.

Also, I can calculate what I need on the table, manually, but it would be a major pain. I would need to add to the formula as the season goes on... for each new game, for each player, (and usually the players' position on the table changes by game, for example, if the player starts or comes from the bench, making it harder to automate any other way). Additionally, there are a lot of other calculations that the Pivot Table just make automatically. It is just failing to account for the cumulative percentages, everything else works like a charm.

Thanks.

Thanks SGIII, for your answer (and me stealing off your precious time). That is exactly what I was trying to avoid, and it would not be easy to maintain. Thought about doing that, but I will need to do that in at least 4 or 5 places in the table. And since it is a "live" table, I will need to change it manually after every game. Worse, as stated, player's data is scattered all over the table rows, data is entered as per game, so their results are not consecutively down. Player first game's FGA and FGM will be F6, G6, second game could be F26, G26, third game F47, G47. So I would have needed to find the player all over the table, create the formula for the total percentage, manually (or at least copy and paste) the formula (pretty sure would need to do fixed references for that pasting), then, when new game data comes, I would need to go to those four previous games and change the formula to account for the new data. And that for every player.

I am beginning to think that there is no solution for this, other than copying and pasting the entire Pivot Table (as data, no formulas) into a new sheet, and in the new sheet calculate manually, then repeat for each game. A pain...

Any thoughts?

Thanks SGIII

At first I thought about Categories, but was convinced that it would be a lot of work, "massaging" my raw data to fit what I needed. Right now, that Pivot tables does not seem to work (completely) for what I need, I will revisit Categories. I will go play with it some and report back.

One thing I am afraid of is, in the past, when using these tools, the original order of my table would get messed up (sorting in a Category-enabled table, when turning categories off, would have sorted the entire table also.) That can't happen, since I need to have the ability to go back to the original order of data as entered, from time to time. But maybe I was doing something wrong before, I was just playing with the feature, nothing serious. I'll go play with it now...

I am proceeding by deleting blank rows, there are not many anyway, maybe one between games, and they don't seems to affect the outcome yet.

I also had to add a column for Game number, and a column to identify starters and bench (while deleting those rows from the table), since just creating one single player category messes everything in the table up, when turning off categories there is no way to go back to how the table was but "undo" everything. By adding the columns, I can sort back, with a bit of work, maybe more than anticipated, but at least I get the original order back.

Now the big test, adding the % calculation, THEN adding another game and see how much work I need to invest. (After all, this is not my job, hehe, just helping others, for fun.)

Right now, all my rows are players and their data, including the Team/Coach data, which counts as a player and is used when the game officials can not assign a play (let's say a turnover) to any player in particular. All data are in order of game, then Starter, Bench, and that needs to stay that way in the original table, or at least go back to that format.

One question: If I am organizing my data by categories, is there a way to create team totals? I don't see a way to do that.

Please do not ask the above question, I am changing the approach now. I think I got it.