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):
<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:
<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!
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>
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.
Thanks for your help!
