Averaging based on time stamp

**RemyV** · 10-12-2011, 12:29 PM

I have a spreadsheet where we log data points every 15 seconds for a three day period.
Column A of the imported log file has a date time stamp and column B has a corresponding Amp reading for that date/time.
I would like to be able to create a method of providing the average Amp reading for every hour of the entire log file. Here is some sample data, I have no clue where to begin (this particular sample is rather old and doesn't reflect nearly all the data but the principal is the same. Is there any way to automate what I want to do? (basically give me an hourly average of column B for all the data)

:confused:

UNITS: amps
5/23/2006 10:00 0.769999
5/23/2006 10:00 0.44
5/23/2006 10:01 0.44
5/23/2006 10:01 0.44
5/23/2006 10:02 0.44
5/23/2006 10:02 0.44
5/23/2006 10:03 0.44
5/23/2006 10:03 0.44
5/23/2006 10:04 0.44
5/23/2006 10:04 0.44
5/23/2006 10:05 0.44
5/23/2006 10:05 0.44
5/23/2006 10:06 0.44
5/23/2006 10:06 0.44
5/23/2006 10:07 0.44
5/23/2006 10:07 0.44
5/23/2006 10:08 0.44
5/23/2006 10:08 0.44
5/23/2006 10:09 0.44
5/23/2006 10:09 0.44
5/23/2006 10:10 0.44
5/23/2006 10:10 0.495
5/23/2006 10:11 0.44
5/23/2006 10:11 0.44
5/23/2006 10:12 0.44
5/23/2006 10:12 0.44
5/23/2006 10:13 0.44
5/23/2006 10:13 0.44
5/23/2006 10:14 0.44
5/23/2006 10:14 0.44
5/23/2006 10:15 0.44
5/23/2006 10:15 46.255
5/23/2006 10:16 99.2199
5/23/2006 10:16 103.235
5/23/2006 10:17 97.1299
5/23/2006 10:17 57.3099
5/23/2006 10:18 101.2
5/23/2006 10:18 104.775
5/23/2006 10:19 48.4
5/23/2006 10:19 99.4949
5/23/2006 10:20 102.3
5/23/2006 10:20 104.995
5/23/2006 10:21 59.8399
5/23/2006 10:21 100.375
5/23/2006 10:22 103.785
5/23/2006 10:22 47.85
5/23/2006 10:23 98.1199
5/23/2006 10:23 102.08
5/23/2006 10:24 105.105
5/23/2006 10:24 46.31
5/23/2006 10:25 100.375
5/23/2006 10:25 103.345
5/23/2006 10:26 104.665
5/23/2006 10:26 47.19
5/23/2006 10:27 96.8549
5/23/2006 10:27 98.8349
5/23/2006 10:28 102.08
5/23/2006 10:28 102.905
5/23/2006 10:29 58.5199
5/23/2006 10:29 83.7649
5/23/2006 10:30 100.925
5/23/2006 10:30 104.39
5/23/2006 10:31 46.255
5/23/2006 10:31 98.8899
5/23/2006 10:32 102.08
5/23/2006 10:32 104.555
5/23/2006 10:33 46.035
5/23/2006 10:33 98.7799
5/23/2006 10:34 101.09
5/23/2006 10:34 103.895
5/23/2006 10:35 46.64
5/23/2006 10:35 97.2949
5/23/2006 10:36 100.87
5/23/2006 10:36 101.97
5/23/2006 10:37 103.4
5/23/2006 10:37 103.785
5/23/2006 10:38 69.2449
5/23/2006 10:38 87.2299
5/23/2006 10:39 99.6049
5/23/2006 10:39 101.475
5/23/2006 10:40 103.73
5/23/2006 10:40 47.575
5/23/2006 10:41 98.0649
5/23/2006 10:41 100.98
5/23/2006 10:42 102.245
5/23/2006 10:42 104.61
5/23/2006 10:43 46.97
5/23/2006 10:43 99.2199
5/23/2006 10:44 103.015
5/23/2006 10:44 103.84
5/23/2006 10:45 69.4649
5/23/2006 10:45 98.7799
5/23/2006 10:46 100.375
5/23/2006 10:46 101.09
5/23/2006 10:47 102.355
5/23/2006 10:47 104.5
5/23/2006 10:48 104.83
5/23/2006 10:48 70.0699
5/23/2006 10:49 99.3849
5/23/2006 10:49 101.64
5/23/2006 10:50 104.39
5/23/2006 10:50 46.31
5/23/2006 10:51 98.8349
5/23/2006 10:51 101.585
5/23/2006 10:52 103.4
5/23/2006 10:52 104.39
5/23/2006 10:53 104.005
5/23/2006 10:53 104.28
5/23/2006 10:54 104.555
5/23/2006 10:54 56.1549
5/23/2006 10:55 117.81
5/23/2006 10:55 103.62
5/23/2006 10:56 104.28
5/23/2006 10:56 46.86
5/23/2006 10:57 108.735
5/23/2006 10:57 103.62
5/23/2006 10:58 62.3149
5/23/2006 10:58 96.5249
5/23/2006 10:59 100.32
5/23/2006 10:59 103.84
5/23/2006 11:00 46.365
5/23/2006 11:00 99.2199
5/23/2006 11:01 103.125
5/23/2006 11:01 53.68
5/23/2006 11:02 96.6349
5/23/2006 11:02 101.53
5/23/2006 11:03 74.9649
5/23/2006 11:03 51.4249
5/23/2006 11:04 100.595
5/23/2006 11:04 103.455
5/23/2006 11:05 47.135
5/23/2006 11:05 98.2849
5/23/2006 11:06 103.235
5/23/2006 11:06 47.3
5/23/2006 11:07 98.1199
5/23/2006 11:07 101.805
5/23/2006 11:08 104.335
5/23/2006 11:08 47.08
5/23/2006 11:09 98.2849
5/23/2006 11:09 101.86
5/23/2006 11:10 104.005
5/23/2006 11:10 47.245
5/23/2006 11:11 97.2949
5/23/2006 11:11 99.9899
5/23/2006 11:12 101.53
5/23/2006 11:12 103.51
5/23/2006 11:13 49.61
5/23/2006 11:13 97.7349
5/23/2006 11:14 102.08
5/23/2006 11:14 105.215
5/23/2006 11:15 70.9499
5/23/2006 11:15 99.4949
5/23/2006 11:16 101.86
5/23/2006 11:16 102.96
5/23/2006 11:17 102.685
5/23/2006 11:17 103.29
5/23/2006 11:18 104.555
5/23/2006 11:18 71.9399
5/23/2006 11:19 100.21
5/23/2006 11:19 102.19
5/23/2006 11:20 103.125
5/23/2006 11:20 104.39
5/23/2006 11:21 61.7649
5/23/2006 11:21 96.5799
5/23/2006 11:22 100.32
5/23/2006 11:22 103.73
5/23/2006 11:23 52.25
5/23/2006 11:23 97.4049
5/23/2006 11:24 100.43
5/23/2006 11:24 102.74
5/23/2006 11:25 104.72
5/23/2006 11:25 47.355
5/23/2006 11:26 99.7149
5/23/2006 11:26 102.52
5/23/2006 11:27 103.51
5/23/2006 11:27 52.6899
5/23/2006 11:28 97.7899
5/23/2006 11:28 101.805
5/23/2006 11:29 104.665
5/23/2006 11:29 46.31
5/23/2006 11:30 99.6599
5/23/2006 11:30 102.52

**NBVC** · 10-12-2011, 01:11 PM

Perhaps you can use a Pivot Table.

while in your data range, go to Insert|Pivot Table... ensure whole range is selected and choose destination.

In the right pane that becomes viewable, drag the Units to Row Area and the Amps to the summation area.

click on the Sum of Amps button that appears in the summation area and then select Value Field Setting and pick Average.

Then in the Pivot table right click in the first column and choose Group. Then select Hours and choose a start/ time and if desired an end time.

If you want the data broken by Days too, then also select Days.

Click Ok and review the outcome.

**RemyV** · 10-12-2011, 02:31 PM

Thank you! Not even close to how I was trying to do it but it seems to work. I was able to record a macro and automate it so I believe this will suit my needs perfectly

**NBVC** · 10-12-2011, 02:39 PM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save