VBA Macro to Average Non-Contiguous but Regular Interval Cells

[SianB]

Hi, I have a worksheet with 35,136 rows of data. I want to calculate the average of every 4th cell in the same column. Is there a way to create a macro to select every 4th cell in the average formula? If so, can I then substitute min and max for average? Thanks.

 

[Jon von der Heyden]

Hi

You should be able to us a native formula for that.

Given the column of values:

Excel 2010
	A
1	Value
2	829
3	811
4	351
5	971
6	912
7	635
8	105
9	114
10	708
11	578
12	62
13	549
14	934
15	405
16	90
17	724
18	236
19	873
20	409
Sheet1

We can use the MOD function to establish a pattern:

Excel 2010
	A	B
1	Value	Pattern
2	829	1
3	811	2
4	351	3
5	971	0
6	912	1
7	635	2
8	105	3
9	114	0
10	708	1
11	578	2
12	62	3
13	549	0
14	934	1
15	405	2
16	90	3
17	724	0
18	236	1
19	873	2
20	409	3
Sheet1

Cell Formulas
Range	Formula
B2	=MOD(ROW()-ROW($A$1),4)

And use this approcah to calc a min/max/average/sum etc. As follows:

Excel 2010
	A	B	C	D
1	Value		N	4
2	829
3	811		Min	114
4	351		Max	971
5	971		Average	589.5
6	912		Sum	2358
7	635
8	105
9	114
10	708
11	578
12	62
13	549
14	934
15	405
16	90
17	724
18	236
19	873
20	409
Sheet1

Cell Formulas
Range	Formula
D3	{=MIN(IF(MOD(ROW($A$2:$A$20)-ROW($A$1),4)=0,$A$2:$A$20))}
D4	{=MAX(IF(MOD(ROW($A$2:$A$20)-ROW($A$1),4)=0,$A$2:$A$20))}
D5	{=AVERAGE(IF(MOD(ROW($A$2:$A$20)-ROW($A$1),4)=0,$A$2:$A$20))}
D6	{=SUM(IF(MOD(ROW($A$2:$A$20)-ROW($A$1),4)=0,$A$2:$A$20))}
Press CTRL+SHIFT+ENTER to enter array formulas.