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.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
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.
