Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Should this be for Excel 2007? –  Ivo Flipse Feb 15 '10 at 19:26

1 Answer 1

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.

share|improve this answer
    
Actually, I found easier ways to do this, using dynamic ranges in Excel. I’ll try and elaborate soon, in case other folks come by this question. Thanks for the help, though. –  yonatron Mar 1 '10 at 15:49
    
Fair enough. I can see how you could use dynamic ranges to see the data in the PivotTable as an expanding source of data for a formula and calculate your averages outside the table. I was just trying to answer the question as asked, so that the averages were in the PT in the first place. –  AdamV Mar 1 '10 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.