 Highlighted

# Averaging hourly data in excel

Good afternoon, my name is Ethan

I have a question regarding averaging in excel.

I am trying to average the first 40 minutes of data from each hour and ignore the last 20 minutes.

I have about 360 hours to average and so doing it as individual averages takes much too long.

Is there a formula that would take into account all of the data? And doing a few and then dragging hoping that excel catches on does not work.

I am currently just taking the first 40 cells and averaging then skipping 20 then averaging the next 40 and so on and so forth.

Is there a formula that would do this for me?

Basically I want =average(C8:C48) then =average(C68:C108) then =average(C128:C168) and so on and so forth all the way to C19628.

Any help would be awesome. Thank you.

Highlighted

# Re: Averaging hourly data in excel

Hello @EthanBala,

Representing the average of C8:C47, in cell E8 would be:

`=AVERAGE(OFFSET(\$C\$8,(ROW()-ROW(\$E\$8))*60,,40,)) `

Each time you copy the formula down one cell, it represents skipping 20 rows and averaging the next 40. For example, cell E9 would represent the average of C68:C107, cell E10 would represent the average of C128:C167, etc.