Pivot Table Day of Week Averages

BBalazs

New Member
Joined
Apr 6, 2013
Messages
11
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
Monday10
Saturday30
Sunday
35
Tuesday20
Wednesday35
Grand Total28,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.

Thanks for your help!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Fruit</td><td style=";">Amount</td><td style=";">Day Of Week</td><td style=";">Count</td><td style="text-align: right;;"></td><td style=";">Row Labels</td><td style=";">Sum of Amount</td><td style=";">Sum of Count</td><td style=";">Sum of Field1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">2013.06.01</td><td style=";">Apple</td><td style="text-align: right;;">10</td><td style=";">Saturday</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style=";">Monday</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">2013.06.01</td><td style=";">Pear</td><td style="text-align: right;;">20</td><td style=";">Saturday</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style=";">Tuesday</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">2013.06.02</td><td style=";">Apple</td><td style="text-align: right;;">30</td><td style=";">Sunday</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style=";">Wednesday</td><td style="text-align: right;;">70</td><td style="text-align: right;;">1</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">2013.06.02</td><td style=";">Pear</td><td style="text-align: right;;">40</td><td style=";">Sunday</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style=";">Saturday</td><td style="text-align: right;;">90</td><td style="text-align: right;;">2</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">2013.06.03</td><td style=";">Apple</td><td style="text-align: right;;">10</td><td style=";">Monday</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Sunday</td><td style="text-align: right;;">70</td><td style="text-align: right;;">1</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">2013.06.04</td><td style=";">Apple</td><td style="text-align: right;;">20</td><td style=";">Tuesday</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">260</td><td style="text-align: right;;">6</td><td style="text-align: right;;">43.33333333</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">2013.06.05</td><td style=";">Apple</td><td style="text-align: right;;">30</td><td style=";">Wednesday</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">2013.06.05</td><td style=";">Pear</td><td style="text-align: right;;">40</td><td style=";">Wednesday</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">2013.06.08</td><td style=";">Pear</td><td style="text-align: right;;">60</td><td style=";">Saturday</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=1/COUNTIF(<font color="Blue">A$2:A$10,A2</font>)</td></tr></tbody></table></td></tr></table><br />
 

BBalazs

New Member
Joined
Apr 6, 2013
Messages
11
I'd never have thought of such a clever workaround. It worked like a charm, thank you very much.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top