Getting an output of multiple columns with averages of selected number of rows of column B

[venkatkaushik]

Hi all,
I have about 400000 rows and 6 columns, column B with numbers. The numbers in column B have to be averaged, 50 each. And about 900-1200 rows in column B have the same serial number in column C. I want to get average of 50 rows each and output them in different columns based on number in column C.
Say, column C has "1" for 1000 rows in column B, I need to get averages of B1-B50, B51-B100,.....,B951-B1000 in say column H. Then in column I, the averages of numbers have to start from B1001(where the column C has "2" till say B1150) and so on it goes till the the serial numbers in C end at 336/337 and columns till ME/MF.
I have got the averages of 1-50, 51-100,.... by the formula given by one of the users here, "=AVERAGE(INDEX(B:B,2+50*(ROWS($J$2:J2)-1)):INDEX(B:B,2+50*(ROWS($J$2:J2))))" and then dragging it but it doesn't work with creating columns as it goes to become "=AVERAGE(INDEX(C:C,2+50*(ROWS($J$2:K2)-1)):INDEX(C:C,2+50*(ROWS($J$2:K2))))" and along with this, the problem is that the number of rows in column B that have same serial number in column C are not integral multiples of 50.
Can anybody help? I can show you on skype if you help out.
It will be a great help if somebody can help.

Thank You.

[excelliot]

you can try this formula:

=AVERAGE(INDEX($C:$B,MATCH(COLUMN(A$1),$C:$C,0)+50*(ROWS(B$2:B2)-1)):INDEX($B:$B,MATCH(COLUMN(A$1),$C:$C,0)+50*(ROWS(A$2:A2))))

[venkatkaushik]

Hi,
The code works but the issue is that the averages come as A1:A50, A50:A100 and not A51:A100
Can you take a look and let me know how it can be done to continue from the next number and not the one that is ending in the previous row?

Thanks.

[excelliot]

I was a bit busy, let me check & come back to you in while..

[excelliot]

Try using this formula in H2 & drag

=AVERAGE(INDEX($F:$F,MATCH(COLUMN(A$1),$E:$E,0)+50*(ROW($H1)-1)):INDEX($F:$F,MATCH(COLUMN(A$1),$E:$E,0)+50*(ROW($H1))-1))

I2 is averaging cells F12386:F12436 instead of F12386:F12435 so you will get diff results..

12386-12436 is 50 & means it is averaging 51 cells, it should be F12386:F12435..