Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Add same set of formulae at defined intervals to list of row data
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 |
#2
|
|||
|
|||
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 On Tue, 11 Jan 2005 23:05:07 -0800, PC-Nut wrote: 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 |
#3
|
|||
|
|||
Another approach, quite similar to what Gord suggested ..
Assume you have in Sheet1, data in cols A to C, in row1 down 1 Text1 Data1 2 Text2 Data2 3 Text3 Data3 etc In Sheet2 ------------- Put in any starting cell, say in A2: =OFFSET(Sheet1!$A$1,ROWS($A$1:A1)*25-25,COLUMNS($A$1:A1)-1) Copy A2 across as many cols as there is in Sheet1, i.e. to C2, then fill down until zeros appear, signalling exhaustion of data extracted from Sheet1 For the sample data, you'll get: 1 Text1 Data1 26 Text26 Data26 51 Text51 Data51 etc Adapt to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PC-Nut" wrote in message ... 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 |
#4
|
|||
|
|||
Good addition Max.
I made assumption that data was in column A only. Gord On Wed, 12 Jan 2005 16:56:46 +0800, "Max" wrote: Another approach, quite similar to what Gord suggested .. Assume you have in Sheet1, data in cols A to C, in row1 down 1 Text1 Data1 2 Text2 Data2 3 Text3 Data3 etc In Sheet2 ------------- Put in any starting cell, say in A2: =OFFSET(Sheet1!$A$1,ROWS($A$1:A1)*25-25,COLUMNS($A$1:A1)-1) Copy A2 across as many cols as there is in Sheet1, i.e. to C2, then fill down until zeros appear, signalling exhaustion of data extracted from Sheet1 For the sample data, you'll get: 1 Text1 Data1 26 Text26 Data26 51 Text51 Data51 etc Adapt to suit |
#5
|
|||
|
|||
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. "Max" wrote: Another approach, quite similar to what Gord suggested .. Assume you have in Sheet1, data in cols A to C, in row1 down 1 Text1 Data1 2 Text2 Data2 3 Text3 Data3 etc In Sheet2 ------------- Put in any starting cell, say in A2: =OFFSET(Sheet1!$A$1,ROWS($A$1:A1)*25-25,COLUMNS($A$1:A1)-1) Copy A2 across as many cols as there is in Sheet1, i.e. to C2, then fill down until zeros appear, signalling exhaustion of data extracted from Sheet1 For the sample data, you'll get: 1 Text1 Data1 26 Text26 Data26 51 Text51 Data51 etc Adapt to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PC-Nut" wrote in message ... 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 |
#6
|
|||
|
|||
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. "Gord Dibben" wrote: 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 On Tue, 11 Jan 2005 23:05:07 -0800, PC-Nut wrote: 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 |
#7
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PC-Nut" wrote in message ... 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. |
#8
|
|||
|
|||
Thanks, Gord !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Good addition Max. I made assumption that data was in column A only. Gord |
#9
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#10
|
|||
|
|||
Thank you! You have helped me complete my project.
"Max" wrote: 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#11
|
|||
|
|||
Glad to hear that !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "PC-Nut" wrote in message ... Thank you! You have helped me complete my project. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto scroll down data validation list | Excel Discussion (Misc queries) | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
How to import a path/filename when importing data into an XML list | Excel Worksheet Functions | |||
Grouping data within intervals | Excel Worksheet Functions |