Average offset

**Jack11** · 10-18-2009, 10:28 PM

Hi,

I have a very large spreadsheet (27,000 rows). It is information from a data logger, which is logging at 1 minute intervals.

I would like to manage that information by converting it to an hourly average (i.e. I want to average 'blocks' of 60). The information I want starts at row 51 so e.g., I am after average(C51:C110), average (C111:C170) etc. etc.

To allow autofilling, I think I need an AVERAGE(OFFSET) function, but am having difficulty making it work. Can anyone help?

**mdbct** · 10-18-2009, 10:48 PM

Assuming your results formula is in row 1

Please Login or Register  to view this content.

change the blue -1 to the row number your formula resides

**Jack11** · 10-18-2009, 11:35 PM

Thanks for your help mdbct!

To reduce (my) confusion, I copied just one column into a new tab. The column goes from A3:A10998 and I adjusted the formula to

=AVERAGE(OFFSET($A$3,(ROW()-3)*60,0,60,1))

Works a treat!

Thanks again

**DonkeyOte** · 10-19-2009, 02:51 AM

Worth noting that OFFSET is Volatile, a non-volatile equivalent

Please Login or Register  to view this content.

Also, given the number of unique values you could create a column adjacent to A to push the values into the appropriate hour and then use a Pivot Table to generate the results.

**lukedef90** · 06-15-2017, 04:57 AM

Hi,

I have a large dataset of 86,400 rows of data for 12 hours in seconds and need to convert to 1 minute average data.

The data starts in K2 and need to average every 59 rows to compile into minutes. Hence, first K2:K60, second set of data K61:K120, third set of data K121:K180.

Could you advise what is the correct Average Offset function to use?

I have tried the a few functions which are providing incorrect results.

Thanks

**FDibbins** · 06-16-2017, 02:24 AM

Luke, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.