Converting large data set into a smaller averaged data set

[Excel Bombadil]

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.

 

[T. Valko]

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.

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

 

[JoeMo]

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

Should that last argument be 13?

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

 

[JoeMo]

Actually, I think the formula should be:

=AVERAGE(OFFSET(A$1,ROWS(C$1:C1)*14-14-(ROW(C1)-1),0,13))

Copy down as far as needed.

 

[T. Valko]

Should that last argument be 13?

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

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

,

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

 

[T. Valko]

Actually, I think the formula should be:

=AVERAGE(OFFSET(A$1,ROWS(C$1:C1)*14-14-(ROW(C1)-1),0,13))

Copy down as far as needed.

Eh, I don't know!

If the interval is 13 then use:

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

 

[rambike]

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.

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

 

[Fazza]

If one field has the time data, maybe a pivot table or similar solution? Please post sample data if further advice is required.