Averaging hourly data in excel

[EthanBala]

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.

[#2]

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.

[#3]

@PReaganthank you for the informative response. I'm hoping you can provide some guidance regarding a similar situation for me.

I have a year's worth of data by hour and would like to average the values per day.

Current string is: =AVERAGE('Reference Sheet'!B4:B27)

This returns the 24hr avg of the data set but also requires my changing the 4 & 27 to incrementally higher values to average the next day's data. The data is in one sheet but want the averages to be on another.

I know using OFFSET would solve this but am unsure exactly how to set up the formula. I'm unsure how the ROW references in your previous post affect the formula and know what I have isn't correct.

This is what I'm currently thinking: =AVERAGE(OFFSET('Reference Sheet'!$B$4,$B$3,23,))

Thank you for any guidance you, or anyone else, can provide.