Aggregate results by week and month for charts

by Fernan   Last Updated January 18, 2019 09:03 AM

Is there a way to convert daily values into weekly averages for charting?

Sample source sheet

---------------------------------------------------
| Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days |
---------------------------------------------------
| row 1  |    25    |   30     |    5     | etc...| 
---------------------------------------------------
| row 2  |    2     |          |    4     | etc...|
---------------------------------------------------
| etc ... each row has a different cadence.

Except for, each row may not have values daily, but rather weekly or monthly, depending on the cadence. So I would like to use the query function to aggregate some rows as weeks, and some rows as the month.

Expected results for a weekly aggregate (if there is more than 1 value I just want the avg for the week, IE: if in a week there are values: 2,3,3, simply show: 2)

---------------------------------------------------
| Data 1 | Week 1 | Week 2 | Week 3 | etc...52 weeks |
---------------------------------------------------
| row 1  |    25  |   30   |    5   | etc...| 
---------------------------------------------------
| row 2  |    2   |   0    |    4   | etc...|
---------------------------------------------------
| etc ... 

for a monthly aggregate (if there is more than 1 value I just want the avg for the month, IE; if in a month there are values: 25,50,100, simply show: 58)

---------------------------------------------------
| Data 1 | Jan | Feb | Mar | etc...12 months |
---------------------------------------------------
| row 1  | 50  | 30  |  55 | etc...| 
---------------------------------------------------
| row 2  | 2   |  0  | 4   | etc...|
---------------------------------------------------
| etc ...

This way, I can create graphs showing the correct unit of measure (week, month, etc). How could I convert?



Answers 1


for week:

=ROUND(AVERAGE(ARRAYFORMULA(VALUE(QUERY(
 {ARRAYFORMULA(TRANSPOSE(WEEKNUM($A$1:$AE$1, 2))),
  TRANSPOSE($A2:$AE2)},
 "select Col2 where Col1 matches '"&COLUMN()&"'", 0)))), 0)

for month:

=ROUND(AVERAGE(ARRAYFORMULA(VALUE(QUERY(
 {ARRAYFORMULA(TRANSPOSE(MONTH($A$1:$AE$1))),
  TRANSPOSE($A2:$AE2)},
 "select Col2 where Col1 matches '"&COLUMN()&"'", 0)))), 0)

3

MARK MY ANSWER
MARK MY ANSWER
January 18, 2019 08:56 AM

Related Questions


Updated March 03, 2018 02:03 AM

Updated February 06, 2019 12:03 PM

Updated April 13, 2019 13:03 PM

Updated April 13, 2019 13:03 PM