1

I have a running total for a particular month for our ad spend (marketing). I update this number daily, but I do not enter it for each day, rather I just update it each day in one cell. For instance, on the 1st I could have spent $100 and on the second I update that to $120...etc. I am creating a dashboard for daily spend and I need to have a formula that will update this average as the days go by. For instance, if the daily spend it $200 on the 5th, the average per day would be $40, but if it is also $200 the next day then the average changes based on the day of the month. How on earth do I accomplish this if it is even possible. I am using Google Sheets.

4
  • Are you just looking for a way to divide the current value by the day number (so $200/5=$40) or is it something more complicated you're looking to do? How would Sheets know what day number it is?
    – Laurel
    Jan 9 at 17:32
  • Google sheets does know the date based on the TODAY function which puts the date in any given field, but I don't know how to calculate my average daily spend on the day. Right now I am calculating it based on the 31 days in January but it won't be accurate until the 31st and I want it to be accurate every day without me having to go in and change it every single day.
    – Nicole
    Jan 9 at 18:10
  • Nicole, you might consider adding a sheet for your daily entries and then showing a total (as well as average etc.) where you currently make the changes in one cell. Your approach is prone to possible issues and errors that will make it very difficult to troubleshoot, or possibly even notice. Hopefully, the answers are helpful.
    – Blindspots
    Jan 9 at 23:43
  • If any of the answers below answered your question, consider accepting one of them by clicking the checkmark button on the left of their post.
    – TheMaster
    Jan 10 at 1:26

2 Answers 2

1

If you want to be able to look at spreadsheets for past months, you can use something like this to find the day number, plugging in the last and first days of the month respectively:

MIN(DATE(2023,12,31), TODAY()) - DATE(2023,12,1) + 1

This is the difference in days between today (or the last day of the month) and the first day of the month plus one. This does not work for months in the future, though you could use MAX to ensure the day value for those months is never less than 0 + 1:

MAX(0,MIN(DATE(2024,12,31), TODAY()) - DATE(2024,12,1)) + 1

Your final formula would probably look something like this for January (where A2 is where you type in the total cost for the month so far):

= A2 / (MAX(0,MIN(DATE(2024,1,31), TODAY()) - DATE(2024,1,1)) + 1)
2
  • Oh thank goodness!!!! That worked. Thank you so much!!!!
    – Nicole
    Jan 9 at 19:39
  • If trying to calculate the number of days in a month you can use the EOMONTH function to get the last day of a month for any date d. It has a month offset argument + or -. So, =EOMONTH(d, ) returns the last day of d's month, and wrapping that in DAY would give you the day number. Similarly =EOMONTH(d, -7) would give you the last day in the month 7 months prior to d. If you are looking for total days between two dates, the DATEDIF function is designed for this.
    – Blindspots
    Jan 10 at 0:16
0
=AVERAGE(A2, DATEDIF("1/8/2024", TODAY(), "D"))

# OR 

=A2 / DATEDIF("1/8/2024", TODAY(), "D"))

Explanation

  • Uses DATEDIF with TODAY to return the number of days since the start_ date; 1/8/2024 in my example. The start-date can be hardcoded or a reference to a cell containing a date.
  • Because the calculation is two numbers, the AVERAGE function is overkill but it does make the formula's purpose immediately obvious in the future.

DATEDIF
Calculates the number of days, months, or years between two dates.

=DATEDIF(start_date, end_date, unit)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.