Not sure this can even be done

bstraw

New Member
Joined
Sep 8, 2008
Messages
17
I have hourly data that I want to combine together for the entire day and average on one row. Can this be done? For example, the dataset below has measurements for 10-01-2016 every hour. I want to have excel take all of the temperature data for example (row 3) and average it for the entire day on a single row for that particular day. Any help would be appreciated, and If i did not explain properly, I apologize in advance. Thanks again for any help you may have.

10-01-2016 00:00 53.4 82.0 48.0 0.3 332 2.7 335 850.40 0.0 0.00
10-01-2016 01:00 52.1 84.9 47.7 0.7 3 3.5 350 850.17 0.0 0.00
10-01-2016 02:00 50.8 87.4 47.2 0.1 273 1.6 286 849.93 0.0 0.00
10-01-2016 03:00 49.4 91.5 47.1 0.2 278 1.6 264 849.58 0.0 0.00
10-01-2016 04:00 48.6 93.6 46.8 0.1 252 1.1 244 849.53 0.0 0.00
10-01-2016 05:00 48.6 93.8 46.9 0.1 248 1.3 218 849.40 0.0 0.00
10-01-2016 06:00 49.6 90.6 46.9 0.2 231 2.0 216 849.46 0.2 0.00
10-01-2016 07:00 50.8 87.7 47.3 0.2 72 2.2 198 849.58 69.3 0.00
10-01-2016 08:00 59.3 63.1 46.7 0.5 182 2.0 197 849.60 245.0 0.00
10-01-2016 09:00 65.4 48.6 45.5 1.5 181 4.3 169 849.58 428.5 0.00
10-01-2016 10:00 69.1 44.2 46.5 4.5 173 9.1 190 849.42 588.3 0.00
10-01-2016 11:00 72.2 37.5 44.8 5.8 157 10.7 165 848.87 699.9 0.00
10-01-2016 12:00 75.2 31.7 43.1 5.5 152 12.0 199 848.29 751.8 0.00
10-01-2016 13:00 77.0 29.6 42.8 5.7 167 10.9 141 847.45 748.0 0.00
10-01-2016 14:00 76.8 28.1 41.3 5.1 164 12.0 195 846.92 462.5 0.00
10-01-2016 15:00 75.9 29.2 41.6 3.7 158 7.9 162 846.64 176.7 0.00
10-01-2016 16:00 74.4 34.9 44.9 1.7 103 6.1 72 846.35 129.4 0.00
10-01-2016 17:00 77.4 27.1 40.9 1.3 115 5.0 114 846.00 191.5 0.00
10-01-2016 18:00 69.4 44.0 46.5 0.2 83 3.2 102 846.00 4.7 0.00
10-01-2016 19:00 63.0 58.8 48.4 0.4 230 2.1 242 846.34 0.0 0.00
10-01-2016 20:00 62.1 58.9 47.6 0.9 329 3.2 11 846.61 0.0 0.00
10-01-2016 21:00 61.6 56.7 46.0 1.0 11 5.9 26 847.09 0.0 0.00
10-01-2016 22:00 60.9 54.8 44.5 1.1 296 5.4 253 847.38 0.0 0.00
10-01-2016 23:00 60.4 59.6 46.3 1.2 354 5.4 0 847.51 0.0 0.00
10-02-2016 00:00 60.9 53.3 43.8 1.8 63 5.2 94 847.53 0.0 0.00
10-02-2016 01:00 57.5 63.2 45.1 1.1 53 5.1 84 847.75 0.0 0.00
10-02-2016 02:00 53.6 73.3 45.3 0.4 240 3.1 282 847.71 0.0 0.00
10-02-2016 03:00 51.5 81.7 46.1 0.6 274 4.7 256 847.58 0.0 0.00
10-02-2016 04:00 50.4 85.0 46.1 0.7 252 3.7 291 847.64 0.0 0.00
10-02-2016 05:00 48.7 89.0 45.6 0.3 242 2.5 220 847.91 0.0 0.00
10-02-2016 06:00 47.7 91.6 45.4 0.2 334 2.5 294 848.06 0.2 0.00
10-02-2016 07:00 48.9 88.8 45.7 0.3 223 3.1 234 848.34 68.5 0.00
10-02-2016 08:00 58.8 62.7 46.1 0.6 163 3.4 201 848.61 244.5 0.00
10-02-2016 09:00 66.0 44.5 43.8 1.8 180 4.5 185 848.58 430.4 0.00
10-02-2016 10:00 69.7 39.3 43.9 4.6 155 8.9 193 848.52 589.5 0.00
10-02-2016 11:00 73.5 33.8 43.2 5.5 157 10.5 190 848.16 698.3 0.00
10-02-2016 12:00 75.8 30.2 42.4 6.1 133 11.2 139 847.56 766.9 0.00
10-02-2016 13:00 77.9 27.6 41.8 6.7 136 14.8 131 846.80 750.1 0.00
10-02-2016 14:00 78.6 24.6 39.5 7.1 133 15.3 129 846.03 687.7 0.00
10-02-2016 15:00 78.0 24.7 39.1 6.8 158 12.3 88 845.35 492.1 0.00
10-02-2016 16:00 78.3 24.8 39.5 6.9 168 16.6 190 844.59 355.4 0.00
10-02-2016 17:00 77.2 25.4 39.1 4.6 142 10.8 150 844.10 129.2 0.00
10-02-2016 18:00 72.6 34.7 43.1 0.9 115 3.5 127 843.58 12.3 0.00
10-02-2016 19:00 65.7 50.5 46.9 0.2 355 1.8 64 843.12 0.0 0.00
10-02-2016 20:00 67.7 44.2 45.1 4.7 115 15.3 115 843.26 0.0 0.00
10-02-2016 21:00 65.8 49.9 46.6 6.2 127 18.8 153 843.43 0.0 0.00
10-02-2016 22:00 62.6 59.8 48.4 2.4 109 8.3 109 843.14 0.0 0.00
10-02-2016 23:00 59.8 67.1 48.8 1.3 40 4.2 85 842.50 0.0 0.00
10-03-2016 00:00 56.2 75.9 48.7 0.3 343 2.5 340 841.57 0.0 0.00
10-03-2016 01:00 54.5 79.2 48.2 0.7 347 4.3 331 840.68 0.0 0.00
10-03-2016 02:00 54.5 79.2 48.2 0.9 346 3.9 349 839.74 0.0 0.00
10-03-2016 03:00 53.5 82.0 48.1 1.2 7 4.2 1 838.79 0.0 0.00
10-03-2016 04:00 53.4 82.0 48.1 0.5 330 2.6 38 838.08 0.0 0.00
10-03-2016 05:00 52.0 83.8 47.3 0.9 341 3.5 34 837.36 0.0 0.00
10-03-2016 06:00 50.0 85.2 45.7 0.8 345 5.0 289 836.43 0.0 0.00
10-03-2016 07:00 50.8 77.9 44.2 1.1 352 4.3 263 835.83 49.5 0.00
10-03-2016 08:00 57.1 63.0 44.6 1.9 8 5.5 16 835.33 238.2 0.00
10-03-2016 09:00 62.5 56.3 46.7 1.8 62 7.1 73 834.18 430.3 0.00
10-03-2016 10:00 66.9 47.6 46.4 3.7 112 8.9 159 833.57 589.2 0.00
10-03-2016 11:00 70.1 41.3 45.5 6.7 152 14.1 128 832.70 718.4 0.00
10-03-2016 12:00 73.8 33.6 43.4 4.7 176 12.9 109 831.97 656.5 0.00
10-03-2016 13:00 75.2 29.2 41.0 1.8 249 11.1 224 831.22 392.8 0.00
10-03-2016 14:00 73.0 22.1 32.0 5.6 261 20.1 288 831.12 331.8 0.00
10-03-2016 15:00 68.5 27.6 33.7 3.6 333 16.9 0 831.18 296.8 0.00
10-03-2016 16:00 67.3 26.3 31.6 3.2 295 15.6 349 831.32 118.5 0.00
10-03-2016 17:00 66.8 21.9 26.7 5.1 267 16.5 273 831.73 102.0 0.00
10-03-2016 18:00 64.2 19.7 22.0 10.9 262 30.7 252 831.88 15.6 0.00
10-03-2016 19:00 61.2 18.4 17.9 4.3 294 14.5 310 833.34 0.0 0.00
10-03-2016 20:00 58.4 17.5 14.5 3.2 300 13.5 320 834.35 0.0 0.00
10-03-2016 21:00 57.1 18.9 15.1 4.2 300 19.0 281 834.75 0.0 0.00
10-03-2016 22:00 56.0 21.8 17.5 5.0 303 15.7 256 835.35 0.0 0.00
10-03-2016 23:00 54.1 27.7 21.5 3.2 313 15.5 300 836.33 0.0 0.00
10-04-2016 00:00 53.3 31.1 23.5 6.4 284 26.3 294 837.16 0.0 0.00
10-04-2016 01:00 52.6 30.8 22.7 5.0 277 19.4 249 836.96 0.0 0.00
10-04-2016 02:00 51.8 30.4 21.6 3.7 265 12.3 263 836.98 0.0 0.00
10-04-2016 03:00 50.7 30.3 20.6 2.3 312 10.7 293 836.59 0.0 0.00
10-04-2016 04:00 48.7 33.3 21.0 1.5 320 9.3 298 836.43 0.0 0.00
10-04-2016 05:00 45.5 41.0 23.2 2.4 59 7.5 59 836.44 0.0 0.00
10-04-2016 06:00 43.4 46.2 24.2 2.9 70 7.9 23 836.50 0.0 0.00
10-04-2016 07:00 43.0 50.8 26.0 2.0 222 6.5 179 837.16 54.7 0.00
10-04-2016 08:00 48.2 41.4 25.9 1.9 220 5.7 215 837.65 237.9 0.00
10-04-2016 09:00 52.6 35.3 25.9 3.6 139 9.0 102 837.74 431.9 0.00
10-04-2016 10:00 56.0 29.5 24.6 4.6 129 10.6 158 837.85 592.4 0.00
10-04-2016 11:00 58.8 23.1 21.2 4.5 148 10.3 132 837.85 649.7 0.00
10-04-2016 12:00 60.8 20.5 20.0 2.5 54 8.7 10 837.87 633.5 0.00
10-04-2016 13:00 62.0 21.8 22.5 4.7 144 13.1 166 837.72 551.2 0.00
10-04-2016 14:00 60.5 22.5 22.0 2.2 222 11.1 164 837.81 186.8 0.00
10-04-2016 15:00 61.1 24.3 24.4 1.4 338 5.5 350 838.34 135.8 0.00
10-04-2016 16:00 61.2 20.7 20.6 6.9 281 21.5 293 838.94 348.6 0.00
10-04-2016 17:00 59.4 21.2 19.6 6.4 281 18.0 295 839.53 170.3 0.00
10-04-2016 18:00 56.3 24.2 20.2 3.6 287 15.7 290 840.08 6.0 0.00
10-04-2016 19:00 52.8 30.2 22.4 0.9 358 13.2 303 840.72 0.0 0.00
10-04-2016 20:00 50.4 34.4 23.3 1.4 6 6.1 307 841.24 0.0 0.00
10-04-2016 21:00 47.8 39.4 24.3 0.7 336 3.9 285 841.53 0.0 0.00
10-04-2016 22:00 45.4 45.8 25.7 0.5 119 3.2 268 841.87 0.0 0.00
10-04-2016 23:00 42.6 57.4 28.6 0.4 237 4.9 204 842.04 0.0 0.00
10-05-2016 00:00 40.8 63.4 29.4 1.4 324 7.4 202 842.32 0.0 0.00
10-05-2016 01:00 42.0 55.8 27.4 2.0 198 8.4 197 842.42 0.0 0.00
10-05-2016 02:00 39.2 63.4 27.9 1.7 220 6.9 147 842.84 0.0 0.00
10-05-2016 03:00 38.6 65.5 28.0 1.6 316 4.9 239 842.72 0.0 0.00
10-05-2016 04:00 36.5 71.3 28.1 0.4 302 3.5 290 842.68 0.0 0.00
10-05-2016 05:00 35.0 73.1 27.3 1.1 335 4.6 36 843.10 0.0 0.00
10-05-2016 06:00 34.6 72.0 26.5 1.4 347 5.6 12 843.64 0.0 0.00
10-05-2016 07:00 35.5 70.9 27.0 1.0 256 4.3 238 844.24 54.6 0.00
10-05-2016 08:00 43.3 51.3 26.5 1.5 355 6.6 24 844.57 237.4 0.00
10-05-2016 09:00 49.0 38.5 24.8 2.0 48 7.1 76 844.45 429.1 0.00
10-05-2016 10:00 53.7 33.1 25.3 1.7 54 6.3 80 844.17 588.3 0.00
10-05-2016 11:00 58.0 28.1 25.1 3.4 93 8.5 117 843.46 699.0 0.00
10-05-2016 12:00 60.8 23.4 23.2 6.6 122 16.8 108 842.73 758.6 0.00
10-05-2016 13:00 62.2 21.3 22.1 4.3 95 10.5 81 842.07 493.4 0.00
10-05-2016 14:00 63.0 19.8 21.1 2.7 109 7.5 301 841.68 361.6 0.00
10-05-2016 15:00 65.5 18.8 21.9 2.8 306 12.4 303 841.55 471.7 0.00
10-05-2016 16:00 65.5 18.6 21.7 2.5 315 10.3 6 841.58 279.1 0.00
10-05-2016 17:00 62.2 25.4 26.3 2.3 336 10.9 302 841.98 146.3 0.00
10-05-2016 18:00 58.0 30.1 26.7 1.4 334 6.1 14 842.65 8.6 0.00
10-05-2016 19:00 55.7 33.5 27.4 3.1 11 8.6 32 843.51 0.0 0.00
10-05-2016 20:00 53.4 37.5 28.1 2.1 10 9.8 9 844.07 0.0 0.00
10-05-2016 21:00 50.6 42.2 28.5 2.2 61 7.1 64 844.83 0.0 0.00
10-05-2016 22:00 50.1 41.6 27.7 0.6 311 5.3 0 845.46 0.0 0.00
10-05-2016 23:00 49.0 46.3 29.3 1.4 349 6.4 319 845.75 0.0 0.00
10-06-2016 00:00 49.5 45.5 29.3 2.9 28 11.0 24 845.94 0.0 0.00
10-06-2016 01:00 48.3 49.4 30.2 1.0 320 5.0 30 845.75 0.0 0.00
10-06-2016 02:00 48.5 51.8 31.6 3.9 356 18.0 353 845.63 0.0 0.00
10-06-2016 03:00 46.9 63.4 35.1 6.9 1 17.4 22 845.36 0.0 0.00
10-06-2016 04:00 44.9 77.3 38.3 3.6 0 13.2 25 846.03 0.0 0.00
10-06-2016 05:00 43.5 92.3 41.4 1.0 0 5.3 39 846.09 0.0 0.05
10-06-2016 06:00 43.5 95.4 42.3 0.6 255 2.8 28 846.23 0.0 0.00
10-06-2016 07:00 44.1 94.4 42.6 0.3 44 3.2 30 846.78 11.6 0.01

<colgroup><col span="12"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
We don't need that much data, just a small sample.

Assuming your data starts in column A to L, with the temps starting in C...
N​
O​
1​
10/1/2016​
62.64167​
2​
10/1/2016​
62.64167​
3​
10/2/2016​
64.46667​
4​
10/3/2016​
60.7125​
5​
10/4/2016​
52.70417​
6​
10/5/2016​
50.09167​
7​
10/6/2016​
46.15​
O1=AVERAGEIF($A$1:$A$128,$N1,$C$1:$L$128)
copied down
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,169
Members
410,775
Latest member
alal1030
Top