C
changeable
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
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