Forum Discussion
Solved
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 rathe...
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.
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.
- HansVogelaar, it took a little while but that formula worked perfectly. Thank you