Forum Discussion
running average
my current spreadsheet has about 5 months of data every minute. is there a way to write a formula to get an hourly average for every 60 minutes of data. it would definitely save a lot of time rather than going through the whole sheet and putting in a formula every 60 rows. Thanks
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.
6 Replies
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.
- HansVogelaar, Thank you for the input. That formula did work for the most part. i copied the formula into C61 and then autofilled the data all the way down, but it seems every 60 lines the time is off by 1 as it gets further down the list. C61 is at 2:00pm then the next average is at 2:59pm then 3:59pm then 4:58pm then 5:57pm and so on
Are you sure that you have 60 rows for every hour?
