Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this automation be achieved by macro?
Hi all! I have a question on how to automated this process. i have some data which looks like these: Time Day Y X Speed 6:12:21 Monday 3.137623456 101.6953814 57 6:12:27 Monday 3.136615051 101.6952216 71 6:12:32 Monday 3.135641022 101.6951024 75 6:12:37 Monday 3.134624022 101.6949792 75 6:12:42 Monday 3.133702706 101.6947838 75 6:12:47 Monday 3.132911451 101.6942716 71 6:12:53 Monday 3.132395216 101.6934099 61 . . . . . . . . . . and i need to do some filtering on it which is based on the followin criteria: DAY TIME SEGMEN Monday 7:00:00 3.137263<=Y<=3.138149 Tuesday 7:05:00 3.136371<=Y<=3.137263 Wednesd 7:10:00 3.135472<=Y<=3.136371 Thursday 7:15:00 3.134575<=Y<=3.135472 Friday 7:20:00 3.133716<=Y<=3.134575 Saturday . 3.13297<=Y<=3.133716 Sunday . Y<=3.13297AND101.693463<=X 21:00:00 101.692597<=X<=101.693463 i.e. I need to have the average speed and standard deviation of a grou of data that fall in: certain "*Day*" (monday....sunday) and within certain "*time*" (7:00:0 to 7:05:00, 7:05:00 to 7:10:00, up to 20:55:00 to 21:00:00) and satisf certain criteria "*segment* " (3.137263<=Y<=3.138149.and so on....) I know this can be done by using the "auto filtering", however it i too tedious as i have to repeatly select those criteria one by on which may results to 7(days) x 169 (duration) x 127 (segment criteria = 150241 trials!!!! I have tried pivot table too, but it cannot support the numbers of dat that i have (around 30-40k). When i try a small portion of my data usin pivot tables, instead of select "range of criteria" i.e. (say time fro 7:00:00 to 7:05:00) it can only select "a particular criteria" i.e (say, 7:01:30 or 7:02:01 and etc). Besides, there are 3 types of criteria in the "segment" criteria. 1 involve satisfying variable Y only, 2) involve satisfying variable only, 3) involve satisfying variable X and Y. Please, anyone, show me how to solve it by vb or macro or c# or an other better way...., thanks a lot -- changeabl ----------------------------------------------------------------------- changeable's Profile: http://www.excelforum.com/member.php...fo&userid=1571 View this thread: http://www.excelforum.com/showthread.php?threadid=27240 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this automation be achieved by macro?
I expect it could be done with VBA, but if you can't do it with a pivot table,
the macro will be very slow -- very possibly unacceptably slow. Can you put this data into Access? It can do averages and standard deviations, and is very good at "filtering". On Tue, 26 Oct 2004 07:14:35 -0500, changeable wrote: Hi all! I have a question on how to automated this process. i have some data which looks like these: Time Day Y X Speed 6:12:21 Monday 3.137623456 101.6953814 57 6:12:27 Monday 3.136615051 101.6952216 71 6:12:32 Monday 3.135641022 101.6951024 75 6:12:37 Monday 3.134624022 101.6949792 75 6:12:42 Monday 3.133702706 101.6947838 75 6:12:47 Monday 3.132911451 101.6942716 71 6:12:53 Monday 3.132395216 101.6934099 61 . . . . . . and i need to do some filtering on it which is based on the following criteria: DAY TIME SEGMENT Monday 7:00:00 3.137263<=Y<=3.138149 Tuesday 7:05:00 3.136371<=Y<=3.137263 Wednesd 7:10:00 3.135472<=Y<=3.136371 Thursday 7:15:00 3.134575<=Y<=3.135472 Friday 7:20:00 3.133716<=Y<=3.134575 Saturday . 3.13297<=Y<=3.133716 Sunday . Y<=3.13297AND101.693463<=X 21:00:00 101.692597<=X<=101.693463 i.e. I need to have the average speed and standard deviation of a group of data that fall in: certain "*Day*" (monday....sunday) and within certain "*time*" (7:00:00 to 7:05:00, 7:05:00 to 7:10:00, up to 20:55:00 to 21:00:00) and satisfy certain criteria "*segment* " (3.137263<=Y<=3.138149.and so on....) I know this can be done by using the "auto filtering", however it is too tedious as i have to repeatly select those criteria one by one which may results to 7(days) x 169 (duration) x 127 (segment criteria) = 150241 trials!!!! I have tried pivot table too, but it cannot support the numbers of data that i have (around 30-40k). When i try a small portion of my data using pivot tables, instead of select "range of criteria" i.e. (say time from 7:00:00 to 7:05:00) it can only select "a particular criteria" i.e. (say, 7:01:30 or 7:02:01 and etc). Besides, there are 3 types of criteria in the "segment" criteria. 1) involve satisfying variable Y only, 2) involve satisfying variable X only, 3) involve satisfying variable X and Y. Please, anyone, show me how to solve it by vb or macro or c# or any other better way...., thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
% Achieved | Excel Discussion (Misc queries) | |||
% achieved to a negative goal | Excel Worksheet Functions | |||
Clickable pie chart - anyone know how this might be achieved? | Charts and Charting in Excel | |||
automation macro | Excel Programming | |||
Macro 'Automation error' with ChemOffice Excel macro | Excel Programming |