3

I would like to figure out a formula that will look at the last 14 days of data in column D and calculate the average. But I would like it to dynamically update every day so it's always showing the last 14 days average based on the current date.

Does that make sense? Is this possible?

https://docs.google.com/spreadsheets/d/1C8paoYXirR5qus8axs-SciRqxeaBWMxsSmC0PGX969E/edit?usp=sharing

2
  • average including DIV/0 errors as zero? or excluding them?
    – user0
    Apr 25, 2019 at 20:26
  • 1
    Excluding them, sorry I should have mentioned that
    – William B
    Apr 25, 2019 at 20:30

1 Answer 1

1

=AVERAGE(QUERY({A2:D}, 
 "select Col4 
  where Col4 is not null 
  order by toDate(Col1) desc 
  limit 14", 0))

0

1
  • 1
    This worked perfectly, thank you so much!
    – William B
    Apr 25, 2019 at 20:49

Your Answer

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

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