Autofill average values from consecutive 24 columns

[SanteriMiettinen]

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

[#2]

@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?

[#3]

@Hans Vogelaar 

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

[#4 · Accepted solution]

@SanteriMiettinen 

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

[#5]

@SanteriMiettinen 

Alternatives could be Power Query and Pivot Table.

[#6]

@Hans Vogelaar @OliverScheurich 

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

-Santeri