Refiining data in excel

R

Rob

I have large files that are to be analysed, which are taken from a
recorder at 60 Hz. Sixty readings per second is far more than
necessary for my purposes, and I wondered whether there is a quick way
to 'refine' this data, like make a new file from the existing one that
has 5 or six readings per second.... What's the quickest way to do
this?

Any help would be appreciated!

Rob
 
G

Gary''s Student

How about averaging the data in groups. Let's say we have 10 seconds of data
at 60 HZ in column A in cells A1 thru A600.

In B1 enter:
=AVERAGE(INDIRECT("A"&((ROW()-1)*60+1)&":A"&((ROW()-1)*60+1)+59))
and copy down thru B10

This gets us the average of 1 thru 60,
then the average of 61 thru 120,
etc.
 
R

Rob

How about averaging the data in groups. Let's say we have 10 seconds of data
at 60 HZ in column A in cells A1 thru A600.

In B1 enter:
=AVERAGE(INDIRECT("A"&((ROW()-1)*60+1)&":A"&((ROW()-1)*60+1)+59))
and copy down thru B10

This gets us the average of 1 thru 60,
then the average of 61 thru 120,
etc.
Click to expand...

Thanks for that, but I'm more looking to simply discard 9 out of 10
entries, and there are many columns in each file... Is there an any
way of doing this, or an application to do it?

Thanks

Rob
 
J

JLatham

Another possible way using a helper column, the MOD() function and Auto Filter:

Put a formula in a cell on the same row with the first reading such as
=MOD(ROW(),5)
and fill that down to the end of your data readings and then use Data |
Filter | Auto Filter to choose the value that will give you the number of
readings per second that you want. Copy what is displayed to another sheet.

Try various values for the '5' in the formula above to get the granularity
you want, some factor of 60 such as 2, 4, 5, 6, 10, 12, 15 (or a multiple of
one of those) should get you the sampling you want.
 
G

Gus

Hi Rob,

Another one along the same lines.

In your helper column put 1 in the first cell, grab the fill handle
and drag it down 10 rows hold down ctrl then release the left
mouse button then release the ctrl button. You've now got
1 to 10 in the first 10 cells, while they are still highlighted
grab the fill handle again and drag down to the end of your
data then repeat the ctrl button bit and you will have a repeating
series of 1 to 10 running down your column.
Then use autofilter to sort your info and copy and paste to
a new sheet.

HTH
Martin
 
R

Rob

Hi Rob,

Another one along the same lines.

In your helper column put 1 in the first cell, grab the fill handle
and drag it down 10 rows hold down ctrl then release the left
mouse button then release the ctrl button. You've now got
1 to 10 in the first 10 cells, while they are still highlighted
grab the fill handle again and drag down to the end of your
data then repeat the ctrl button bit and you will have a repeating
series of 1 to 10 running down your column.
Then use autofilter to sort your info and copy and paste to
a new sheet.

HTH
Martin
Click to expand...

Thanks for that, I'll use the excel filter. This brings me to my next
problem - a lot of the files are text files, and while they can be
opened in notepad, Excel refuses to open files that large. Some are
127MB, and I have no way of opening these and filtering them....

Any advice on this?

Thanks!
 
Top