SOLVED

Autofill average values from consecutive 24 columns

New Contributor

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

@SanteriMiettinen 

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?

 

@Hans Vogelaar 

 

Hi, this is an example dataset, could you get an understanding from this?

 

-Santeri

DayHourValue DayDaily average
11141,33 1583,95
12140,45 2577,54
13146,55 3 
14155,76 4 
15443,27 5 
16575,05and so on…  
17722,57   
18625,05   
19506,58   
110529,47   
111559,60   
112685,63   
113909,96   
114638,68   
115563,46   
116471,49   
117578,55   
118789,23   
1191 427,49   
1201 316,42   
121904,43   
122525,44   
123378,52   
124279,76   
21160,08   
22143,89   
23136,31   
24121,39   
25360,39   
26620,50   
27670,50   
28531,17   
29575,45   
210522,87   
211641,30   
212663,72   
213946,98   
214751,92   
215571,42   
216472,70   
217523,31   
218811,66   
2191 492,68   
2201 209,72   
221732,71   
222559,22   
223382,82   
224258,35   
31138,62   
32123,22   
33130,15   
34136,40   
35396,14   
36633,30   
37755,98   
38738,30   
39607,65   
310607,67   
311627,39   
312597,93   
313903,73   
314583,61   
315539,46   
316462,54   
317529,82   
318959,51   
3191 742,65   
3201 172,70   
321880,22   
322600,54   
323395,94   
324272,54   
41143,72   
42133,44   
43124,63   
44125,16   
45413,52   
46620,63   
47627,17   
48590,36   
49477,37   
410521,39   
411584,16   
412675,19   
413926,92   
414666,92   
415443,05   
416546,99   
417570,32   
418852,35   
4191 606,16   
4201 330,62   
421950,24   
422658,27   
423389,94   
424249,00   
51158,28   
52127,88   
53124,10   
54115,98   
55466,95   
56637,54   
57595,50   
58568,77   
59500,18   
510484,70   
511593,25   
512660,62   
513866,67   
514674,15   
515571,43   
516452,98   
517591,87   
518783,17   
5191 377,04   
5201 380,84   
521843,89   
522546,21   
523384,15   
524248,41   
best response confirmed by SanteriMiettinen (New Contributor)
Solution

@SanteriMiettinen 

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

@SanteriMiettinen 

Alternatives could be Power Query and Pivot Table.

average.JPG

@Hans Vogelaar @Quadruple_Pawn 

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

 

-Santeri