Calculate time related values as minute average

[Doctor Gooose]

Trim-test.xlsxI have a time column (tt:mm:ss) and a value column (INT) where I want to average the values belonging to each minute. The ideal would be a new set of colums, one showing hh:mm and the other the average of values within the minute. For example minute 04 should show average of values between 04:00 and 04:59.
The table look like this:

Time Speed
06:00:13 99
06:00:38 118
06:00:48 102
06:01:18 125
06:01:23 126
06:01:33 105
06:01:38 110
06:01:43 118
06:01:48 84
06:01:49 76
06:01:53 74
06:02:03 114
06:02:07 123
06:02:16 106
06:02:17 96
06:02:18 98
06:02:22 97
06:02:23 101
06:02:24 91
06:02:27 79
06:02:28 79
06:02:32 110
06:02:37 110
06:02:47 108
06:03:11 116
06:03:12 131
06:03:42 76
06:03:52 103
06:03:57 108
06:03:58 118
06:04:02 97
06:04:11 122
06:04:17 101
06:04:22 101
06:04:27 96
06:04:28 95
06:04:32 111
06:04:37 115
06:04:38 112
06:04:42 108
06:04:47 109
06:04:52 114
06:04:57 107
06:05:12 114
06:05:22 95
06:05:23 111
06:05:47 107
06:05:48 117
06:05:57 99
06:05:58 99

and the result should be:
06:00:00 106
06:01:00 102
06:02:00 101
06:03:00 109
06:04:00 107
06:05:00 106

Greatful for assistance!

[Spencer101]

Could you post an example workbook. Far easier to help that way.

[tom1977]

Hi look into attachment (Pivot table + one helper column)

[Doctor Gooose]

Trim-test.xlsx

Please find the worksheet here.

[tom1977]

In attachment your sample with pivot is it ok or not?

[Doctor Gooose]

I would like the hour value together with the minute, then it will be perfect. Like 06:01 a.s.o.
The table is very long, actually it reads over several hours.

[tom1977]

Ok once again look into attachment

[Doctor Gooose]

How do you create the "helper"-column to tim:min which shows up as Time Minute in the pivot table?

[tom1977]

Unhide column C and check formula.

[Doctor Gooose]

Thanks for your assistance!