Subtotals in pivot tables in Excel

2
votes

In the sample data provided there are multiple line entries per day as well as multiple days per week. The sum subtotals in an Excel pivot table are calculate as expected, with the correct sales totals per week (the line entries sum up to total sales for a day and the daily totals sum up correctly to weekly totals).

However, when trying to calculate the average sales per day (in each week), the average sales per line entry is give. Is there any way to calculate the average sale value per day, instead of calculating the average sales value per line entry in every week?

That is, I would like the values in the first subtotal row to be (9+10)/2; (40+35)/2 and (28+24)/2; and in the second subtotal row to be (11+11+10)/3; (30+25+25)/3; and (24+19+20)/3.

enter image description here

The data:

Date,Week,Quantity,Sale Value,Purchase Value
2013/01/02,Week 1,1,10,8
2013/01/03,Week 1,2,20,12
2013/01/02,Week 1,4,10,8
2013/01/02,Week 1,4,20,12
2013/01/03,Week 1,8,15,12
2013/01/06,Week 2,5,20,15
2013/01/07,Week 2,4,15,10
2013/01/08,Week 2,5,10,8
2013/01/06,Week 2,6,10,9
2013/01/07,Week 2,7,10,9
2013/01/08,Week 2,5,15,12

1
excelexcel-2010pivot-table
I suggest you edit your examples, so we can copy and paste, and help you to solve your problem. For your requirements array formulas with IF conditions and/or AVERAGEIF COUNTIF would work without PIVOT. – zx8754
@zx8754 I've added the data. Yes, however this approach will not be pragmatic for the amount of data I'm really working with, and it will not allow slicing the report on various columns. – erichfw

1 Answers

1
votes

I have a workaround, though ugly. Mind you, I seem to recall Microsoft admitted their averaging in PivotTables was buggy (for Excel 2007).

  1. Create your PivotTable as your example but without subtotals and in Tabular Form.

  2. Copy Week1 : 2013/01/08 to somewhere under the PT. Fill in the blanks in the first column of this array. Subtotal the second for each change in the first, using Count and Summary below data.

  3. Move the second column of this array so that 2013/01/02 is on the same row as it is in the PT.

  4. Subtotal Week in the PT.

  5. Filter the columns that contain the PT (not within the PT itself) for Contains tot in the Week column. Put a formula such as =I7/$M7 immediately to the right of the values you moved (in the example N7) copy it across two columns and all three cells down as required.

  6. Unfilter and tidy up as desired.

SO17657195 example