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

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

Hi Mel,

To plot or extract every 20th value from your dataset, you can use the INDEX function in Microsoft Excel. Here are the steps:

1. Select a new column where you want to extract every 20th value.
2. In the first cell of the new column, enter the following formula:
   Formula:

   ---

   =INDEX(A:A,20*(ROW()-1)+1) 


   ---

3. Replace "A:A" with the column where your time values are located.
4. Drag the formula down to the end of your dataset.

This formula will extract every 20th value from your time column. You can then copy and paste the extracted values into a new sheet to plot them.

Alternatively, you can use the same formula to plot every 20th value directly in a chart. Here are the steps:

1. Select the time and concentration columns.
2. Go to the Insert tab and select a chart type that you prefer.
3. Right-click on the chart and select "Select Data".
4. Click on "Add" to add a new series.
5. In the "Series values" field, enter the following formula:
   Formula:

   ---

   =INDEX(B:B,20*(ROW()-1)+1) 


   ---

6. Replace "B:B" with the column where your concentration values are located.
7. Click "OK" to close the "Select Data" window.

This will plot every 20th value from your concentration column in the chart.

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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