SOLVED

New Contributor

# 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... 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

5 Replies

# Re: Autofill average values from consecutive 24 columns

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?

# Re: Autofill average values from consecutive 24 columns

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
best response confirmed by SanteriMiettinen (New Contributor)
Solution

# Re: Autofill average values from consecutive 24 columns

Thanks. You can use AVERAGEIF for this. See the attached demo.

# Re: Autofill average values from consecutive 24 columns

Alternatives could be Power Query and Pivot Table.

# Re: Autofill average values from consecutive 24 columns

Thank you both for solutions to this issue. Both work just like intended. Have a great week!

-Santeri