# Pivot Table Day of Week Averages

#### BBalazs

##### New Member
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):
 Date Fruit Amount Day Of Week 2013.06.01 Apple 10 Saturday 2013.06.01 Pear 20 Saturday 2013.06.02 Apple 30 Sunday 2013.06.02 Pear 40 Sunday 2013.06.03 Apple 10 Monday 2013.06.04 Apple 20 Tuesday 2013.06.05 Apple 30 Wednesday 2013.06.05 Pear 40 Wednesday 2013.06.08 Pear 60 Saturday

<tbody>
</tbody>
... 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:

 Row Labels Average of Amount Monday 10 Saturday 30 Sunday 35 Tuesday 20 Wednesday 35 Grand Total 28,88888889

<colgroup><col><col></colgroup><tbody>
</tbody>

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.

#### Andrew Poulsom

##### MrExcel MVP
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:

#### BBalazs

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

1,082,334
Messages
5,364,681
Members
400,810
Latest member
elbashka

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...