Converting large data set into a smaller averaged data set

E

Excel Bombadil

New Member
Joined
May 24, 2011
Messages
1
Greetings!

I have been working on converting a large data set with data sampled every 5 seconds into a set of data which averages all of the data points over each minute and reports a column of data with one average data point corresponding to each minute.

Is there some way to have excel take the average of every set of 13 datapoints in a column and report each of these averaged values in a single column. The default from excel will take the average of the first 13 datapoints, i.e. AVERAGE(A1:A14) and then using autofill instead of reporting the average of the next 13 data points in the set i.e. I would like it to be AVERAGE(A15:A28) followed by AVERAGE(A29:A42) etc, but Excel defaults to AVERAGE(A2:A15) followed by AVERAGE(A3:A16) etc. instead.

I was hoping autofill might work for this in someway, but it doesn't seem to be doing so.

I am not totally ignorant of excel, but am far from an expert so any help is greatly appreciated and you will have to accept my apologies if this is a very simple problem.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Excel Bombadil said:
Greetings!

I have been working on converting a large data set with data sampled every 5 seconds into a set of data which averages all of the data points over each minute and reports a column of data with one average data point corresponding to each minute.

Is there some way to have excel take the average of every set of 13 datapoints in a column and report each of these averaged values in a single column. The default from excel will take the average of the first 13 datapoints, i.e. AVERAGE(A1:A14) and then using autofill instead of reporting the average of the next 13 data points in the set i.e. I would like it to be AVERAGE(A15:A28) followed by AVERAGE(A29:A42) etc, but Excel defaults to AVERAGE(A2:A15) followed by AVERAGE(A3:A16) etc. instead.

I was hoping autofill might work for this in someway, but it doesn't seem to be doing so.

I am not totally ignorant of excel, but am far from an expert so any help is greatly appreciated and you will have to accept my apologies if this is a very simple problem.
Click to expand...
Try this...

Let's assume you want the first average in cell D1 and then to continue down column D.

Enter this formula in D1 and copy down as needed:

=AVERAGE(OFFSET(A$1,ROWS(D$1:D1)*14-14,,14))

D1 will be the average of A1:A14
D2 will be the average of A15:A28
D3 will be the average of A29:A42
D4 will be the average of A43:A56
etc
etc
 
Upvote 0
T. Valko said:
Try this...

Let's assume you want the first average in cell D1 and then to continue down column D.

Enter this formula in D1 and copy down as needed:

=AVERAGE(OFFSET(A$1,ROWS(D$1:D1)*14-14,,14))

D1 will be the average of A1:A14
D2 will be the average of A15:A28
D3 will be the average of A29:A42
D4 will be the average of A43:A56
etc
etc
Click to expand...
Should that last argument be 13?
Rich (BB code): 
=AVERAGE(OFFSET(A$1,ROWS(D$1:D1)*14-14,,13))
 
Upvote 0
Actually, I think the formula should be:
Code: 
=AVERAGE(OFFSET(A$1,ROWS(C$1:C1)*14-14-(ROW(C1)-1),0,13))
Copy down as far as needed.
 
Upvote 0
JoeMo said:
Should that last argument be 13?
Rich (BB code): 
=AVERAGE(OFFSET(A$1,ROWS(D$1:D1)*14-14,,13))
Click to expand...
I'm not sure.

The OP said the interval was 13 rows but the ranges they described were 14 rows so I went with 14 rows.

take the average of the first 13 datapoints, i.e. AVERAGE(A1:A14) and then using autofill instead of reporting the average of the next 13 data points in the set i.e. I would like it to be AVERAGE(A15:A28) followed by AVERAGE(A29:A42) etc
Click to expand...
,

A1:A14 = 14 rows
A15:A28 = 14 rows
A29:A42 = 14 rows

If the interval was 13 rows then you'd also need to change:

...*14-14

To:

...*13-13
 
Upvote 0
JoeMo said:
Actually, I think the formula should be:
Code: 
=AVERAGE(OFFSET(A$1,ROWS(C$1:C1)*14-14-(ROW(C1)-1),0,13))
Copy down as far as needed.
Click to expand...
Eh, I don't know!

If the interval is 13 then use:

=AVERAGE(OFFSET(A$1,ROWS(D$1:D1)*13-13,,13))
 
Upvote 0
Greetings!

I have been working on converting a large data set with data sampled every 5 seconds into a set of data which averages all of the data points over each minute and reports a column of data with one average data point corresponding to each minute.

Is there some way to have excel take the average of every set of 13 datapoints in a column and report each of these averaged values in a single column. The default from excel will take the average of the first 13 datapoints, i.e. AVERAGE(A1:A14) and then using autofill instead of reporting the average of the next 13 data points in the set i.e. I would like it to be AVERAGE(A15:A28) followed by AVERAGE(A29:A42) etc, but Excel defaults to AVERAGE(A2:A15) followed by AVERAGE(A3:A16) etc. instead.

I was hoping autofill might work for this in someway, but it doesn't seem to be doing so.

I am not totally ignorant of excel, but am far from an expert so any help is greatly appreciated and you will have to accept my apologies if this is a very simple problem.
Click to expand...

Hi Bombadil

One other way to accomplish this (AS LONG AS your interval (e.g. 13 rows) is constant) is to compute your first average in the row where the 5second points reach 1minute. Then select that cell, along with the 13 (empty) cells above, and fill down while all 13 or 14 cells are highlighted. This will result in an average value every 14 rows, where each average value is composed of the 13 or 14 preceding 5second values.
THEN -- in order to get all the values in adjacent rows, highlight the entire column with the averages, use Find and Select --> Go to Special --> formulas. All of the average values should be selected, and you can then copy and paste them into a new 1minute column

This may be more steps than the other options -- but it is fairly quick
Regards
 
Upvote 0
If one field has the time data, maybe a pivot table or similar solution? Please post sample data if further advice is required.
 
Upvote 0
You must log in or register to reply here.

Similar threads

montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
206
Nick Loughton
N
O
  • Question
Excel/VBA to conditionally send emails per schedule
Replies
0
Views
699
OC2PS
O
S
  • Question
Ranking Data Based on Multiple Criteria
Replies
4
Views
2K
bebo021999
B
O
  • Question
Upper limit of geography data type conversion?
Replies
3
Views
1K
Bosquedeguate
Bosquedeguate

Forum statistics

Threads
1,215,061
Messages
6,122,921
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top