Straight Average of Subtotal Data in Pivot Table

[mcparente]

I want the pivot table to generate a Grand average that is the average of the subtotal/summary data above it, not an average of all the underlying data.

The results of a pivot table are below.

How do I get a straight Grand Average instead of a weighted grand average for this pivot table? For example, the straight average of (20, 15, 20, 23, and 19) is 19; however, the pivot table's default grand average is to take the weighted average for all of the underlying data.


Average of Total:
Title Item #of Items Avg. Total
ABC A 4 20
ABC B 6 15
ABC C 7 20
ABC D 3 23
ABC E 1 19

 

[Mark W.]

Why do you want an average of an average?

 

[mcparente]

I want an average of an average because I want to weight each item equally even though each item has a different total number (N). I want to be able to automatically get the straight average within the pivot table so that I can create a pivot chart with dynamic results.

 

[Mark W.]

The PivotTable Average summary function doesn't produce a weighted average.

 

[mcparente]

I guess Excel cannot produce an average of an average. Too bad

Instead, I created another pivot table that points to my original pivot table and takes an average of the rows in my original pivot table. It's a workaround, but at least it's an automated way of getting the average.

Thanks for your help.

 

[Mark W.]

Maybe because an average of an average doesn't have any statistical meaning.

 

[mcparente]

It's not a real average that I am using. I could have used max or min since the underlying values are all the same for each item. I guess I just wanted to take an average of all the underlying unique items in the pivot table, which equated to taking "an average of an average".

 

[Mark W.]

It's not a real average that I am using. I could have used max or min since the underlying values are all the same for each item. I guess I just wanted to take an average of all the underlying unique items in the pivot table, which equated to taking "an average of an average".

You're confused about how a PivotTable summary function works.

Book2
	A	B	C	D	E	F	G
1	Field1			Average of Field1	Total
2	20			Total	19.4
3	15
4	20
5	23
6	19
7
8
9
Sheet1

 

[mcparente]

Thanks for your help