How to use function to calculate row or column number?

I have a spread sheet with a lot of data. I would like to “shrink data” so that I would re-count data to average of 5 columns. Then amount of data would shrink to the one fifth.

I tried kind of next method:

Data to average would be in row2 and row1 is running count 1,2,3,4…


Fill this down until R1Cx*5 is number of the last data column.

This syntax does not work but is there simple method do this kind of calculation?

It would be better if you put a sample with data, and expected result, to some share, and put a link here.

But why did you do the question a wiki??? I cannot understand what moves people to tick that. Now you will be unable to edit your own question… so use “Add comment” to clarify your question.

You can’t modify the address with operations, use INDIRECT() function just for that, it allows use a text like an address. =AVERAGE(INDIRECT("R2C["&(R1C1*5-4)&"]:R2C["&(R1C1*5)&"]"))

Please review INDIRECT() help

If the answer solves your question please tick the :heavy_check_mark:.

Or maybe you can use this solution

This indirect() was easy way to make this work. I simply make a C-row that got the cells b5, b10, b15… then the funktion is simply:

Thank you very much on help!