[[email protected]]

I need to take the average of a large data set, for example for the hour(C) 7 of day(B) 2, hour 8 of day 2 and so on. As you can see some hours have more data points than others. I have to do this for both value rows D and E.

=AVERAGEIF($C$1:$C$33,7,D2:D33)
But then I have to manually change the range since it repeats (changes day at ROW34).
I Figured that by making a new column where the hour (C) exists regardless of whether or not it is in the data then I can skip on step:

Make column K1 to K24, then in any empty column:
=AVERAGEIF($C$1:$C$33,$K1,D$2:D$33)

Then by going down with the expansion cross I will get all the D averages for exactly one day, going sideways will give me the E averages as well, where no data exists I get 0#DVID! which is okay, Still I have to change the ranges and repeat once the day moves on, this works for the mean time as I only have to do one month for now, but eventually I need to do all 365 days.

If you could let me know how to include the B column so this is quicker I would appreciate it very much.

I have a vast range, I am copying two days out of 365:

A= Cell number

A B C D E
01 2 07 1.01 116.01
02 2 07 0.66 119.42
03 2 08 0.62 119.49
04 2 08 0.62 120.19
05 2 08 0.57 146.63
06 2 10 0.62 18.22
07 2 11 0.66 33.57
08 2 11 0.74 28.69
09 2 11 0.88 186.97
10 2 11 1.01 131.53
11 2 11 1.05 72.37
12 2 12 1.4 153.87
13 2 12 1.01 121.94
14 2 12 1.27 156.32
15 2 12 1.62 169.6
16 2 13 1.67 161.02
17 2 13 0.83 185.81
18 2 14 1.01 154.28
19 2 15 2.72 166.47
20 2 15 1.36 159.9
21 2 15 2.1 155.9
22 2 15 1.97 166.12
23 2 16 1.01 165.87
24 2 16 2.14 146.66
25 2 16 1.48 147.16
26 2 16 1.27 143.99
27 2 17 0.78 201.54
28 2 17 0.88 9.34
29 2 17 1.23 165.03
30 2 17 1.53 160.04
31 2 17 0.88 147.54
32 2 17 1.01 13.76
33 2 18 1.09 26.9
34 3 07 1.32 147.47
35 3 07 0.92 147.65
36 3 08 0.53 147.75
37 3 08 0.62 147.65
38 3 08 0.57 147.54
39 3 10 2.14 96.31
40 3 10 2.14 89.78
41 3 10 2.8 84.44
42 3 10 2.88 81.74
43 3 10 1.93 97.05
44 3 11 1.83 92.31
45 3 11 2.37 88.98
46 3 11 2.45 87.96
47 3 11 1.93 87.89
48 3 11 2.67 86.13
49 3 11 2.32 89.54
50 3 12 2.02 91.15
51 3 12 5.12 84.48
52 3 13 4.51 76.86
53 3 13 5.12 73.91
54 3 13 4.68 79.71
55 3 13 4.24 79.71
56 3 13 3.23 78.72
57 3 14 1.48 58.32
58 3 14 1.88 60.64
59 3 15 1.44 24.3
60 3 15 2.28 55.4
61 3 15 3.46 61.02
62 3 15 4.77 67.24
63 3 15 2.98 65.73
64 3 16 2.41 72.05
65 3 16 2.23 89.26
66 3 16 2.76 76.4
67 3 16 4.28 81.39
68 3 16 3.72 76.09
69 3 16 3.46 72.37
70 3 17 3.07 76.86
71 3 17 4.24 95.85
72 3 17 3.58 99.26
73 3 17 3.89 85.08
74 3 17 2.88 90.76