Tjyylli

I often make PivotTables in which each row contains a number of per-person average measures. I then want to look at the unweighted column average for each measure, and usually make some kind of chart from these.

Because my individual cells are often averaged from different numbers of data points, the Grand Total row ends up being a weighted average, which I’m not interested in. So I usually make my own average row a few rows above the table to use for my charts.

That’s not too much work, but there’s another problem. I often add a few more people’s worth of data to the PivotTables’ source, then refresh the tables. This means my average row needs to be updated to encompass more rows from the PivotTable. Not a huge deal with one table, but when I have lots of them across lots of sheets, I have to do find/replace on a whole bunch of formulas.

So: is there a way to automatically get unweighted column averages in a PivotTable, such that when the table is refreshed, the averages

1. don’t change locations and


2. encompass the newly added (or removed) data

Thanks

This is a bit kludgy but it works.
If you have a single 'category' (eg person), you can just use AverageIf in the following example. If you have multiple criteria (eg person, product or gender, or hometown) you need AverageifS (plural - only available from Excel 2007).

You need a helper column next to your data. In here we will find:

• is this the first occurence of this person (plus second, third criteria)?


• if so, what is the average for that person?


• if not, BLANK (not zero).

So, if you person's name is in column A, your value to be measured is in column B, row 1 is headings and row 2 to 1000 is data, in cell C2 you need a formula like this:

=IF(COUNTIF($A$2:A2,A2)=1,AVERAGEIF($A$2:$A$1000,A2,$B$2:$B$1000),"")

If you have two criteria (eg person and product, region, or whatever), extend this using COUNTIFS and AVERAGEIFS (your measured value is now in column C, and note this becomes the first argument of the averageifS, not the last):

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,AVERAGEIF($C$1:$C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2),"")

Now you have a column where each person's average is displayed exactly once.

Include a heading for this extra helper column ("Personal average").
Change the source of your pivot table to include this extra data and refresh.
INclude the extra column and make sure you summarise it by average rather than sum. Now your subtotals per category or your grand totals (if you have no higher-level categories) will be accurate, since in every case the average for each person is simply the average of a single number which is already calculated, so the weighting has been suppressed.

I know it's a bit klunky but when done the finished formula can always be copied down as you add data (or use a Table so Excel does that for you), and the result is part of your pivot table so you can easily produce a single report.