+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    PC-Nut
    Guest

    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. #2
    Gord Dibben
    Guest

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

    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 <PC-Nut@discussions.microsoft.com>
    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. #3
    Max
    Guest

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

    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 <at>yahoo<dot>com
    ----
    "PC-Nut" <PC-Nut@discussions.microsoft.com> wrote in message
    news:7E14A6F6-62B8-4635-91CE-7BBB6AC5DF95@microsoft.com...
    > 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. #4
    Gord Dibben
    Guest

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

    Good addition Max.

    I made assumption that data was in column A only.

    Gord

    On Wed, 12 Jan 2005 16:56:46 +0800, "Max" <demechanik@yahoo.com> 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. #5
    PC-Nut
    Guest

    Re: Add same set of formulae at defined intervals to list of row d

    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 <at>yahoo<dot>com
    > ----
    > "PC-Nut" <PC-Nut@discussions.microsoft.com> wrote in message
    > news:7E14A6F6-62B8-4635-91CE-7BBB6AC5DF95@microsoft.com...
    > > 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. #6
    PC-Nut
    Guest

    Re: Add same set of formulae at defined intervals to list of row d

    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 <PC-Nut@discussions.microsoft.com>
    > 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. #7
    Max
    Guest

    Re: Add same set of formulae at defined intervals to list of row d

    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 <at>yahoo<dot>com
    ----
    "PC-Nut" <PCNut@discussions.microsoft.com> wrote in message
    news:885AA017-08EE-4241-A1AD-55E8369B9E58@microsoft.com...
    > 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. #8
    Max
    Guest

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

    Thanks, Gord !
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "Gord Dibben" <gorddibbATshawDOTca> wrote in message
    news:d6pau01f9qc2l2v8ok32o8mqiguv10cj28@4ax.com...
    > Good addition Max.
    >
    > I made assumption that data was in column A only.
    >
    > Gord




  9. #9
    Max
    Guest

    Re: Add same set of formulae at defined intervals to list of row d

    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 <at>yahoo<dot>com
    ----



  10. #10
    PC-Nut
    Guest

    Re: Add same set of formulae at defined intervals to list of row d

    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 <at>yahoo<dot>com
    > ----
    >
    >
    >


  11. #11
    Max
    Guest

    Re: Add same set of formulae at defined intervals to list of row d

    Glad to hear that !
    Thanks for the feedback ..
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "PC-Nut" <PCNut@discussions.microsoft.com> wrote in message
    news:C08077AB-B4C7-41E7-9D8C-BBE1100B3A0A@microsoft.com...
    > Thank you! You have helped me complete my project.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1