Averaging Discrete Sets of Data in a Large Dataset

[wrf_89]

Hello everybody,

I need to take averages of different groups of data in a large dataset.

What I need to do though is this... I need to average together the values from these rows... rows 1, 58, 115, 172, 229, and so on, basically in increments of 57. Doing this manually would take a long, long time.

After this is complete, I would then need to shift one row up, so that I would then take the average of rows 2, 59, 116, 173, 230, and so on.

After this is done, the same thing happens, and then again, and then again until there is no more to do.



How could I go by doing this?

I have highlighted the pattern in the attached excel file.

Thank you very much,

wrf_89

[p24leclerc]

If you don't mind using a control column, you can do the following:
In column B or any other available one, you put the following formula and you copy it down your data:

Please Login or Register  to view this content.

now in the next available column, you put the following formula and also copy it but only down 57 rows:

Please Login or Register  to view this content.

This gives you your average.

[Pete_UK]

Put this array* formula in C1:

Please Login or Register  to view this content.

and then copy it into cells C2:C57.

* An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>

Hope this helps.

Pete

[Teethless mama]

No helper column is required.


=AVERAGE(IF(MOD(ROW(A1:A2337),57)=1,A1:A2337))

Array formula: Press Ctrl+Shift+Enter, not just Enter

[wrf_89]

Hello,
Thank you! This works, but only for the 1st time. I also need to be able to carry this through the rest of the patterns.
After the first, I need to do rows 2, 59, 116, 173, 230, and so on. And then, rows, 3, 60, 117, 174, 231, and so on.
And then, rows, 4, 61, 118, 175, 232, and so on.

When I drag the formula down a few cells, I don't get the answers I need. Is there a way to modify the current formula?

Thank you,

wrf_89

[Pete_UK]

Who is it that you are replying to?

Pete

[wrf_89]

Wait wait wait sorry I was being an idiot.
I figured it out now... everyone's contributions worked haha.

Thank you very much everybody!
wrf_89

[mike65535]

Please mark thread "Solved"