Add same set of formulae at defined intervals to list of row data

[Guest]

Hello! I have a list 9000 rows of data and am trying to figure out a way to
analyze the data in a separate sheet tab by looking at the list of data at
every 25th interval. Is there a Macro or worksheet function that can automate
the task rather than me typing in the same formulaes 360 times. Thank you

 

[Gord Dibben]

Nut

Enter this formula in A1 of new worksheet.

=OFFSET(Sheet1!$A$1,25*ROW()-1,0)

Exchange Sheet1 for the name of your data sheet.


Gord Dibben Excel MVP

 

[Guest]

Max

Thank you! This is very helpful. What I am trying to do is analyze trends in
9000 rows of data (Sheet1). However I want to in a separate worksheet
(Sheet2) I want to write formulaes that look at the 9000 rows of data in
Sheet1 at intervals of 25 rows. So in Sheet2 I want to have (9000/25) 360 row
of formulaes that analyze the 9000 rows of data and show me trends in buckets
of 25 on Sheet2.

Example: Sheet1 might have a column A of Volume of business with 9000 rows.
In sheet2, I want a column A called Volume of business that looks at Sheet1
and aggregates/sums 25 rows, then the next 25 rows and the next 25 rows until
it reviews all 9000 rows. Hope this makes sense.

Thank you for your help again.

 

[Guest]

Gord,

Thank you! This is very helpful. What I am trying to do is analyze trends in
9000 rows of data (Sheet1). However I want to in a separate worksheet
(Sheet2) I want to write formulaes that look at the 9000 rows of data in
Sheet1 at intervals of 25 rows. So in Sheet2 I want to have (9000/25) 360 row
of formulaes that analyze the 9000 rows of data and show me trends in buckets
of 25 on Sheet2.

Example: Sheet1 might have a column A of Volume of business with 9000 rows.
In sheet2, I want a column A called Volume of business that looks at Sheet1
and aggregates/sums 25 rows, then the next 25 rows and the next 25 rows until
it reviews all 9000 rows. Hope this makes sense.

Thank you for your help again.

 

[Max]

Assuming numeric data in Sheet1,
in cols A to C, from row1 down

In Sheet2
-----------
Put in the starting cell, say A2:

=SUM(OFFSET(INDIRECT("Sheet1!A"&ROWS($A$1:A1)*25-25+1),,COLUMNS($A$1:A1)-1,2
5))

Copy across and down

This will return the equivalents of :

In A2: =SUM(Sheet1!A1:A25), copied across to C2
In A3: =SUM(Sheet1!A26:A50), copied across to C3
etc

Just change SUM(...) to AVERAGE(...)
to calc the averages

The "25" is the height param in the OFFSET, so you could adjust this to say:
50 if the interval was 50 instead

 

[Max]

Thanks, Gord !

 

[Max]

Sorry, scratch this phrase:

The "25" is the height param in the OFFSET, so you could adjust this to say:
50 if the interval was 50 instead

Just adjust all the "25"s in the formula to say: 50
if the interval was 50 instead

 

[Guest]

Thank you! You have helped me complete my project.

 

[Max]

Glad to hear that !
Thanks for the feedback ..