2

In the editable spreadsheet below I have a column of dates (column A) alongside a column of data (column C).

I want to calculate the daily average of the data in column C and enter it in columns C and F

I would also like to do the same for weekly and day-of-week average calculations, but I'd be happy with daily averages to start.
Could someone provide some guidance on this?

Sample Sheet

1
  • You have too much data in your sheet. Please read how to and share the test sheet so as you can be easier helped. Also don't forget to show us expected results. Aug 24, 2020 at 14:08

1 Answer 1

1

You would need 3 distinct formula alongside some data readjustment

  • Formula to calculate the daily average

    =QUERY(A2:F,"select avg(D) where A is not null group by A label avg(D) ''")

daily average

  • Formula to calculate the day-of-week average

    =QUERY(A2:D151,"select B, avg(D) group by B label avg(D) ''")

In this formula we return both the day of the week and the average columns.

day-of-week average

  • Formula to calculate the weekly average

    =AVERAGEIFS(D2:D,A2:A,">="&L2,A2:A,"<="&M2)

For this formula it is best/easier to separate your dates into 2 columns.
You use the above formula in cell N2 and then you drag it down.

weekly average

Functions used:

3
  • Thank you so much! Is there an array formula I could use for the start/end dates (columns L and M) so that new dates appear as they are added? Btw, before I realized you replied with answers I went back and tried to edit the spreadsheet to better comply with the guidelines you posted, so the spreadsheet looks much different now.
    – Wil
    Aug 24, 2020 at 18:00
  • I suggest you accept the present answer and create a new one. Why? 1. In your edited sheet you have just changed the columns. So you can easily adjust the given formulas. 2. "I want to calculate the daily average of the data in column...I'd be happy with daily averages" All of your 3 questions have been successfully answered. 3. "Is there an array formula I could use for the start/end dates (columns L and M) so that new dates appear as they are added?" This weekly average question requires a completely different approach and I believe it deserves a new question of its own. Aug 24, 2020 at 22:10
  • Having reasoned all the above, I would suggest you split your future questions and avoid compiling them in just one question. It is very confusing to users and extremely difficult for other people to be helped as well. Aug 24, 2020 at 22:14

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.