Forum Discussion
Solved
Autofill average values from consecutive 24 columns
Hi, I have a spreadsheet containing hourly data in a column (column G3 downwards in this case), so there are 24 daily values, after which the day changes and I have another 24 values and so on... Ho...
Thanks. You can use AVERAGEIF for this. See the attached demo.
Hi, this is an example dataset, could you get an understanding from this?
-Santeri
| Day | Hour | Value | Day | Daily average | |
| 1 | 1 | 141,33 | 1 | 583,95 | |
| 1 | 2 | 140,45 | 2 | 577,54 | |
| 1 | 3 | 146,55 | 3 | ||
| 1 | 4 | 155,76 | 4 | ||
| 1 | 5 | 443,27 | 5 | ||
| 1 | 6 | 575,05 | and so on… | ||
| 1 | 7 | 722,57 | |||
| 1 | 8 | 625,05 | |||
| 1 | 9 | 506,58 | |||
| 1 | 10 | 529,47 | |||
| 1 | 11 | 559,60 | |||
| 1 | 12 | 685,63 | |||
| 1 | 13 | 909,96 | |||
| 1 | 14 | 638,68 | |||
| 1 | 15 | 563,46 | |||
| 1 | 16 | 471,49 | |||
| 1 | 17 | 578,55 | |||
| 1 | 18 | 789,23 | |||
| 1 | 19 | 1 427,49 | |||
| 1 | 20 | 1 316,42 | |||
| 1 | 21 | 904,43 | |||
| 1 | 22 | 525,44 | |||
| 1 | 23 | 378,52 | |||
| 1 | 24 | 279,76 | |||
| 2 | 1 | 160,08 | |||
| 2 | 2 | 143,89 | |||
| 2 | 3 | 136,31 | |||
| 2 | 4 | 121,39 | |||
| 2 | 5 | 360,39 | |||
| 2 | 6 | 620,50 | |||
| 2 | 7 | 670,50 | |||
| 2 | 8 | 531,17 | |||
| 2 | 9 | 575,45 | |||
| 2 | 10 | 522,87 | |||
| 2 | 11 | 641,30 | |||
| 2 | 12 | 663,72 | |||
| 2 | 13 | 946,98 | |||
| 2 | 14 | 751,92 | |||
| 2 | 15 | 571,42 | |||
| 2 | 16 | 472,70 | |||
| 2 | 17 | 523,31 | |||
| 2 | 18 | 811,66 | |||
| 2 | 19 | 1 492,68 | |||
| 2 | 20 | 1 209,72 | |||
| 2 | 21 | 732,71 | |||
| 2 | 22 | 559,22 | |||
| 2 | 23 | 382,82 | |||
| 2 | 24 | 258,35 | |||
| 3 | 1 | 138,62 | |||
| 3 | 2 | 123,22 | |||
| 3 | 3 | 130,15 | |||
| 3 | 4 | 136,40 | |||
| 3 | 5 | 396,14 | |||
| 3 | 6 | 633,30 | |||
| 3 | 7 | 755,98 | |||
| 3 | 8 | 738,30 | |||
| 3 | 9 | 607,65 | |||
| 3 | 10 | 607,67 | |||
| 3 | 11 | 627,39 | |||
| 3 | 12 | 597,93 | |||
| 3 | 13 | 903,73 | |||
| 3 | 14 | 583,61 | |||
| 3 | 15 | 539,46 | |||
| 3 | 16 | 462,54 | |||
| 3 | 17 | 529,82 | |||
| 3 | 18 | 959,51 | |||
| 3 | 19 | 1 742,65 | |||
| 3 | 20 | 1 172,70 | |||
| 3 | 21 | 880,22 | |||
| 3 | 22 | 600,54 | |||
| 3 | 23 | 395,94 | |||
| 3 | 24 | 272,54 | |||
| 4 | 1 | 143,72 | |||
| 4 | 2 | 133,44 | |||
| 4 | 3 | 124,63 | |||
| 4 | 4 | 125,16 | |||
| 4 | 5 | 413,52 | |||
| 4 | 6 | 620,63 | |||
| 4 | 7 | 627,17 | |||
| 4 | 8 | 590,36 | |||
| 4 | 9 | 477,37 | |||
| 4 | 10 | 521,39 | |||
| 4 | 11 | 584,16 | |||
| 4 | 12 | 675,19 | |||
| 4 | 13 | 926,92 | |||
| 4 | 14 | 666,92 | |||
| 4 | 15 | 443,05 | |||
| 4 | 16 | 546,99 | |||
| 4 | 17 | 570,32 | |||
| 4 | 18 | 852,35 | |||
| 4 | 19 | 1 606,16 | |||
| 4 | 20 | 1 330,62 | |||
| 4 | 21 | 950,24 | |||
| 4 | 22 | 658,27 | |||
| 4 | 23 | 389,94 | |||
| 4 | 24 | 249,00 | |||
| 5 | 1 | 158,28 | |||
| 5 | 2 | 127,88 | |||
| 5 | 3 | 124,10 | |||
| 5 | 4 | 115,98 | |||
| 5 | 5 | 466,95 | |||
| 5 | 6 | 637,54 | |||
| 5 | 7 | 595,50 | |||
| 5 | 8 | 568,77 | |||
| 5 | 9 | 500,18 | |||
| 5 | 10 | 484,70 | |||
| 5 | 11 | 593,25 | |||
| 5 | 12 | 660,62 | |||
| 5 | 13 | 866,67 | |||
| 5 | 14 | 674,15 | |||
| 5 | 15 | 571,43 | |||
| 5 | 16 | 452,98 | |||
| 5 | 17 | 591,87 | |||
| 5 | 18 | 783,17 | |||
| 5 | 19 | 1 377,04 | |||
| 5 | 20 | 1 380,84 | |||
| 5 | 21 | 843,89 | |||
| 5 | 22 | 546,21 | |||
| 5 | 23 | 384,15 | |||
| 5 | 24 | 248,41 |
Thanks. You can use AVERAGEIF for this. See the attached demo.