Averaging hourly data in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1183268%22%20slang%3D%22en-US%22%3EAveraging%20hourly%20data%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183268%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%2C%20my%20name%20is%20Ethan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20question%20regarding%20averaging%20in%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20average%20the%20first%2040%20minutes%20of%20data%20from%20each%20hour%20and%20ignore%20the%20last%2020%20minutes.%3C%2FP%3E%3CP%3EI%20have%20about%20360%20hours%20to%20average%20and%20so%20doing%20it%20as%20individual%20averages%20takes%20much%20too%20long.%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20that%20would%20take%20into%20account%20all%20of%20the%20data%3F%20And%20doing%20a%20few%20and%20then%20dragging%20hoping%20that%20excel%20catches%20on%20does%20not%20work.%3C%2FP%3E%3CP%3EI%20am%20currently%20just%20taking%20the%20first%2040%20cells%20and%20averaging%20then%20skipping%2020%20then%20averaging%20the%20next%2040%20and%20so%20on%20and%20so%20forth.%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20that%20would%20do%20this%20for%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20I%20want%20%3Daverage(C8%3AC48)%20then%20%3Daverage(C68%3AC108)%20then%20%3Daverage(C128%3AC168)%20and%20so%20on%20and%20so%20forth%20all%20the%20way%20to%20C19628.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20awesome.%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1183268%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184653%22%20slang%3D%22en-US%22%3ERe%3A%20Averaging%20hourly%20data%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184653%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563578%22%20target%3D%22_blank%22%3E%40EthanBala%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERepresenting%20the%20average%20of%20C8%3AC47%2C%20in%20cell%20E8%20would%20be%3A%3C%2FP%3E%3CPRE%3E%3DAVERAGE(OFFSET(%24C%248%2C(ROW()-ROW(%24E%248))*60%2C%2C40%2C))%26nbsp%3B%3C%2FPRE%3E%3CP%3EEach%20time%20you%20copy%20the%20formula%20down%20one%20cell%2C%20it%20represents%20skipping%2020%20rows%20and%20averaging%20the%20next%2040.%20For%20example%2C%20cell%20E9%20would%20represent%20the%20average%20of%20C68%3AC107%2C%20cell%20E10%20would%20represent%20the%20average%20of%20C128%3AC167%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Visitor

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.

1 Reply
Highlighted

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.