Pivot Table Day of Week Averages

[BBalazs]

Hi All!

I've been searching all over the place for a solution to this issue. While I found many similar threads, none have helped me resolve the problem, so I'm asking for your help.

I have data arranged in the following manner (but it cannot be sorted this reliably):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Fruit
[/TD]
[TD]Amount
[/TD]
[TD]Day Of Week
[/TD]
[/TR]
[TR]
[TD]2013.06.01
[/TD]
[TD]Apple
[/TD]
[TD]10
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]2013.06.01
[/TD]
[TD]Pear
[/TD]
[TD]20
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]2013.06.02
[/TD]
[TD]Apple
[/TD]
[TD]30
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD]2013.06.02
[/TD]
[TD]Pear
[/TD]
[TD]40
[/TD]
[TD]Sunday
[/TD]
[/TR]
[TR]
[TD]2013.06.03
[/TD]
[TD]Apple
[/TD]
[TD]10
[/TD]
[TD]Monday
[/TD]
[/TR]
[TR]
[TD]2013.06.04
[/TD]
[TD]Apple
[/TD]
[TD]20
[/TD]
[TD]Tuesday
[/TD]
[/TR]
[TR]
[TD]2013.06.05
[/TD]
[TD]Apple
[/TD]
[TD]30
[/TD]
[TD]Wednesday
[/TD]
[/TR]
[TR]
[TD]2013.06.05
[/TD]
[TD]Pear
[/TD]
[TD]40
[/TD]
[TD]Wednesday
[/TD]
[/TR]
[TR]
[TD]2013.06.08
[/TD]
[TD]Pear
[/TD]
[TD]60
[/TD]
[TD]Saturday
[/TD]
[/TR]
</tbody>[/TABLE]
... and so on. Please note the last line, that will be important later on.

Using this data, I've been trying to create a Pivot Table on Average Amount of Fruit Sold per Day of Week. Perhaps it would be easier with array formulas, but I'm really going for a Pivot Table (Pivot Chart actually) because I can add slicers later on (a particularly important feature).

The problem is that if I create a Pivot table with Day Of Week as rows (category axis) and amount as values, things don't add up. While having a "sum of amount" things are fine, but the problem is that I am looking for averages. If I modify the function to "average fo amount", what I get is the following:

[TABLE="width: 221"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels
[/TD]
[TD]Average of Amount[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]Sunday
[/TD]
[TD="align: right"]35
[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]28,88888889
[/TD]
[/TR]
</tbody>[/TABLE]

Please note Saturday. We had amounts 10, 20 and 60 for Saturday, the average of which data is 30 alright, but there were only 2 actual occurrences of Saturday in the dataset (06.01. and 06.08.) so the correct value would be 45.

Do you have any idea how I could resolve this issue? The actual Dataset is pretty large and much more varied, that is why we're using pivot tables and slicers in the first place; but if this issue is resolved pretty much everything is.

Thanks for your help!

 

[Andrew Poulsom]

The only way I can thing of to get what you want is to add a column named Count to your source data with the formula:

=1/COUNTIF(A$2:A$10,A2)

Then in the data area of your pivot table put Sum of Amount, Sum of Count and a calculated field to divide one by the other. Example:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Date	Fruit	Amount	Day Of Week	Count		Row Labels	Sum of Amount	Sum of Count	Sum of Field1
2	2013.06.01	Apple	10	Saturday	0.5		Monday	10	1	10
3	2013.06.01	Pear	20	Saturday	0.5		Tuesday	20	1	20
4	2013.06.02	Apple	30	Sunday	0.5		Wednesday	70	1	70
5	2013.06.02	Pear	40	Sunday	0.5		Saturday	90	2	45
6	2013.06.03	Apple	10	Monday	1		Sunday	70	1	70
7	2013.06.04	Apple	20	Tuesday	1		Grand Total	260	6	43.33333333
8	2013.06.05	Apple	30	Wednesday	0.5
9	2013.06.05	Pear	40	Wednesday	0.5
10	2013.06.08	Pear	60	Saturday	1
Sheet4

Cell Formulas
Range	Formula
E2	=1/COUNTIF(A$2:A$10,A2)

 

[BBalazs]

I'd never have thought of such a clever workaround. It worked like a charm, thank you very much.