Average of Multiple Columns from multiple Pivot Tables

**element32d** · 04-01-2013, 09:34 AM

Hello, I've attached an example of what I'm trying to do for reference.

If you look in the "PivotTables" sheet, in yellow there is the pivot tables created using the data from the "Data" sheet. If you scroll to the right in the "PivotTables" sheet, in green you'll see what I'm trying to do, which is combine all the weekly usage from the 4 pivot tables but keep the labels so I can calculate a weekly average. It's not working the way it is setup now.

Any suggestions would be appreciated, thanks!

**Melvinrobb** · 04-01-2013, 10:39 AM

A pivot table wasn't going to work for that scenario, because it thinks the headers you had in column B referenced to all of the later columns, which was not the case.
Would having all of your data in one table, with a column indicating week #, not be the easiest?

**element32d** · 04-01-2013, 10:58 AM

That would work, but the way I was getting the data in the "Data" sheet was from 4 separate Access database queries. So unless I changed my query logic which I'm not sure if I can do or not, I couldn't stack the data like you did.

**Melvinrobb** · 04-01-2013, 11:08 AM

I would suggest using formulas then (SUMIF or INDEX/MATCH), that reference Column B and C for the Week #4 numbers, column D and E for the week #3 numbers, etc......