average of sumed data in pivot tabel

Y

Yann

In a pivot table, I would like to have the average of the sumed data, and
make the average update automatically when the table size expends.

Qtr Sum of Sales Amount

Qtr1 28,441.99 |
Qtr2 37,738.23 | I want the average of these sums.
Qtr3 2,691.03 |
Qtr4 2,960.13 |

in the pivot table there no function as "average of Qtr sums", so I can make
one manually, but it won't take the new data into acount when the table
expends


Yann
 
G

Gary''s Student

Move off the column:

Say your pivot table has been placed in columns D & E and looks like:

Sum of Sales Amount
Quarter Total
q1 49
q2 33
q3 42
q4 26
(blank)
Grand Total 150

Then in another cell (just not in column E) enter:

=AVERAGE(E:E)/2

It does not matter how far down the pivot table goes, the formula just
removes Grand Total from the average.
 
Y

Yann

that won't wotk because under these sums , there are other sums that must not
be added to the average.
but thanks anyway.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average of Subtotals in Pivot Table 0
Avg in a Pivot? 1
Pivot Table 4
Pivot Tables - Sum then Average 1
Calculate Average in pivot item 6
pivot table - get the average of a field 1
Column Number as parameter in Query to select data 1
Weighted Average in Pivot table 1

Top