Trying to get an average amount until today's date

Question

I'm trying to create a budget calculator in google sheets. And I'm facing a problem. I want to calculate the average amount I've spent daily until today.

suppose, today is 16 July 2021 and I want to find the average amount I've spent daily until today (till 15 July from the 1st of the month). entries from 16 July onwards shall be 0 as those are future dates and I haven't spent on those dates.

I want to calculate the average spending only till 15 July and not greater than that.

how to do it? pls, help.

link to sample data

Answer 1

Use filter() to get the rows that are before today, and average the result, like this:

=average( filter(B2:B, A2:A < today()) )