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, 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?
- 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