Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
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. -- Gary's Student gsnu200705 "Rob" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
On 10 Feb, 15:17, Gary''s Student
wrote: 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. -- Gary's Student gsnu200705 "Rob" wrote: 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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
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. "Rob" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
Follow JLatham's advice.
-- Gary's Student gsnu200705 "Rob" wrote: On 10 Feb, 15:17, Gary''s Student wrote: 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. -- Gary's Student gsnu200705 "Rob" wrote: 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 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refiining data in excel
On 10 Feb, 16:47, "Gus" wrote:
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 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consulting Access from Excel | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |