Mar 22 2022 05:17 AM
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
Mar 22 2022 05:48 AM - edited Mar 22 2022 05:49 AM
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.
Mar 22 2022 06:32 AM
Mar 22 2022 06:41 AM
Are you sure that you have 60 rows for every hour?
Mar 22 2022 06:52 AM
Mar 22 2022 07:00 AM
SolutionSo 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.
Mar 22 2022 08:02 AM
Mar 22 2022 07:00 AM
SolutionSo 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.