Forum Discussion

EthanBala's avatar
EthanBala
Copper Contributor
Feb 19, 2020

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 abo...
excel
PReagan's avatar
PReagan
Bronze Contributor
Feb 20, 2020

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.

 

_JGraham_'s avatar
_JGraham_
Copper Contributor
Dec 15, 2020

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.

Resources