| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need to sort and ave with intermittent time values
Howdy - I've got a large number of 10-20 mb files with a column of 1 sec data collected for most, but not all seconds in most, but not all hours. I'd like to make 1 min and 1 hr averages of the rest of the data collected in each time range at whichever sec and min occur first in (whichever) hour. Various combinations of sorting and pivot tables have come to naught due to the intermittency - I need big help, please! Thanks a heap. -- jra ------------------------------------------------------------------------ jra's Profile: http://www.excelforum.com/member.php...o&userid=30877 View this thread: http://www.excelforum.com/showthread...hreadid=505583 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need to sort and ave with intermittent time values
Good morning-
Will you please post some sample data, telling what is the current difficulty and what is your desired outcome? I think what you describe can be done easily enough, but often the solution depends on the actual layout of the data, how the data is presents itself, etc. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need to sort and ave with intermittent time values
Thanks for your reply. Here's what I've got. I'd like an automated way to get the average value each minute and each hour where I don't have regularly spaced 1 sec data. I've copied just 1 physical variable here, but have ~20 for each sec for a total of 100 hours in a month. Thanks for any thoughts. SysDate SysTime (GMT) T_degC 5/10/02 17:26:26 -21.57959 5/10/02 17:26:27 33.42412 5/10/02 17:26:28 33.45773 5/10/02 17:28:18 34.50568 5/10/02 17:28:19 34.50262 5/10/02 17:28:20 34.49957 5/10/02 17:28:21 34.47818 5/10/02 17:28:22 34.45679 5/10/02 17:28:23 34.4629 5/10/02 17:28:24 34.48734 5/10/02 17:28:25 34.46596 5/10/02 17:28:27 34.39263 5/10/02 17:28:28 34.39568 5/10/02 17:28:29 34.40179 5/10/02 17:28:30 34.39568 5/10/02 17:28:31 34.4354 5/10/02 17:29:09 34.64622 5/10/02 17:29:10 34.61261 5/10/02 17:29:11 34.60039 5/10/02 17:29:12 34.57594 5/10/02 17:29:13 34.57594 5/10/02 17:29:14 34.579 5/10/02 17:29:15 34.58511 5/10/02 17:29:16 34.58511 5/10/02 17:29:17 34.57289 5/10/02 17:29:20 34.62788 5/10/02 17:29:21 34.64316 5/10/02 17:29:22 34.64011 5/10/02 17:29:23 34.64927 5/10/02 17:29:24 34.66455 5/10/02 17:29:25 34.67677 5/10/02 17:29:26 34.67371 5/10/02 17:29:27 34.65538 5/10/02 17:29:28 34.634 5/10/02 17:29:29 34.62177 5/10/02 17:29:30 34.58511 5/10/02 17:29:31 34.579 5/10/02 17:29:32 34.54234 5/10/02 17:29:33 34.48734 5/10/02 17:29:34 34.45985 5/10/02 17:29:35 34.40791 5/10/02 17:29:36 34.35291 5/10/02 17:29:37 34.28875 5/10/02 17:29:38 34.24903 5/10/02 17:29:39 34.21542 5/10/02 17:29:40 34.18182 5/10/02 17:29:41 34.09627 5/10/02 17:29:42 33.9649 5/10/02 17:29:43 33.87019 5/10/02 17:29:44 33.74797 5/10/02 17:29:45 33.64715 5/10/02 17:29:46 33.57382 5/10/02 17:29:47 33.52494 5/10/02 17:29:48 33.41801 5/10/02 17:29:49 33.35995 5/10/02 17:29:50 33.34468 5/10/02 17:29:51 33.29885 5/10/02 17:29:52 33.29274 5/10/02 17:29:53 33.33246 5/10/02 17:29:54 33.39967 5/10/02 17:29:55 33.44855 5/10/02 17:29:56 33.473 5/10/02 17:29:57 33.46689 5/10/02 17:29:58 33.47606 5/10/02 17:29:59 33.38745 5/10/02 17:30:00 33.33857 5/10/02 17:30:01 33.3233 5/10/02 17:30:02 33.32635 5/10/02 17:30:03 33.3233 5/10/02 17:30:04 33.32024 5/10/02 17:30:05 33.33246 5/10/02 17:30:06 33.39967 5/10/02 17:30:07 33.42717 5/10/02 17:30:15 33.6441 5/10/02 17:30:16 33.65326 5/10/02 17:30:17 33.65021 5/10/02 17:30:18 33.6777 5/10/02 17:30:19 33.71742 5/10/02 17:30:20 33.59521 5/10/02 17:30:21 33.5005 5/10/02 17:30:22 33.43634 5/10/02 17:30:23 33.44855 5/10/02 17:30:24 33.40578 5/10/02 17:30:25 33.29579 5/10/02 17:30:27 33.05749 5/10/02 17:30:28 32.96888 5/10/02 17:30:29 32.89861 5/10/02 17:30:30 32.81612 5/10/02 17:30:31 32.73363 5/10/02 17:30:33 32.56559 5/10/02 17:30:34 32.48921 5/10/02 17:30:35 32.40366 5/10/02 17:30:36 32.37311 5/10/02 17:30:37 32.33034 5/10/02 17:30:38 32.30895 5/10/02 17:30:39 32.32728 5/10/02 17:30:40 32.33645 5/10/02 17:30:41 32.34256 5/10/02 17:30:42 32.38228 5/10/02 17:30:43 32.39144 5/10/02 17:30:44 32.4006 5/10/02 17:30:45 32.29673 5/10/02 17:30:46 32.21729 5/10/02 17:30:47 32.18674 5/10/02 17:30:48 32.15313 5/10/02 17:30:49 32.13174 5/10/02 17:30:50 32.11341 5/10/02 17:30:51 32.09814 5/10/02 17:30:52 32.1348 5/10/02 17:30:53 32.09203 5/10/02 17:30:54 32.06147 5/10/02 17:30:55 32.05537 5/10/02 17:30:56 32.05537 5/10/02 17:30:57 32.04008 5/10/02 17:30:58 32.04314 5/10/02 17:30:59 32.07369 5/10/02 17:31:00 32.1684 5/10/02 17:31:01 32.24479 5/10/02 17:31:02 32.26923 5/10/02 17:31:03 32.26312 5/10/02 17:31:04 32.29062 5/10/02 17:31:05 32.30895 5/10/02 17:31:06 32.33339 5/10/02 17:31:07 32.36395 5/10/02 17:31:08 32.4281 5/10/02 17:31:09 32.44643 5/10/02 17:31:10 32.46171 5/10/02 17:31:11 32.4556 5/10/02 17:31:12 32.4831 5/10/02 17:31:13 32.54115 5/10/02 17:31:14 32.56864 5/10/02 17:31:15 32.5442 5/10/02 17:31:16 32.51976 5/10/02 17:31:17 32.54726 5/10/02 17:31:18 32.62364 5/10/02 17:31:19 32.72752 5/10/02 17:31:20 32.80084 5/10/02 17:31:21 32.88639 5/10/02 17:31:22 32.92917 5/10/02 17:31:23 32.9811 5/10/02 17:31:24 32.99944 5/10/02 17:31:25 32.99332 5/10/02 17:31:26 32.96888 5/10/02 17:31:27 32.93833 5/10/02 17:31:28 32.88945 5/10/02 17:31:29 32.8314 5/10/02 17:31:30 32.77335 5/10/02 17:31:31 32.72752 5/10/02 17:31:32 32.64503 5/10/02 17:31:33 32.55948 5/10/02 17:31:34 32.52893 5/10/02 17:31:35 32.59615 5/10/02 17:31:36 32.61753 5/10/02 17:31:37 32.59003 5/10/02 17:31:38 32.5992 5/10/02 17:31:39 32.56254 5/10/02 17:31:40 32.52587 5/10/02 17:31:41 32.51671 5/10/02 17:31:42 32.50754 5/10/02 17:31:43 32.53198 5/10/02 17:31:44 32.56559 5/10/02 17:31:45 32.62975 5/10/02 17:31:46 32.65725 5/10/02 17:31:47 32.69086 5/10/02 17:31:48 32.73669 5/10/02 17:31:49 32.76723 5/10/02 17:31:50 32.82224 5/10/02 17:31:51 32.86806 5/10/02 17:31:52 32.91084 5/10/02 17:31:53 32.95055 5/10/02 17:31:54 32.99638 5/10/02 17:31:55 33.03304 5/10/02 17:31:56 33.05749 5/10/02 17:31:57 33.0636 5/10/02 17:31:58 33.0361 5/10/02 17:31:59 33.01166 5/10/02 17:32:00 32.99944 5/10/02 17:32:01 33.05138 5/10/02 17:32:02 33.05443 5/10/02 17:32:03 33.03304 5/10/02 17:32:04 33.02082 5/10/02 17:32:05 33.01166 5/10/02 17:32:06 32.9811 5/10/02 17:32:07 32.97499 5/10/02 17:32:08 32.97194 5/10/02 17:32:09 32.96278 5/10/02 17:32:10 32.96278 5/10/02 17:32:11 32.97194 5/10/02 17:32:12 32.99332 5/10/02 17:32:13 32.99332 5/10/02 17:32:14 33.02693 5/10/02 17:32:15 33.0361 5/10/02 17:32:16 33.06054 5/10/02 17:32:17 33.08804 5/10/02 17:32:18 33.10637 5/10/02 17:32:19 33.11553 5/10/02 17:32:20 33.07582 5/10/02 17:32:21 33.0361 5/10/02 17:32:22 33.01777 5/10/02 17:32:23 32.99027 5/10/02 17:32:24 32.95055 5/10/02 17:32:25 32.96583 5/10/02 17:32:26 32.96278 5/10/02 17:32:27 32.96888 5/10/02 17:32:28 32.96888 5/10/02 17:32:29 32.95667 5/10/02 17:32:30 32.95972 5/10/02 17:32:31 32.86806 5/10/02 17:32:32 32.83445 5/10/02 17:32:33 32.79168 5/10/02 17:32:34 32.71835 5/10/02 17:32:35 32.68475 5/10/02 17:32:36 32.6328 5/10/02 17:32:37 32.5992 5/10/02 17:32:38 32.57475 5/10/02 17:32:39 32.55948 5/10/02 17:32:40 32.54115 5/10/02 17:32:41 32.49532 5/10/02 17:32:42 32.47088 5/10/02 17:32:43 32.47699 5/10/02 17:32:44 32.53503 5/10/02 17:32:45 32.50754 5/10/02 17:32:46 32.4831 5/10/02 17:32:47 32.4831 5/10/02 17:32:48 32.47393 5/10/02 17:32:49 32.47699 5/10/02 17:32:50 32.44032 5/10/02 17:32:51 32.37922 5/10/02 17:32:52 32.30589 5/10/02 17:32:53 32.22646 5/10/02 17:32:54 32.18674 5/10/02 17:32:55 32.13786 5/10/02 17:32:56 32.10119 5/10/02 17:32:57 32.08591 5/10/02 17:32:58 32.07064 5/10/02 17:32:59 32.06147 5/10/02 17:33:00 32.05842 5/10/02 17:33:01 32.05537 5/10/02 17:33:02 32.07675 5/10/02 17:33:03 32.10425 5/10/02 17:33:04 32.08286 5/10/02 17:33:05 32.04314 5/10/02 17:33:06 32.07369 5/10/02 17:33:07 32.01565 5/10/02 17:33:08 31.9301 5/10/02 17:33:09 31.86288 5/10/02 17:33:10 31.80788 5/10/02 17:33:11 31.75289 5/10/02 17:33:12 31.70095 5/10/02 17:33:13 31.69179 5/10/02 17:33:14 31.69179 5/10/02 17:33:15 31.75289 5/10/02 17:33:16 31.75595 5/10/02 17:33:17 31.76206 5/10/02 17:33:18 31.79567 5/10/02 17:33:19 31.81094 5/10/02 17:33:20 31.8476 5/10/02 17:33:21 31.87511 5/10/02 17:33:22 31.90565 5/10/02 17:33:23 32.00648 5/10/02 17:33:24 32.08897 5/10/02 17:33:25 32.15313 5/10/02 17:33:26 32.18369 5/10/02 17:33:27 32.18063 5/10/02 17:33:28 32.19896 5/10/02 17:33:29 32.19285 5/10/02 17:33:30 32.1684 5/10/02 17:33:31 32.1623 5/10/02 17:33:32 32.17757 5/10/02 17:33:33 32.20812 5/10/02 17:33:34 32.24173 5/10/02 17:33:35 32.26923 5/10/02 17:33:36 32.31812 5/10/02 17:33:37 32.37617 5/10/02 17:33:38 32.37922 5/10/02 17:33:39 32.38838 5/10/02 17:33:40 32.4006 5/10/02 17:33:41 32.47393 5/10/02 17:33:42 32.50143 5/10/02 17:33:43 32.5442 5/10/02 17:33:44 32.54115 5/10/02 17:33:45 32.49226 5/10/02 17:33:46 32.41283 5/10/02 17:33:47 32.367 5/10/02 17:33:48 32.39144 5/10/02 17:33:49 32.39144 5/10/02 17:33:50 32.46782 5/10/02 17:33:51 32.4281 5/10/02 17:33:52 32.44032 5/10/02 17:33:53 32.4006 5/10/02 17:33:54 32.37922 5/10/02 17:33:55 32.38533 5/10/02 17:33:56 32.38228 5/10/02 17:33:57 32.46477 5/10/02 17:33:58 32.55032 5/10/02 17:33:59 32.61447 5/10/02 17:34:00 32.67252 5/10/02 17:34:01 32.76418 5/10/02 17:34:02 32.72446 -- jra ------------------------------------------------------------------------ jra's Profile: http://www.excelforum.com/member.php...o&userid=30877 View this thread: http://www.excelforum.com/showthread...hreadid=505583 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need to sort and ave with intermittent time values
I"m guessing you import this data from an outside source, and set up
this solution based on that assumption. You'll need to import the data (or arrange your data) so the date and time are considered in one column as an Excel serial date. I brought in your 299 examples with the date/time in column A1:A299 and the temp in B1:B299. In cells D1:F1 I have headers: Date, Hour, Minute and in D4 I have the header Average. Cells D2:F2 are input fields: enter the date in D2 in mm/dd/yyyy format; enter the hour as an integer between 0 and 23 in E2; enter the minute as an integer between 0 and 59 in F2. The formula in cell E4 to calculate the average for a given hour is =SUMPRODUCT(--(D2=INT($A$1:$A$299)),--(E2=HOUR($A$1:$A$299)),$B$1:$B$299)/SUMPRODUCT(--(D2=INT($A$1:$A$299)),--(E2=HOUR($A$1:$A$299))) The formula in cell F4 to calc the average for a given minute within that hour is =SUMPRODUCT(--(D2=INT($A$1:$A$299)),--(E2=HOUR($A$1:$A$299)),--(F2=MINUTE($A$1:$A$299)),$B$1:$B$299)/SUMPRODUCT(--(D2=INT($A$1:$A$299)),--(E2=HOUR($A$1:$A$299)),--(F2=MINUTE($A$1:$A$299))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need to sort and ave with intermittent time values
Sorry to have been tardy in thanking you for the reply and help but I've been away and working on other things. I've just got back to these data and will implement your solution now. Thanks very much again for the help !! -- jra ------------------------------------------------------------------------ jra's Profile: http://www.excelforum.com/member.php...o&userid=30877 View this thread: http://www.excelforum.com/showthread...hreadid=505583 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
