1) See attached file zAverage is Off_1.jpg
In the two far left coloumns I have Expenses Per Country. I am using "Summarize Values By Sum" to get the total Expenses for each country. For example, in Germany the total expense is $498.78. And in Hungary it is $221.50.
In the two far right columns I have Average Daily Expenses Per Country. I am using "Summarize Values By Average". My intention is to get the average expense per day in each country. For example, in Germany there are 5 days (27-Sept, 30-Sept, 1-Oct, 7-Oct, and 8-Oct). Excel provides the average as $24.94. This is wrong. The average should be $498.78/5 days = $99.76.
The same occurs with Hungary which has 6 days (Sept 24, 25, 27, 28, 29, 30). The average expense per day for Hungary should be $221.50/6 days = $36.92 (not $6.92, which is value that Excel provides).
Is there a step I am missing to get the intented result?
2) See attached file zAverage is Off_2.jpg for Pivot Table Fields
Bookmarks