Sep 14 2022 02:20 AM
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... How could I easily get averages of every day with autofill (if that even works, maybe a function is needed) instead of manually fetching the data for every single day. I've already tried to fetch data for a few days manually, then using the pulldown autofill to fill in the rest, but that doesn't seem to work. Any advice is appreciated.
Thanks
-Santeri
Sep 14 2022 03:28 AM
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?
Sep 14 2022 04:46 AM
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 |
Sep 14 2022 04:53 AM
SolutionThanks. You can use AVERAGEIF for this. See the attached demo.
Sep 14 2022 04:56 AM
Sep 14 2022 05:05 AM
@Hans Vogelaar @OliverScheurich
Thank you both for solutions to this issue. Both work just like intended. Have a great week!
-Santeri
Sep 14 2022 04:53 AM
SolutionThanks. You can use AVERAGEIF for this. See the attached demo.