I have a dataset with weight on specific dates:
I have already made a graph of this, but I find it a little bit confusing:
There are no system when the measurement have been taken place. Meaning in some months there are a lot of data, and other months there are just 2,3 or even 0. So I want to turn this into average per month. I have found a way to get the data into average per month:
Combinding ArrayFormula:
=ARRAYFORMULA(IF(A3:A71 <> ""; YEAR(A3:A71) & "/" & MONTH(A3:A71); ""))
With Query:
=QUERY(A3:C64;"select C, avg(B) where C != '' group by C order by C"; 0)
Which outputs the following:
2020/10 81,6
2020/11 78,9
2020/12 77,4
2020/7 87,2
2020/9 83,4
2021/1 78,0
2021/3 75,6
2021/4 77,9
2021/5 79,6
2021/7 82,6
2021/8 82,3
2021/9 81,0
But there are at least two problems:
- It is not sorted correctly
- I am not able to make a graph of it as this is not formatted by date.
Any ideas how I can solve this problem succesfully? Can it be solved by ArrayFormula?
Please take a look at the spreadsheet:
