Microsoft® Excel® based database addin

 
+ Reply to Thread
Results 1 to 4 of 4
  1. 07-20-2009 09:37 PM #1
    Mel_P Guest

    Default Plot or extract every 10th or 20th pair of values from 33,000 poin

    I have 33,000 pairs of values from a logger.
    - Time, concentration
    How do I plot (or extract) every 20th value (or some other defined interval)?
    Any ideas please (macro, VBA NOY manual selection).

    I have tried past link row1, row10, row20 and dragging to extend the range
    but this gives me row1, row20 row30 row2 row21 row31 etc

    I want to end up with
    row 1 data
    row10 data
    row 20 data

    etc

    Thanks. mel
    Reply With Quote Reply With Quote  
  2. 07-20-2009 10:49 PM #2
    Bernard Liengme Guest

    Default Re: Plot or extract every 10th or 20th pair of values from 33,000 poin

    Let's assume your x-values start in A1
    The formula =INDIRECT("A"&ROW(A1)*10) in any cell (say E1) and copied down
    the column will return the 10th, 20th, 30th x-values. And
    =INDIRECT("B"&ROW(A1)*10) will return the y-values

    The formula =INDIRECT("A"&(ROW(A1)-1)*10+1) in E1 and copied down the column
    will return the 1st, 11th, 21st.... values

    Alternatively, suppose you first x-value is in C5 and the last in C10000
    Then =INDEX($C$5:$C$10000, ROW(A1)*10) in any cell and copied down the
    column with give you the 10th, 20th, ... x-values

    best wishes
    --
    Bernard V Liengme
    Microsoft Excel MVP
    http://people.stfx.ca/bliengme
    remove caps from email


    "Mel_P" <MelP@discussions.microsoft.com> wrote in message
    news02BD683-1E69-44C7-ACE3-18BF5E0B9201@microsoft.com...
    > I have 33,000 pairs of values from a logger.
    > - Time, concentration
    > How do I plot (or extract) every 20th value (or some other defined
    > interval)?
    > Any ideas please (macro, VBA NOY manual selection).
    >
    > I have tried past link row1, row10, row20 and dragging to extend the range
    > but this gives me row1, row20 row30 row2 row21 row31 etc
    >
    > I want to end up with
    > row 1 data
    > row10 data
    > row 20 data
    >
    > etc
    >
    > Thanks. mel


    Reply With Quote Reply With Quote  
  3. 07-20-2009 11:00 PM #3
    Mel_P Guest

    Default Re: Plot or extract every 10th or 20th pair of values from 33,000

    Thanks - will try tomorrow!

    "Bernard Liengme" wrote:

    > Let's assume your x-values start in A1
    > The formula =INDIRECT("A"&ROW(A1)*10) in any cell (say E1) and copied down
    > the column will return the 10th, 20th, 30th x-values. And
    > =INDIRECT("B"&ROW(A1)*10) will return the y-values
    >
    > The formula =INDIRECT("A"&(ROW(A1)-1)*10+1) in E1 and copied down the column
    > will return the 1st, 11th, 21st.... values
    >
    > Alternatively, suppose you first x-value is in C5 and the last in C10000
    > Then =INDEX($C$5:$C$10000, ROW(A1)*10) in any cell and copied down the
    > column with give you the 10th, 20th, ... x-values
    >
    > best wishes
    > --
    > Bernard V Liengme
    > Microsoft Excel MVP
    > http://people.stfx.ca/bliengme
    > remove caps from email
    >
    >
    > "Mel_P" <MelP@discussions.microsoft.com> wrote in message
    > news02BD683-1E69-44C7-ACE3-18BF5E0B9201@microsoft.com...
    > > I have 33,000 pairs of values from a logger.
    > > - Time, concentration
    > > How do I plot (or extract) every 20th value (or some other defined
    > > interval)?
    > > Any ideas please (macro, VBA NOY manual selection).
    > >
    > > I have tried past link row1, row10, row20 and dragging to extend the range
    > > but this gives me row1, row20 row30 row2 row21 row31 etc
    > >
    > > I want to end up with
    > > row 1 data
    > > row10 data
    > > row 20 data
    > >
    > > etc
    > >
    > > Thanks. mel
    >
    >
    >
    Reply With Quote Reply With Quote  
  4. 07-20-2009 11:30 PM #4
    Mel_P Guest

    Default Re: Plot or extract every 10th or 20th pair of values from 33,000

    Tried the "INDIRECT" function - works just as proposed. Thanks - haven't
    seen that function before - will try further tomorrow but I presume it is
    possible to use a cell value instead of the "10" to make it a "user variable?
    how would I reference the cell?

    "Bernard Liengme" wrote:

    > Let's assume your x-values start in A1
    > The formula =INDIRECT("A"&ROW(A1)*10) in any cell (say E1) and copied down
    > the column will return the 10th, 20th, 30th x-values. And
    > =INDIRECT("B"&ROW(A1)*10) will return the y-values
    >
    > The formula =INDIRECT("A"&(ROW(A1)-1)*10+1) in E1 and copied down the column
    > will return the 1st, 11th, 21st.... values
    >
    > Alternatively, suppose you first x-value is in C5 and the last in C10000
    > Then =INDEX($C$5:$C$10000, ROW(A1)*10) in any cell and copied down the
    > column with give you the 10th, 20th, ... x-values
    >
    > best wishes
    > --
    > Bernard V Liengme
    > Microsoft Excel MVP
    > http://people.stfx.ca/bliengme
    > remove caps from email
    >
    >
    > "Mel_P" <MelP@discussions.microsoft.com> wrote in message
    > news02BD683-1E69-44C7-ACE3-18BF5E0B9201@microsoft.com...
    > > I have 33,000 pairs of values from a logger.
    > > - Time, concentration
    > > How do I plot (or extract) every 20th value (or some other defined
    > > interval)?
    > > Any ideas please (macro, VBA NOY manual selection).
    > >
    > > I have tried past link row1, row10, row20 and dragging to extend the range
    > > but this gives me row1, row20 row30 row2 row21 row31 etc
    > >
    > > I want to end up with
    > > row 1 data
    > > row10 data
    > > row 20 data
    > >
    > > etc
    > >
    > > Thanks. mel
    >
    >
    >
    Reply With Quote Reply With Quote  
« Previous Thread | Next Thread »

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

Forum Rules