Return value of every nth column

[delta_negative]

A worksheet has data from machine oil pressure readings in column range A:D. The need is for a formula or macro to perform the following;

Starting at row 1 copy the values of A:D to F:I every 5th row so that row 1 F:I will contain the values of row1 A:D, row 2 F:I will contain the values of row 6 A:D, row 3 F:I will contain the values row 11 A:D, etc. It would be great to have the same procedure followed every 15th row starting at row 1 K:N, then row 16, then row 31, etc. Sheet has 100s of Ks of rows so would it be possible to automatically perform this to the last row, even when new rows are being added?

Column G has formula MAX(B1:B5), Column H has formula MIN(C1:C5) with same formula in row6, row 11,etc.

Have been copying down formula but it is time consuming as well as a problem if I accidentally unclick with the mouse.

Any help greatly appreciated.

 

[Jonmo1]

Try this in F1 and filled down/right as far as needed.

=INDEX(A:A,(ROWS(A$1:A1)-1)*5+1)

Don't change the A$1:A1 reference, it has no relationship with your data.
The *5 represents the interval (every 5 rows)
The +1 represents the Row# to begin in.

 

[delta_negative]

Try this in F1 and filled down/right as far as needed.

=INDEX(A:A,(ROWS(A$1:A1)-1)*5+1)

Don't change the A$1:A1 reference, it has no relationship with your data.
The *5 represents the interval (every 5 rows)
The +1 represents the Row# to begin in.

That works absolutely superb. Thank you very much.

I edited the original post because I had forgotten to add that in row 1 column G has a formula MAX(B1:B5) and column H has the formula MIN(C1:C5) with each successive iteration incrementing MAX(B6:B10) and MIN(C6:C10),etc. Is it possible to add that functionality to the formula you posted?

 

[Jonmo1]

Try

=MAX(OFFSET(B1:B5),ROWS(A$1:A1)-1)*5,0)
and
=MIN(OFFSET(C1:C5),ROWS(A$1:A1)-1)*5,0)

 

[delta_negative]

Try

=MAX(OFFSET(B1:B5),ROWS(A$1:A1)-1)*5,0)
and
=MIN(OFFSET(C1:C5),ROWS(A$1:A1)-1)*5,0)

Received error message"You have entered too few arguments for the function" with B5 highlighted in the formula bar.

 

[Jonmo1]

I messed up my parens,
Also forgot to lock the row references..

that's what I get for not testing

=MAX(OFFSET(B$1:B$5,(ROWS(A$1:A1)-1)*5,0))
and
=MIN(OFFSET(C$1:C$5,(ROWS(A$1:A1)-1)*5,0))

 

[delta_negative]

I messed up my parens,
Also forgot to lock the row references..

that's what I get for not testing

=MAX(OFFSET(B$1:B$5,(ROWS(A$1:A1)-1)*5,0))
and
=MIN(OFFSET(C$1:C$5,(ROWS(A$1:A1)-1)*5,0))

Really appreciate your help. That's exactly what I needed.

Just realized there is 1 missing part, if you have the time. I am trying to SUM the totals of the same columns, rows 1-5, 6-10, etc as well as rows 1-15, 16-30, etc. I attempted to modify your code but no go.

 

[Jonmo1]

You might just change the word MAX to say..... SUM

And the *5 represents the interval (every n rows)
and adjust the initial C1:C5 range accordingly

 

[delta_negative]

You might just change the word MAX to say..... SUM

And the *5 represents the interval (every n rows)
and adjust the initial C1:C5 range accordingly

Another home run.

Greatly appreciated. Hope you have a great weekend.

 

[delta_negative]

You might just change the word MAX to say..... SUM

And the *5 represents the interval (every n rows)
and adjust the initial C1:C5 range accordingly

After running the suggested code for a few days everything is working great. However I now realize that because the size of the worksheet is dynamic, the processor usage is rapidly increasing to the point of consuming gigs of ram.

Have researched the problem and MS suggest different strategies to improve the situation. I think in this instance a huge step would be to limit the first calculation to execute every 60 seconds. I have added a row that contains the time in format h:mm:ss but have no idea how to reference that in conjunction with the code you have already supplied so that the code executes every time the time cell equals 59, on a continual iterative basis. Is such a thing possible?

Any help always very much appreciated .