How to filter sets of "n" number of numbers from a list and make an output list corresponding to the averages of those sets?

[Rnkhch]

Hello,

I have a very long data set corresponding to every second (in time) of data collection from a machine, and I want to make a chart of the readings vs. time. However, there are too many readings, and I want to generate two defined names:

1. take every "n", in this case 5, readings and average them and make a new array of readings (in column F below)
2. take the "nth", in this case 1st, time of each set from above and make a new array of times (in column E below)

Here is an example XL2BB:

Blank power workbook1.xlsx
	A	B	C	D	E	F	G
1		Input			Output
2		Time	Value		Time	Value
3		0:00:01	78		0:00:01	60.4
4		0:00:02	76		0:00:06	51.6
5		0:00:03	53		0:00:11	58.4
6		0:00:04	65		0:00:16	37.6
7		0:00:05	30		0:00:21	69
8		0:00:06	35		etc.	etc.
9		0:00:07	69
10		0:00:08	52
11		0:00:09	31
12		0:00:10	71
13		0:00:11	55
14		0:00:12	58
15		0:00:13	75
16		0:00:14	45
17		0:00:15	59
18		0:00:16	29
19		0:00:17	47
20		0:00:18	57
21		0:00:19	26
22		0:00:20	29
23		0:00:21	54
24		0:00:22	84
25
26
27
28		etc.	etc.
29
Sheet12

Cell Formulas
Range	Formula
F3	F3	=AVERAGE(C3:C7)
F4	F4	=AVERAGE(C8:C12)
F5	F5	=AVERAGE(C13:C17)
F6	F6	=AVERAGE(C18:C22)
F7	F7	=AVERAGE(C23:C27)

Thanks for any input!

 

[bebo021999]

Try the AVERAGEIFS.

 

[Rnkhch]

I was thinking of AVERAGEIFS, but I couldn't figure out how to set criteria for it to act on every 5 cells...

Perhaps a complex FILTER can do it

 

[bebo021999]

E4=E3+5/84400

F3:=AVERAGEIFS($C$3:$C$24,$B$3:$B$24,">=" &E3,$B$3:$B$24,"<" &E4)

 

[Rnkhch]

Oh I see, thanks.