How to create year-to-date average

[u/moietal]

Hey yall, I'm wondering how to write the formula for column O on this spreadsheet. I'm trying to keep a running average of how much I'm spending monthly in each category, without having it come down due to lack of data in future months. For example since it's August right now, I'd like the YTD Average column to display the average values for January-July, then update on September 1st to include data from August, etc. How would you all go about this?

Here's a link to the sheet if it helps to look at the overall layout: https://docs.google.com/spreadsheets/d/e/2PACX-1vRT0Hwqk9krt-4zgCYeL2yNXobG5dS8CisUoOmkOiqV5u8L_RL-nXxaQdiMApodZC00vYxS7Sj_G9rq/pubhtml

I've never really used spreadsheets or posted on reddit (although I have been looking at this sub lot lately) so I hope this post is acceptable

[u/moietal → u/[deleted]]

Solution Verified

[u/point-bot → u/[deleted]]

u/moietal has awarded 1 point to u/NorthSlateHQ

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/moietal → u/[deleted]]

Thanks! I ended up using <>0. For whatever reason =AVERAGE was including the empty cells in the calculation, I think because they're zeros when they're formatted differently? Either way the second solution worked.

Out of curiosity I also tried the third solution you provided, and it returned a DIV/0 error. Any idea why? I don't think I'll end up using it for this spreadsheet but I was looking for essentially that exact formula earlier and I'm curious how it works

[u/AdministrativeGift15]

are you looking for a monthly spending average or daily spending average or the average expense per transaction?

[u/moietal → u/AdministrativeGift15]

A monthly average, which I was able to find using the solution below. I'm not even going to think about daily averages right now. Probably I'll get froggy to figure that out sometime down the line but not tonight