Forum Discussion
running average
So it's not exactly every minute.
I'll assume that A2 and down contain the date and time, and B2 and down the data.
In C61:
=IF(MINUTE($A61)=0,AVERAGEIFS($B$2:$B61,$A$2:$A61,">"&$A61-TIME(1,0,0)),"")
Fill down.
Let's say the data are in B2:B225000.
If necessary, insert a column in column C.
Select C61.
Enter one of the following formulas.
If you want a rolling average over 60 minutes for every minute:
=AVERAGE(B2:B61)
If you want a rollning average only every 60 minutes:
=IF(MOD(ROW(B61),60)=1,AVERAGE(B2:B61),"")
Double-click the fill handle in the lower right corner of C61 to fill the formula down to the end of the data.
Are you sure that you have 60 rows for every hour?
- HansVogelaar, yes i believe they are 60 rows but its not consistent. The first hour goes from row 61 to row 121, the second hour goes from row 122 to row 181, the third hour goes from row 182 to row 242, the fourth hour goes from row 243 to row 303, and so on
So it's not exactly every minute.
I'll assume that A2 and down contain the date and time, and B2 and down the data.
In C61:
=IF(MINUTE($A61)=0,AVERAGEIFS($B$2:$B61,$A$2:$A61,">"&$A61-TIME(1,0,0)),"")
Fill down.