Reducing data points?

[amdx]

I have 4000 data points of an electrical device.
I'd like to use every 20th point to run math on.
How do I tell Excel to skip 19 data points then get
the 20th and skip another 19 and get the 20th etc.?
Thanks, Mike

 

[MartinW]

Hi Mike,

There is a lot of ways to do that depending on how your data is
setup. One way is to use a helper column.

Put 1 in the first cell and drag down to the 20th cell, hold ctrl and
let go the left button. Then grab the fill handle again and drag down
to the end of your data, hold ctrl and let go the left button again.
Now you will have a repeating series of 1 to 20 running down
the sheet. Use autofilter to select every 20 and copy the result
to a new sheet and then use the data as you wish.

HTH
Martin

 

[MartinW]

Hi Mike,

One of the other ways is assuming your data in column A.
Put this in your helper column and drag down as far as needed.

=INDEX(A:A,ROWS($1:1)*20)

HTH
Martin

 

[Guest]

I have 4000 data points of an electrical device.
I'd like to use every 20th point to run math on.
How do I tell Excel to skip 19 data points then get
the 20th and skip another 19 and get the 20th etc.?
Thanks, Mike

Mike

This sums every second number in a range
=SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20)) it is an array formula and must be
committed using Ctrl + Shift + Enter

Regards
Peter

 

[Guest]

Or you can muliply each data point by 2 with

=SUM(IF(MOD(ROW(A1:A4000),20)=0,A1:A4000*2)) as array formula

Peter

 

[amdx]

Hi Mike,

There is a lot of ways to do that depending on how your data is
setup. One way is to use a helper column.

Put 1 in the first cell and drag down to the 20th cell, hold ctrl and
let go the left button. Then grab the fill handle again and drag down
to the end of your data, hold ctrl and let go the left button again.
Now you will have a repeating series of 1 to 20 running down
the sheet. Use autofilter to select every 20 and copy the result
to a new sheet and then use the data as you wish.

HTH
Martin

Hi Martin,
I got it up to this point--

"Use autofilter to select every 20 and copy the result
to a new sheet and then use the data as you wish."

What is and where is autofilter?
As additional info, I have three columns of data, A,B, and C
and rows starting at 3 and ending at 2003.

Thanks, Mike

 

[Guest]

Hi
select a cell within the data and choose Data, Autofilter then follow
Martin's suggestions

Peter

:

 

[amdx]

Hi
select a cell within the data and choose Data, Autofilter then follow
Martin's suggestions

Peter

:

Thanks Peter,
I've got it now,
Here's how I saved the description.

I have 4000 data points of an electrical device.
I'd like to use every 20th point to run math on.
How do I tell Excel to skip 19 data points then get
the 20th and skip another 19 and get the 20th etc.?
Thanks, Mike

Put 1 in the first cell of the last column

(first column with no data in it)

Now left click and hold on the lower right corner of the cell

(this is called the fill handle) and drag down

to the number of cells you want to skip, hold ctrl and let go of the left
button.

(if you want every tenth cell drag to 10, every N cell drag to N,)

Then grab the fill handle again and drag down to the end of your data,

hold ctrl and let go of the left button again.
Now you will have a repeating series of 1 to N running down
the sheet.

Select a cell within the data series you just made, now go to top

and click Data, click Autofilter, Note the drop down arrows at top of data,

open an arrow and select the number of the data you want to save.

Now your data has been reduced, copy and paste it to a new file.

 

[MartinW]

Hi Mike,

Glad you got it working. Just FYI the index formula works
well too. Seeing as your data starts in row 3 you need
to tweak it a little like this

=INDEX(A:A,ROWS($1:1)*20+2)

place the formula in the third cell of your blank column
and copy down as far as needed.

HTH
Martin