Pivot Table Calculations

[jim]

I'm wondering if it's possible to perform a specific calculation within
a pivot table. Here's the summary:

I have a query that returns numeric values in columns called "A," "B,"
and "C" for each user in a list. The Grand Total column is the sum of
A, B, and C for each user. I'm interested in showing the percentage of
a user's values belonging to C (e.g. C / SUM(A+B+C)).

Is it possible to do this without introducing any sort of additional
sheet or helper cells?

Thanks for any input.


jim

 

[steven1001]

It sounds like >> Formulas >> Calculated Field will do what you need .

 

[jim]

Thanks for the reply. This doesn't quite solve the problem though.
Let me elaborate a little more.

I have a column in a spreadsheet that will have three values -- A, B,
or C for each of several people. Each person will appear in the list
multiple times (so Bob could have 100 instances in total, for example).
When I plug this into a pivot I'd like to be able to see what
percentage of the total for each person the value "C" makes up (so
Bob's total count of "C" over the total no. of times Bob appears in the
list.

There are other ways to get this information, but for my purposes, a
PivotTable would be the most favorable solution.


Jim

 

[heniek]

You can use Pivot Table. But before you create it, you should add a
second column that will first calculate percentage of the overall
total.
You will also need a "Grand Total" cell at the bottom of the list (even
if it is in in cell B65536 (the last row).

For example.
Column A: Names of persons
Column B: Your values
Column C: Percentage of "Grand Total" (i.e. =B4/$B$65536)

If you do not want to see column C simply hide it.
Then create a Pivot Table and select area "A1:C65535" (note that last
row is NOT included). Pivot Table would then summarize columns B and C
where column C summaries will be actual percentages of the sum, as you
wanted.

Heniek

 

[steven1001]

"Is it possible to do this without introducing any sort of additional
sheet or helper cells?"

I could not do it without helper cells :-(
.. perhaps one of the gurus can advise?

I was able to do it by putting a value of 1 beside each entry, then
doing a sumif of that value by the person's name in the next column,
then divide the "1" by the sumif value in a third column... put the
third column value in the pivot table and sum it and express as a %.

regards..

 

[jim]

Thanks all. I wound up adding a helper column to the data output. I
did learn about using pivot table calculations through this, though.
So big thanks for all the feedback.


jim