running average

[skillinger287]

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

[#2]

@skillinger287 

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.

[#3]

@Hans Vogelaar, 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

[#4]

@skillinger287 

Are you sure that you have 60 rows for every hour?

[#5]

@Hans Vogelaar, 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

[#6 · Accepted solution]

@skillinger287 

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.

[#7]

@Hans Vogelaar, it took a little while but that formula worked perfectly. Thank you