Hi,

I have a table with all my daily expenses (that I extracted from my credit card reports). I made categories and subcategories for these expenses

I created a Pivot Table to group these expenses by year, month and so on...

But what I'd like is a way to get an average of these expenses per year and per month. I mean, for example, average of my Grocery Shopping in 2021. 

I tried to get the Average with the Average field of the Pivot Table but it doesn't work as expected:

For example, in the row \"Amelia\" and the subrow \"Deporte y Gimnasio\", for 2021, the average is -35. Probably because I have 3 itmes \"Deporte y Gimnasio\" in 2021 that sums up to -105. But what I would like is to get the monthly average, in that case 105/8 = 13,125 ... (divided by 8 because August is so far the 8th month of the year)... Of course, for 2020 that average would be, for the same row Amelia and category \"Deporte y Gimnasio\", 6/12 = 0,5.

My date fields are correctly formatted as date, and days/months/years are correctly recognized...

I hope I made it clear. 

Thanks in advance

Nicolas.

Hi,

I have a table with all my daily expenses (that I extracted from my credit card reports). I made categories and subcategories for these expenses

I created a Pivot Table to group these expenses by year, month and so on...

But what I'd like is a way to get an average of these expenses per year and per month. I mean, for example, average of my Grocery Shopping in 2021. 

I tried to get the Average with the Average field of the Pivot Table but it doesn't work as expected:

For example, in the row \"Amelia\" and the subrow \"Deporte y Gimnasio\", for 2021, the average is -35. Probably because I have 3 itmes \"Deporte y Gimnasio\" in 2021 that sums up to -105. But what I would like is to get the monthly average, in that case 105/8 = 13,125 ... (divided by 8 because August is so far the 8th month of the year)... Of course, for 2020 that average would be, for the same row Amelia and category \"Deporte y Gimnasio\", 6/12 = 0,5.

My date fields are correctly formatted as date, and days/months/years are correctly recognized...

I hope I made it clear. 

Thanks in advance

Nicolas.

nicolasdaudin 

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. 

nicolasdaudin 

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.

mathetes 

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.

nicolasdaudin 

When I do the same thing with my Pivot Table that tracks my checking accounts AND my credit cards, by budget categories, I use the Pivot Table to do the monthly totals and just create a separate column off to the side (NOT part of the Pivot Table but adjacent to it) and calculate the averages there. Perhaps that will work adequately for you.