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)

 

[kvsrinivasamurthy]

I think data starts from 2nd row.
In K2 copied down

=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))

 

[vladimiratanasiu]

I think data starts from 2nd row.
In K2 copied down

=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))

I applied it, but in the range K13:K42 it returns only errors.

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	67.82
3		Feb-21	61.25	64.54						64.54	64.54
4		Mar-21	59.19	62.75						62.75	62.75
5		Apr-21	48.36	59.16						59.16	59.16
6		May-21	49.97	57.32						57.32	57.32
7		Jun-21	48.36	55.83						55.83	55.83
8		Jul-21	49.97	54.99						54.99	54.99
9		Aug-21	16.12	50.13						50.13	50.13
10		Sep-21	67	52.00						52.00	52.00
11		Oct-21	11	47.90						47.90	47.90
12		Nov-21	44	47.55						47.55	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
K2:K53	K2	=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))

 

[kvsrinivasamurthy]

Upload file showing problem.

 

[vladimiratanasiu]

Upload file showing problem.

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	67.82
3		Feb-21	61.25	64.54						64.54	64.54
4		Mar-21	59.19	62.75						62.75	62.75
5		Apr-21	48.36	59.16						59.16	59.16
6		May-21	49.97	57.32						57.32	57.32
7		Jun-21	48.36	55.83						55.83	55.83
8		Jul-21	49.97	54.99						54.99	54.99
9		Aug-21	16.12	50.13						50.13	50.13
10		Sep-21	67	52.00						52.00	52.00
11		Oct-21	11	47.90						47.90	47.90
12		Nov-21	44	47.55						47.55	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
K2:K53	K2	=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))

 

[vladimiratanasiu]

Upload file showing problem.

2024.xlsx

Shared with Dropbox

www.dropbox.com

 

[kvsrinivasamurthy]

The blank cells are giving problem . They strings of length 0. To confirm Go to the blank cell in column C and Just Press delete .
Any to overcome this problem I have changed the formula. Now it is ARRAY formula (needs Ctrl+Shift+Enter together).
In K2

=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUM(IFERROR($D$2:$D2*($C$2:$C2<>"Total"),0))/SUMPRODUCT(1*($C$2:$C2<>"Total")))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[vladimiratanasiu]

The blank cells are giving problem . They strings of length 0. To confirm Go to the blank cell in column C and Just Press delete .
Any to overcome this problem I have changed the formula. Now it is ARRAY formula (needs Ctrl+Shift+Enter together).
In K2

=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUM(IFERROR($D$2:$D2*($C$2:$C2<>"Total"),0))/SUMPRODUCT(1*($C$2:$C2<>"Total")))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

I adjusted your formula and now it's working perfectly. Thank you!

 

[kvsrinivasamurthy]

Welcome.