Forum Discussion
Calculate Monthly Average on a Pivot Table
That's for Windows:
- creating PivotTable you have an option to add data to data model
- in data model you have an option to add measures
- for above sample measure could be
which we may use in PivotTable.
I attached the file with the sample, you may try to open it. I'm not on Mac, but as I know it doesn't support such functionality. Perhaps something changes recently.
So, instead of SUM(Values) / COUNT(Values) you need as average
SUM(Values) / COUNT(months in the period)
That's easy to do with data model
but afraid it's still not available for Excel on Mac.
- Hi Sergei thank you!
What do you mean by "with data model". You mean my raw data, and directly calculate there in the excel sheet instead of using the pivot table?That's for Windows:
- creating PivotTable you have an option to add data to data model
- in data model you have an option to add measures
- for above sample measure could be
which we may use in PivotTable.
I attached the file with the sample, you may try to open it. I'm not on Mac, but as I know it doesn't support such functionality. Perhaps something changes recently.
- thank you, I'm gonna try!
- So my solution (calculate average monthly off to the side) DOES work on the Mac, which is also the environment in which I work.
Not sure it's the same. For example, you have only one $100 expense in May. When average for year 2021 will be 100/8; average for 1H2021 is 100/6; average for 2Q2021 is 100/3 and for other quarters is 0/3. Finally average for May is 100/1 and for other months is zero.
- It all depends on how you're using the monthly averages for budget tracking and planning purposes. If, for example, I only have one expense for auto insurance--say $700 in May--but that covers the year's auto insurance, it still is legit to say that the average monthly expense is 700/12 and I might need to plan to accumulate cash at that rate to pay for next year's insurance.
As is often the case, not only does excel offer multiple ways to do the same thing; there are different meanings assigned to the same words: so what do we mean when we talk of "average monthly"? And those definitions of meaning affect the Excel method to resolve.
