Progressive average formula that counts also blank cells

[vladimiratanasiu]

Hello!

I posted a message some time ago (Formula to calculate automatically progressive average), getting the proper solution in that case. Basically, it calculates and shows automatically the average of all costs at some point, each time a new value is inserted and excluding the annual totals. Presently, some changes have occured, needing to adjust the old formula to the new conditions. In the attached table, the column D includes cells to show monthly energy consumption. Some cells contains non-zero values (e.g. D2:D12, D15:D19 etc.), but other ones are blank (e.g. D13, D20:D21 etc.). In column F, it's calculated the medium energy consumption, as dynamic average of all values recorded at some point. However, the formula considers only non-zero cells (see results from F13, F20:F21, F28:F29 etc.), ignoring the blank ones. I need it to be adjusted, so that can count also the blank cells, generating in column F the average values from the column J. As it's seen, the range J2:J11 has identical values with those from D2:D11, but starting with J13 there are constant differences between the cells of the two columns because of the blank cells from column D. In addition, I need the formula to take into consideration all cells after the last non-zero one (e.g. F43:F52), so that they can be blank and not show average values calculated on the basis of the according range of column D (D43:D52).

Thank you!

2024.xlsx
	B	C	D		F				J
1	Year	Month	Monthly energy consumption (Kwh)
2	2021	Jan-21	67.82		67.82				67.82
3		Feb-21	61.25		64.54				64.54
4		Mar-21	59.19		62.75				62.75
5		Apr-21	48.36		59.16				59.16
6		May-21	49.97		57.32				57.32
7		Jun-21	48.36		55.83				55.83
8		Jul-21	49.97		54.99				54.99
9		Aug-21	16.12		50.13				50.13
10		Sep-21	67		52.00				52.00
11		Oct-21	11		47.90				47.90
12		Nov-21	44		47.55				47.55
13		Dec-21			47.55				43.59
14		Total	523.04
15	2022	Jan-22	54		48.09				44.39
16		Feb-22	66		49.46				45.93
17		Mar-22	78		51.50				48.07
18		Apr-22	68		52.60				49.32
19		May-22	-10		48.69				45.83
20		Jun-22			48.69				43.28
21		Jul-22			48.69				41.00
22		Aug-22	49		48.71				41.40
23		Sep-22	27		47.50				40.72
24		Oct-22	51		47.69				41.18
25		Nov-22			47.69				39.39
26		Dec-22			47.69				37.75
27		Total	383
28	2023	Jan-23			47.69				36.24
29		Feb-23			47.69				34.85
30		Mar-23	106		50.60				37.48
31		Apr-23	96		52.76				39.57
32		May-23	46		52.46				39.79
33		Jun-23	188		58.35				44.73
34		Jul-23			58.35				43.29
35		Aug-23	139		61.71				46.28
36		Sep-23	52		61.32				46.46
37		Oct-23	109		63.16				48.30
38		Nov-23	41		62.33				48.09
39		Dec-23	46		61.75				48.03
40		Total	823
41	2024	Jan-24	62		61.76				48.41
42		Feb-24	55		61.53				48.58
43		Mar-24			61.53				blank / no value
44		Apr-24			61.53				blank / no value
45		May-24			61.53				blank / no value
46		Jun-24			61.53				blank / no value
47		Jul-24			61.53				blank / no value
48		Aug-24			61.53				blank / no value
49		Sep-24			61.53				blank / no value
50		Oct-24			61.53				blank / no value
51		Nov-24			61.53				blank / no value
52		Dec-24			61.53				blank / no value
53		Total	117
Energy consumption

Cell Formulas
Range	Formula
C2:C13	C2	='Costuri energie generale'!P38
C15:C26	C15	='Costuri energie generale'!P55
C28:C39	C28	='Costuri energie generale'!P74
C41:C52	C41	='Costuri energie generale'!P93
F2:F52	F2	=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
D2:D13	D2	=IF(ISBLANK('Costuri energie generale'!Q38),"",'Costuri energie generale'!Q38)
D14,D53,D40,D27	D14	=SUM(D2:D13)
D15:D26	D15	=IF(ISBLANK('Costuri energie generale'!Q55),"",'Costuri energie generale'!Q55)
D28:D39	D28	=IF(ISBLANK('Costuri energie generale'!Q74),"",'Costuri energie generale'!Q74)
D41:D52	D41	=IF(ISBLANK('Costuri energie generale'!Q93),"",'Costuri energie generale'!Q93)

 

[JV0710]

Hi

Try

In Cell F2 enter : =IF(C4="Total","",SUM(AVERAGE(IF((C$4:C4<>"Total"),1*(D$4:D4))))) and drag down

 

[kvsrinivasamurthy]

In F3 copied down.

=AVERAGEIFS($D$3:$D3,$C$3:$C3,"<>"&"Total",$D$3:$D3,"<>""")

 

[vladimiratanasiu]

Hi

Try

In Cell F2 enter : =IF(C4="Total","",SUM(AVERAGE(IF((C$4:C4<>"Total"),1*(D$4:D4))))) and drag down

Thank you for your response! I used your formula, getting the attached results. Unfortunately, there are errors exactly in the cells related with the blank ones. And the results are similar with the original values, but I need them to show an average value (column F), as I mentioned in my original message. What is the proper solution? Thank you!

2024.xlsx
	B	C	D	E	F
1	Year	Month	Monthly energy consumption (Kwh)	Medium monthly energy consumption (Kwh)
2	2021	Jan-21	67.82	67.82	67.82
3		Feb-21	61.25	64.54	61.25
4		Mar-21	59.19	62.75	59.19
5		Apr-21	48.36	59.16	48.36
6		May-21	49.97	57.32	49.97
7		Jun-21	48.36	55.83	48.36
8		Jul-21	49.97	54.99	49.97
9		Aug-21	16.12	50.13	16.12
10		Sep-21	67	52.00	67.00
11		Oct-21	11	47.90	11.00
12		Nov-21	44	47.55	44.00
13		Dec-21		47.55	#VALUE!
14		Total	523.04
15	2022	Jan-22	54	48.09	54.00
16		Feb-22	66	49.46	66.00
17		Mar-22	78	51.50	78.00
18		Apr-22	68	52.60	68.00
19		May-22	-10	48.69	-10.00
20		Jun-22		48.69	#VALUE!
21		Jul-22		48.69	#VALUE!
22		Aug-22	49	48.71	49.00
23		Sep-22	27	47.50	27.00
24		Oct-22	51	47.69	51.00
25		Nov-22		47.69	#VALUE!
26		Dec-22		47.69	#VALUE!
27		Total	383
28	2023	Jan-23		47.69	#VALUE!
29		Feb-23		47.69	#VALUE!
30		Mar-23	106	50.60	106.00
31		Apr-23	96	52.76	96.00
32		May-23	46	52.46	46.00
33		Jun-23	188	58.35	188.00
34		Jul-23		58.35	#VALUE!
35		Aug-23	139	61.71	139.00
36		Sep-23	52	61.32	52.00
37		Oct-23	109	63.16	109.00
38		Nov-23	41	62.33	41.00
39		Dec-23	46	61.75	46.00
40		Total	823
Sheet1

Cell Formulas
Range	Formula
E2:E40	E2	=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
F2:F40	F2	=IF(C2="Total","",SUM(AVERAGE(IF((C$2:C2<>"Total"),1*(D$2:D2)))))
D14,D40,D27	D14	=SUM(D2:D13)

 

[vladimiratanasiu]

In F3 copied down.

=AVERAGEIFS($D$3:$D3,$C$3:$C3,"<>"&"Total",$D$3:$D3,"<>""")

I adjusted your formula to my table, but the results from the range F13:F52 are the same with the values from my original table. I need them to reflect the right average, counting in the calculations the blank cells, too. At the same time, the cells F14, F27, F40 and F43:F52 should contain no value. Could you modify your formula, in order to meet my specific needs? Thank you!

2024.xlsx
	B	C	D	E	F
1	Year	Month	Monthly energy consumption (Kwh)	Medium monthly energy consumption (Kwh)
2	2021	Jan-21	67.82	67.82	67.82
3		Feb-21	61.25	64.54	64.54
4		Mar-21	59.19	62.75	62.75
5		Apr-21	48.36	59.16	59.16
6		May-21	49.97	57.32	57.32
7		Jun-21	48.36	55.83	55.83
8		Jul-21	49.97	54.99	54.99
9		Aug-21	16.12	50.13	50.13
10		Sep-21	67	52.00	52.00
11		Oct-21	11	47.90	47.90
12		Nov-21	44	47.55	47.55
13		Dec-21		47.55	47.55
14		Total	523.04		47.55
15	2022	Jan-22	54	48.09	48.09
16		Feb-22	66	49.46	49.46
17		Mar-22	78	51.50	51.50
18		Apr-22	68	52.60	52.60
19		May-22	-10	48.69	48.69
20		Jun-22		48.69	48.69
21		Jul-22		48.69	48.69
22		Aug-22	49	48.71	48.71
23		Sep-22	27	47.50	47.50
24		Oct-22	51	47.69	47.69
25		Nov-22		47.69	47.69
26		Dec-22		47.69	47.69
27		Total	383		47.69
28	2023	Jan-23		47.69	47.69
29		Feb-23		47.69	47.69
30		Mar-23	106	50.60	50.60
31		Apr-23	96	52.76	52.76
32		May-23	46	52.46	52.46
33		Jun-23	188	58.35	58.35
34		Jul-23		58.35	58.35
35		Aug-23	139	61.71	61.71
36		Sep-23	52	61.32	61.32
37		Oct-23	109	63.16	63.16
38		Nov-23	41	62.33	62.33
39		Dec-23	46	61.75	61.75
40		Total	823		61.75
41	2024	Jan-24	62	61.76	61.76
42		Feb-24	55	61.53	61.53
43		Mar-24		61.53	61.53
44		Apr-24		61.53	61.53
45		May-24		61.53	61.53
46		Jun-24		61.53	61.53
47		Jul-24		61.53	61.53
48		Aug-24		61.53	61.53
49		Sep-24		61.53	61.53
50		Oct-24		61.53	61.53
51		Nov-24		61.53	61.53
52		Dec-24		61.53	61.53
53		Total	117
Sheet1

Cell Formulas
Range	Formula
E2:E52	E2	=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
F2	F2	=AVERAGEIFS($D2:$D$2,$C2:$C$2,"<>"&"Total",$D2:$D$2,"<>""")
F3:F52	F3	=AVERAGEIFS($D$2:$D3,$C$2:$C3,"<>"&"Total",$D$2:$D3,"<>""")
D14,D53,D40,D27	D14	=SUM(D2:D13)

 

[kvsrinivasamurthy]

Do you want the result in column J. I am not clear. Can you explain more clearly.

 

[vladimiratanasiu]

Do you want the result in column J. I am not clear. Can you explain more clearly.

I mentioned in my first message that I need a formula to generate the results inserted in the column J of my first table. Each one of them is calculated as an average of all values recorded from January 2021 to that month. But when dividing the sum resulted in that month, it's necessary to be counted also the previous months with no values (e.g. E13, E20, E21 etc), and including them as elements of the average. The original formulas from column F of the initial table makes those calculations, but doesn't consider the blank cells of column D as a dividing element / factor. That's why, in the cells of column F that are in line with the blank cells of column D (e.g. F13, F20, F21 etc.) formula repeats the results generated in the last cell that has a non-zero equivalent cell in column D (in our case, F13 and F19). I need also, as in the original formula, the new one to ignore the cells that summarize all values from one year (D14, D27, D40), and keep blank the cells of column E from that row (E14, E27, E40). Could you help me in this sense? Thank you!

 

[vladimiratanasiu]

.

 

[kvsrinivasamurthy]

In K3 copied down

=IF(OR($C3="Total",SUMIF($C3:$C$54,"<>"&"Total",$D3:$D$54)=0),"",SUMPRODUCT($D$3:$D3*($C$3:$C3<>"Total"))/SUMPRODUCT(1*($C$3:$C3<>"Total")))

 

[vladimiratanasiu]

In K3 copied down

=IF(OR($C3="Total",SUMIF($C3:$C$54,"<>"&"Total",$D3:$D$54)=0),"",SUMPRODUCT($D$3:$D3*($C$3:$C3<>"Total"))/SUMPRODUCT(1*($C$3:$C3<>"Total")))

Thank you for the answer. I tried your formula, exactly as you mentioned. Unfortunately, I've got some wrong values and errors. I attached to this answer a table, that includes column L. It contains the desired results and the simple formulas used to get each one them. Where is mentioned "BLANK", I need that cell to contain formula but show no value. Could you / someone else help me, in this sense?

Thank you!

2024.xlsx
	B	C	D	E						K	L	M
1	Year	Month	Monthly energy consumption (Kwh)	Medium monthly energy consumption (Kwh)							Necessary results
2	2021	Jan-21	67.82	67.82							67.82
3		Feb-21	61.25	64.54						61.25	64.54
4		Mar-21	59.19	62.75						60.22	62.75
5		Apr-21	48.36	59.16						56.27	59.16
6		May-21	49.97	57.32						54.69	57.32
7		Jun-21	48.36	55.83						53.43	55.83
8		Jul-21	49.97	54.99						52.85	54.99
9		Aug-21	16.12	50.13						47.60	50.13
10		Sep-21	67	52.00						50.03	52.00
11		Oct-21	11	47.90						45.69	47.90
12		Nov-21	44	47.55						45.52	47.55
13		Dec-21		47.55						#VALUE!	43.59
14		Total	523.04								BLANK
15	2022	Jan-22	54	48.09						#VALUE!	44.39
16		Feb-22	66	49.46						#VALUE!	45.93
17		Mar-22	78	51.50						#VALUE!	48.07
18		Apr-22	68	52.60						#VALUE!	49.32
19		May-22	-10	48.69						#VALUE!	45.83
20		Jun-22		48.69						#VALUE!	43.28
21		Jul-22		48.69						#VALUE!	41.00
22		Aug-22	49	48.71						#VALUE!	42.75
23		Sep-22	27	47.50						#VALUE!	43.14
24		Oct-22	51	47.69						#VALUE!	41.18
25		Nov-22		47.69						#VALUE!	39.39
26		Dec-22		47.69						#VALUE!	37.75
27		Total	383								BLANK
28	2023	Jan-23		47.69						#VALUE!	36.24
29		Feb-23		47.69						#VALUE!	34.85
30		Mar-23	106	50.60						#VALUE!	37.48
31		Apr-23	96	52.76						#VALUE!	…
32		May-23	46	52.46						#VALUE!	…
33		Jun-23	188	58.35						#VALUE!	…
34		Jul-23		58.35						#VALUE!	…
35		Aug-23	139	61.71						#VALUE!	…
36		Sep-23	52	61.32						#VALUE!	…
37		Oct-23	109	63.16						#VALUE!	…
38		Nov-23	41	62.33						#VALUE!	…
39		Dec-23	46	61.75						#VALUE!	…
40		Total	823								BLANK
41	2024	Jan-24	62	61.76						#VALUE!	48.41
42		Feb-24	55	61.53						#VALUE!	48.58
43		Mar-24									47.33	, but Blank cell
44		Apr-24									46.15	, but Blank cell
45		May-24									…	, but Blank cell
46		Jun-24									…	, but Blank cell
47		Jul-24									…	, but Blank cell
48		Aug-24									…	, but Blank cell
49		Sep-24									…	, but Blank cell
50		Oct-24									…	, but Blank cell
51		Nov-24									…	, but Blank cell
52		Dec-24									…	, but Blank cell
53		Total	117								BLANK
Sheet1

Cell Formulas
Range	Formula
L2	L2	=D2/1
L3	L3	=SUM(D2:D3)/2
L4	L4	=SUM(D2:D4)/3
L5	L5	=SUM(D2:D5)/4
L6	L6	=SUM(D2:D6)/5
L7	L7	=SUM(D2:D7)/6
L8	L8	=SUM(D2:D8)/7
L9	L9	=SUM(D2:D9)/8
L10	L10	=SUM(D2:D10)/9
L11	L11	=SUM(D2:D11)/10
L12	L12	=SUM(D2:D12)/11
L13	L13	=SUM(D2:D13)/12
D14,D53,D40,D27	D14	=SUM(D2:D13)
L15	L15	=SUM(D2:D13,D15)/13
L16	L16	=SUM(D2:D13,D15:D16)/14
L17	L17	=SUM(D2:D13,D15:D17)/15
L18	L18	=SUM(D2:D13,D15:D18)/16
L19	L19	=SUM(D2:D13,D15:D19)/17
L20	L20	=SUM(D2:D13,D15:D20)/18
L21	L21	=SUM(D2:D13,D15:D21)/19
L22	L22	=SUM(D2:D13,D15:D23)/20
L23	L23	=SUM(D2:D13,D15:D24)/21
L24	L24	=SUM(D2:D13,D15:D25)/22
L25	L25	=SUM(D2:D13,D15:D26)/23
L26	L26	=SUM(D2:D13,D15:D26)/24
L28	L28	=SUM(D2:D13,D15:D26,D28)/25
L29	L29	=SUM(D2:D13,D15:D26,D28:D29)/26
L30	L30	=SUM(D2:D13,D15:D26,D28:D30)/27
E2:E42	E2	=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
L41	L41	=SUM(D2:D13,D15:D26,D28:D39,D41)/37
L42	L42	=SUM(D2:D13,D15:D26,D28:D39,D41:D42)/38
L43	L43	=SUM(D2:D13,D15:D26,D28:D39,D41:D43)/39
L44	L44	=SUM(D2:D13,D15:D26,D28:D39,D41:D44)/40
K3:K52	K3	=IF(OR($C3="Total",SUMIF($C3:$C$54,"<>"&"Total",$D3:$D$54)=0),"",SUMPRODUCT($D$3:$D3*($C$3:$C3<>"Total"))/SUMPRODUCT(1*($C$3:$C3<>"Total")))