Averaging every Nth cell in a column

[nilshammarsten]

I've got solar irradiance data for every hour of the year. It's formatted in the same column, and I need to find the average solar irradiance of each corresponding hour on each day of every month. That means the average irradiance at 1 pm, 2 pm, 3 pm, and so forth in January, February, etc. Would anyone be able to help me with the right formula to use? Below is my excel sheet and a picture of it.

Year	Month	Day	Hour	Temperature	DNI	DNI Hourly Irradiance (Wh/m2)	Month	Hour	Average Irradiance
2020	1	1	0	-2.4	0	0	Jan	1
2020	1	1	1	-2.5	0	0	Jan	2
2020	1	1	2	-2.6	0	0	Jan	3
2020	1	1	3	-2.5	0	0	Jan	4
2020	1	1	4	-2.6	0	0	Jan	5
2020	1	1	5	-2.7	0	0	Jan	6
2020	1	1	6	-3.1	0	0	Jan	7
2020	1	1	7	-3.7	0	0	Jan	8
2020	1	1	8	-4.4	0	0	Jan	9
2020	1	1	9	-4.9	0	0	Jan	10
2020	1	1	10	-5.1	0	0	Jan	11
2020	1	1	11	-5.1	0	0	Jan	12
2020	1	1	12	-5.3	0	0	Jan	13
2020	1	1	13	-4.9	0	0	Jan	14
2020	1	1	14	-2.8	336	336	Jan	15
2020	1	1	15	-0.9	793	793	Jan	16
2020	1	1	16	0.3	710	710	Jan	17
2020	1	1	17	1.1	675	675	Jan	18
2020	1	1	18	1.6	891	891	Jan	19
2020	1	1	19	1.8	871	871	Jan	20
2020	1	1	20	1.5	805	805	Jan	21
2020	1	1	21	-0.5	668	668	Jan	22
2020	1	1	22	-2.3	190	190	Jan	23
2020	1	1	23	-1.9	0	0	Jan	24
2020	1	2	0	-1.3	0	0	Feb	1
2020	1	2	1	-0.7	0	0	Feb	2
2020	1	2	2	-0.2	0	0	Feb	3
2020	1	2	3	0.2	0	0	Feb	4
2020	1	2	4	0.4	0	0	Feb	5
2020	1	2	5	0.4	0	0	Feb	6
2020	1	2	6	0.4	0	0	Feb	7
2020	1	2	7	0.2	0	0	Feb	8
2020	1	2	8	0	0	0	Feb	9
2020	1	2	9	-0.1	0	0	Feb	10
2020	1	2	10	0.1	0	0	Feb	11
2020	1	2	11	0	0	0	Feb	12
2020	1	2	12	-0.2	0	0	Feb	13
2020	1	2	13	0.1	0	0	Feb	14
2020	1	2	14	1.4	83	83	Feb	15
2020	1	2	15	3.2	543	543	Feb	16
2020	1	2	16	4.4	595	595	Feb	17
2020	1	2	17	5	348	348	Feb	18
2020	1	2	18	5.3	580	580	Feb	19
2020	1	2	19	5.1	562	562	Feb	20
2020	1	2	20	4.5	76	76	Feb	21
2020	1	2	21	3.5	21	21	Feb	22
2020	1	2	22	2.6	0	0	Feb	23
2020	1	2	23	2.5	0	0	Feb	24
2020	1	3	0	2.5	0	0
2020	1	3	1	3.2	0	0
2020	1	3	2	3.5	0	0
2020	1	3	3	3.5	0	0
2020	1	3	4	3.4	0	0
2020	1	3	5	3.4	0	0
2020	1	3	6	3.5	0	0
2020	1	3	7	3.3	0	0
2020	1	3	8	3.2	0	0
2020	1	3	9	3	0	0
2020	1	3	10	2.8	0	0
2020	1	3	11	2.6	0	0
2020	1	3	12	2.4	0	0
2020	1	3	13	2.3	0	0
2020	1	3	14	2.7	20	20
2020	1	3	15	3.2	140	140
2020	1	3	16	4	57	57
2020	1	3	17	4.7	108	108
2020	1	3	18	5.1	61	61
2020	1	3	19	5.1	114	114
2020	1	3	20	4.5	0	0
2020	1	3	21	3.1	162	162
2020	1	3	22	2	0	0
2020	1	3	23	1.6	0	0
2020	1	4	0	1.3	0	0
2020	1	4	1	1.1	0	0
2020	1	4	2	0.9	0	0
2020	1	4	3	0.8	0	0
2020	1	4	4	0.5	0	0
2020	1	4	5	0.3	0	0
2020	1	4	6	0.1	0	0
2020	1	4	7	-0.2	0	0
2020	1	4	8	-0.6	0	0
2020	1	4	9	-0.5	0	0
2020	1	4	10	-0.6	0	0
2020	1	4	11	-0.8	0	0
2020	1	4	12	-0.8	0	0
2020	1	4	13	-0.4	0	0
2020	1	4	14	0.4	0	0
2020	1	4	15	0.9	0	0
2020	1	4	16	1.1	0	0
2020	1	4	17	1.1	0	0
2020	1	4	18	1.1	46	46
2020	1	4	19	1	1	1
2020	1	4	20	0.8	0	0
2020	1	4	21	0.1	0	0
2020	1	4	22	-0.7	0	0
2020	1	4	23	-1	0	0
2020	1	5	0	-1.4	0	0
2020	1	5	1	-1.8	0	0
2020	1	5	2	-2.1	0	0
2020	1	5	3	-2.3	0	0
2020	1	5	4	-2.6	0	0
2020	1	5	5	-2.9	0	0
2020	1	5	6	-3	0	0
2020	1	5	7	-3.1	0	0
2020	1	5	8	-3.1	0	0
2020	1	5	9	-3.1	0	0
2020	1	5	10	-3	0	0
2020	1	5	11	-2.9	0	0
2020	1	5	12	-2.6	0	0
2020	1	5	13	-1.9	0	0
2020	1	5	14	-0.8	63	63
2020	1	5	15	0	11	11
2020	1	5	16	0.5	0	0
2020	1	5	17	0.9	0	0
2020	1	5	18	0.6	0	0
2020	1	5	19	-0.2	0	0
2020	1	5	20	-0.7	31	31
2020	1	5	21	-0.8	0	0
2020	1	5	22	-0.6	0	0
2020	1	5	23	0.5	0	0
2020	1	6	0	1.3	0	0
2020	1	6	1	1.7	0	0
2020	1	6	2	1.5	0	0
2020	1	6	3	1.1	0	0
2020	1	6	4	1	0	0
2020	1	6	5	0.9	0	0

 

[Joe4]

Welcome to the Board!

I think you should be able to use an AVERAGEIFS formula, were you can average records meeting certain criteria.
So you can have both Month and Time as your criteria.

See here for details, and post back (with the formula you attempted) if you run into any difficulty.

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

 

[nilshammarsten]

Welcome to the Board!

I think you should be able to use an AVERAGEIFS formula, were you can average records meeting certain criteria.
So you can have both Month and Time as your criteria.

See here for details, and post back (with the formula you attempted) if you run into any difficulty.

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

Hello, thank you!

I tried using this one but it didn't work
=AVERAGEIF(V2:V745,MOD(COLUMN(V2:V745),24)=1)

 

[Joe4]

What is column V?
We cannot tell, as your original image has not column headers.

I think you at least two different columns in your formula:
- the ones you are using for your criteria
- the ones you are using for your sum

 

[nilshammarsten]

What is column V?
We cannot tell, as your original image has not column headers.

I think you at least two different columns in your formula:
- the ones you are using for your criteria
- the ones you are using for your sum

 

[nilshammarsten]

 

[Joe4]

Firstly, you want columns B and X formatted the same way so you can match them up (either use "Jan" for both, or "1" for both).

Then you can do it like this (simplified example to show you the method):

So the formula in cell Z2 is this:

=AVERAGEIFS(V$2:V$17,B$2:B$17,X2,D$2:D$17,Y2)

which you can copy all the way down to cell Z5.

 

[nilshammarsten]

Firstly, you want columns B and X formatted the same way so you can match them up (either use "Jan" for both, or "1" for both).

Then you can do it like this (simplified example to show you the method):

View attachment 119533

So the formula in cell Z2 is this:

=AVERAGEIFS(V$2:V$17,B$2:B$17,X2,D$2:D$17,Y2)

which you can copy all the way down to cell Z5.

Thank you, that worked perfectly!

 

[Joe4]

You are welcome!

 

[Peter_SSs]

It probably does make sense to have both your month columns formatted the same but if you did want to keep it as you had in post 1 it isn't much of a problem.
One other thing that I noticed though is that in column D your daily hours go from 0 to 23 whereas in column Y your daily hours go from 1 to 24 and I'm not sure if that is how you actually want it and, if so, how you want that handled.

In any case, here a a couple of things to consider.
Column Z I believe produces the same results as Joe's formula (including the #DIV/0! error in row 25 - & there would be other rows below) but without the need to change the abbreviated month name in column X.
Column AA treats Hour 24 in col Y as Hour 0 in col D but I'm unsure if that is what you would want.

24 11 22.xlsm
	A	B	C	D									M									V	W	X	Y	Z	AA
1	Year	Month	Day	Hour									DNI									DNI Hourly Irradiance (Wh/m2)		Month	Hour	Average Irradiance	Average Irradiance
2	2020	1	1	0									0									0		Jan	1	0	0
3	2020	1	1	1									0									0		Jan	2	0	0
4	2020	1	1	2									0									0		Jan	3	0	0
5	2020	1	1	3									0									0		Jan	4	0	0
6	2020	1	1	4									0									0		Jan	5	0	0
7	2020	1	1	5									0									0		Jan	6	0	0
8	2020	1	1	6									0									0		Jan	7	0	0
9	2020	1	1	7									0									0		Jan	8	0	0
10	2020	1	1	8									0									0		Jan	9	0	0
11	2020	1	1	9									0									0		Jan	10	0	0
12	2020	1	1	10									0									0		Jan	11	0	0
13	2020	1	1	11									0									0		Jan	12	0	0
14	2020	1	1	12									0									0		Jan	13	0	0
15	2020	1	1	13									0									0		Jan	14	100.4	100.4
16	2020	1	1	14									336									336		Jan	15	297.4	297.4
17	2020	1	1	15									793									793		Jan	16	272.4	272.4
18	2020	1	1	16									710									710		Jan	17	226.2	226.2
19	2020	1	1	17									675									675		Jan	18	315.6	315.6
20	2020	1	1	18									891									891		Jan	19	309.6	309.6
21	2020	1	1	19									871									871		Jan	20	182.4	182.4
22	2020	1	1	20									805									805		Jan	21	170.2	170.2
23	2020	1	1	21									668									668		Jan	22	38	38
24	2020	1	1	22									190									190		Jan	23	0	0
25	2020	1	1	23									0									0		Jan	24	#DIV/0!	0
Av per Hr

Cell Formulas
Range	Formula
Z2:Z25	Z2	=AVERAGEIFS(V$2:V$10000,B$2:B$10000,MONTH(1&X2),D$2:D$10000,Y2)
AA2:AA25	AA2	=AVERAGEIFS(V$2:V$10000,B$2:B$10000,MONTH(1&X2),D$2:D$10000,MOD(Y2,24))