take every 4 rows to average them and this average data put into 4 rows as a specific single result

[CRG]

Hi, like title says i need to take every 4 rows to average them and this average data put into 4 rows as a specific single result. I tried my formula in tab.1 but didnt work.
My data needs to looks like tab2. Btw table2 continues to 175 000 rows.Can someone help me what i doing wrong? Thanks for help/advice. Crg


table.1
what i tried with my formula

=AVERAGE(FILTER(A1:A4;MOD(COLUMN(A1:A4);4)))

, but unfortunately i get this :

A_col(Values)	B_col(values)
4	5
6	7,5
4	8,5
6	10,5
14	11
10	10
12	10
8	8

table.2
what i want:

A_col(Values)	B_col(Results)
4	5
6	5
4	5
6	5
14	11
10	11
12	11
8	11

 

[Peter_SSs]

Welcome to the MrExcel board!

Not sure how this will go with your very large data, but you can give it a try.

CRG.xlsm
	A	B
1	Data	Average
2	4	5
3	6	5
4	4	5
5	6	5
6	14	11
7	10	11
8	12	11
9	8	11
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=IF(MOD(ROWS(B$2:B2),4)=1,AVERAGE(A2:A5),B1)

 

[awoohaw]

I came up with this using FILTER.

Mr Excel Questions 72.xlsm
	A	B	C
1
2	4	5
3	6	5
4	4	5
5	6	5
6	14	11
7	10	11
8	12	11
9	8	11
10
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=AVERAGE(FILTER($A$2:$A$9,INT((ROW($A$2:$A$9)-2)/4)=INT((ROW(B2)-2)/4)))

 

[Peter_SSs]

I came up with this using FILTER.

Considering this ..

table2 continues to 175 000 rows.

.. I just did a bit of testing of slightly larger samples and as I increased the number of rows that filter approach gets exponentially slower compared to the formula I posted. For example with about 2,500 rows it was 0.93 seconds v 0.02 seconds (factor of 45+). I think with 175,000 rows it would get very slow.

Also, if subsequently any new rows get added at the top, the FILTER formula results will be incorrect (unless the numbers of inserted rows is a neat multiple of 4)

 

[CRG]

Hi guys, thanks for help. This one works well. but yea as Peter said, loading datas is very slow, cuz i have large table. I need to do that by 10 000 (rewriting formula 10 000/20 000/30 000... ). But it works in the end

I came up with this using FILTER.

 

[Peter_SSs]

Are you saying that the formula in post #2 does not work?
It should be much faster in that it only has to calculate an average every 4 rows and the other 3 rows just copies the value above. Also, when it does calculate an average it only uses 4 rows to do so, not the entire 175,000 rows.

 

[awoohaw]

Considering this ..

.. I just did a bit of testing of slightly larger samples and as I increased the number of rows that filter approach gets exponentially slower compared to the formula I posted. For example with about 2,500 rows it was 0.93 seconds v 0.02 seconds (factor of 45+). I think with 175,000 rows it would get very slow.

Also, if subsequently any new rows get added at the top, the FILTER formula results will be incorrect (unless the numbers of inserted rows is a neat multiple of 4)

Thanks Peter.
I know there is a rule of thumb (that I don't know!) regarding what kind of calculations are faster than others. I know that array formulas are in the mix but I don't recall if it is a VBA solution that is faster or not. I'm lucky I seldom have files that big. Your thoroughness is always helpful.