Adjust formula of dynamic average

[vladimiratanasiu]

Hello!

I posted some weeks ago a message regarding a dynamic formula of monthly costs, getting in the answer Formula to calculate dynamic average of monthly costs a good solution adapted to the conditions at that time. Shortely, it calculates dinamically (see value from D88) the average of all monthly values , excepting the annually totals and moving down in line with the each new number inserted. If I add the new values manually, the formula works according to my needs. The problem is when my monthly cells are linked by formulas with data from another table (see F51:F88) and the values are imported automatically from it. In our example, the cells from C85:C88 are linked with those from F85:F88, but the last ones have no content yet. In these conditions, the cells C85:C88 show value 0 / blank space, and the average (D88) is calculated and moves automatically in line with the last cell (C88). My question is how could be adjusted the given formula, so that the right result (e.g. value from E84) can be shown progressively in line only with the last cell (e.g. C84) related with real data from the source table , and can avoid in calculation the next ones (C85:C88) that have no equivalent numerical in the source range.

Thank you!

Book1.xlsx
	A	B	C	D	E	F
1	Year	Month	Costs
2	2017	Apr-17	34.83			34.83
3		May-17	72			72
4		Jun-17	74.77			74.77
5		Jul-17	77.74			77.74
6		Aug-17	76.5			76.5
7		Sep-17	67.74			67.74
8		Oct-17	130.11			130.11
9		Nov-17	183.44			183.44
10		Dec-17	189.56			189.56
11		Total	906.69
12	2018	Jan-18	189.56			189.56
13		Feb-18	169.62			169.62
14		Mar-18	177.41			177.41
15		Apr-18	128.08			128.08
16		May-18	132.35			132.35
17		Jun-18	128.08			128.08
18		Jul-18	132.35			132.35
19		Aug-18	132.35			132.35
20		Sep-18	113.2			113.2
21		Oct-18	78.29			78.29
22		Nov-18	75.77			75.77
23		Dec-18	78.29			78.29
24		Total	1535.35
25	2019	Jan-19	78.29			78.29
26		Feb-19	70.72			70.72
27		Mar-19	71.48			71.48
28		Apr-19	64.42			64.42
29		May-19	66.57			66.57
30		Jun-19	64.42			64.42
31		Jul-19	66.57			66.57
32		Aug-19	66.57			66.57
33		Sep-19	66.29			66.29
34		Oct-19	69.79			69.79
35		Nov-19	67.54			67.54
36		Dec-19	69.79			69.79
37		Total	822.45
38	2020	Jan-20	69.79			69.79
39		Feb-20	65.29			65.29
40		Mar-20	56.51			56.51
41		Apr-20	54.27			54.27
42		May-20	56.08			56.08
43		Jun-20	54.27			54.27
44		Jul-20	56.08			56.08
45		Aug-20	56.08			56.08
46		Sep-20	55.07			55.07
47		Oct-20	57.84			57.84
48		Nov-20	55.97			55.97
49		Dec-20	13.06			13.06
50		Total	650.31
51	2021	1/1/2021	67.82			67.82
52		2/1/2021	61.25			61.25
53		3/1/2021	59.19			59.19
54		4/1/2021	48.36			48.36
55		5/1/2021	49.97			49.97
56		6/1/2021	48.36			48.36
57		7/1/2021	49.97			49.97
58		8/1/2021	16.12			16.12
59		9/1/2021	67			67
60		10/1/2021	11			11
61		11/1/2021	44			44
62		12/1/2021	0			0
63		Total	523.04
64	2022	1/1/2022	54			54
65		2/1/2022	66			66
66		3/1/2022	78			78
67		4/1/2022	68			68
68		5/1/2022	-10			-10
69		6/1/2022	0			0
70		7/1/2022	0			0
71		8/1/2022	49			49
72		9/1/2022	27			27
73		10/1/2022	51			51
74		11/1/2022	0			0
75		12/1/2022	0			0
76		Total	383
77	2023	1/1/2023	0			0
78		2/1/2023	0			0
79		3/1/2023	106			106
80		4/1/2023	96			96
81		5/1/2023	46			46
82		6/1/2023	188			188
83		7/1/2023	0			0
84		8/1/2023	139		70.07584	139
85		9/1/2023	0
86		10/1/2023	0
87		11/1/2023	0
88		12/1/2023	0	66.61531
89		Total	575
Sheet1

Cell Formulas
Range	Formula
D2:D88	D2	=LET(a,COUNTA(C:C)-2,bb,SEQUENCE(a),c,AVERAGEIFS(C:C,B:B,">1"),IF(bb=a,c,""))
C2:C10,C12:C23,C25:C36,C38:C49,C51:C62,C64:C75,C77:C88	C2	=F2
C11	C11	=SUM(C2:C10)
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)
Dynamic array formulas.

 

[Dave Patton]

vladimiratanasiu's post #8​

I posted a suggestion that you could try. It does not appear that you tried the suggestion Excel Data Subtotal.
I did not state that it was dynamic. If you structure your information correctly, it can yield correct answers quite easily and efficiently.

Hopefully, JamesCanale and/or someone else will provide the formulas that you need.

 

[JamesCanale]

So this will find the highest date in "Sheet 1" (for my thing, it's just whats in rows N and O), and put the running average of everything in column C - even stuff before the earliest part of Sheet 1.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Month	Costs											Electricity
2	2017	4/1/2017	34.83
3		5/1/2017	72											Period	Costs
4		6/1/2017	74.77											1/1/2019	78.29
5		7/1/2017	77.74											2/1/2019	70.72
6		8/1/2017	76.5											3/1/2019	71.48
7		9/1/2017	67.74											4/1/2019	64.42
8		10/1/2017	130.11											5/1/2019	66.57
9		11/1/2017	183.44											6/1/2019	64.42
10		12/1/2017	189.56											7/1/2019	66.57
11		Total	906.69											8/1/2019	66.57
12	2018	1/1/2018	189.56											9/1/2019	66.29
13		2/1/2018	169.62											10/1/2019	69.79
14		3/1/2018	177.41											11/1/2019	67.54
15		4/1/2018	128.08											12/1/2019	69.79
16		5/1/2018	132.35											Annual total	822.45
17		6/1/2018	128.08
18		7/1/2018	132.35
19		8/1/2018	132.35
20		9/1/2018	113.2
21		10/1/2018	78.29											1/1/2020	69.79
22		11/1/2018	75.77											2/1/2020	65.29
23		12/1/2018	78.29											3/1/2020	56.51
24		Total	1535.35											4/1/2020	54.27
25	2019	1/1/2019	78.29											5/1/2020	56.08
26		2/1/2019	70.72											6/1/2020	54.27
27		3/1/2019	71.48											7/1/2020	56.08
28		4/1/2019	64.42											8/1/2020	56.08
29		5/1/2019	66.57											9/1/2020	55.07
30		6/1/2019	64.42											10/1/2020	57.84
31		7/1/2019	66.57											11/1/2020	55.97
32		8/1/2019	66.57											12/1/2020	13.06
33		9/1/2019	66.29											Annual total	650.31
34		10/1/2019	69.79
35		11/1/2019	67.54
36		12/1/2019	69.79
37		Total	822.45
38	2020	1/1/2020	69.79											1/1/2021	67.82
39		2/1/2020	65.29											2/1/2021	61.25
40		3/1/2020	56.51											3/1/2021	59.19
41		4/1/2020	54.27											4/1/2021	48.36
42		5/1/2020	56.08											5/1/2021	49.97
43		6/1/2020	54.27											6/1/2021	48.36
44		7/1/2020	56.08											7/1/2021	49.97
45		8/1/2020	56.08											8/1/2021	16.12
46		9/1/2020	55.07											9/1/2021	67
47		10/1/2020	57.84											10/1/2021	11
48		11/1/2020	55.97											11/1/2021	44
49		12/1/2020	13.06											12/1/2021	0
50		Total	650.31											Annual total	523.04
51	2021	1/1/2021	67.82
52		2/1/2021	61.25
53		3/1/2021	59.19
54		4/1/2021	48.36
55		5/1/2021	49.97											1/1/2022	54
56		6/1/2021	48.36											2/1/2022	66
57		7/1/2021	49.97											3/1/2022	78
58		8/1/2021	16.12											4/1/2022	68
59		9/1/2021	67											5/1/2022	-10
60		10/1/2021	11											6/1/2022	0
61		11/1/2021	44											7/1/2022	0
62		12/1/2021	0											8/1/2022	49
63		Total	523.04											9/1/2022	27
64	2022	1/1/2022	54											10/1/2022	51
65		2/1/2022	66											11/1/2022	0
66		3/1/2022	78											12/1/2022	0
67		4/1/2022	68											Annual total	383
68		5/1/2022	-10
69		6/1/2022	0
70		7/1/2022	0
71		8/1/2022	49
72		9/1/2022	27
73		10/1/2022	51
74		11/1/2022	0											1/1/2023	0
75		12/1/2022	0											2/1/2023	0
76		Total	383											3/1/2023	106
77	2023	1/1/2023	0											4/1/2023	96
78		2/1/2023	0											5/1/2023	46
79		3/1/2023	106											6/1/2023	188
80		4/1/2023	96											7/1/2023	0
81		5/1/2023	46											8/1/2023	139
82		6/1/2023	188											9/1/2023
83		7/1/2023	0											10/1/2023
84		8/1/2023	139	70.07584										11/1/2023
85		9/1/2023	0											12/1/2023
86		10/1/2023	0											Annual total	575
87		11/1/2023	0
88		12/1/2023	0
89		Total	575
Sheet2

Cell Formulas
Range	Formula
D2:D84	D2	=LET(a,MATCH(MAXIFS(N:N,O:O,"<>"),B:B,0)-1,b,SEQUENCE(a),c,AVERAGEIFS(OFFSET(C2,0,0,a),OFFSET(B2,0,0,a),">0"),IF(b=a,c,""))
C11	C11	=SUM(C2:C10)
O16,C24,C37,C50,C63,C76,C89,O86,O67,O50,O33	O16	=SUM(O4:O15)
B25:C36	B25	=N4
B38:C49	B38	=N21
B51:C62	B51	=N38
B64:C75	B64	=N55
B77:C88	B77	=N74
Dynamic array formulas.

 

[Dave Patton]

Another approach
Separate the reporting from the data; this is often on a distinct sheet.
Do not enter anything below the data except real numbers that you want to include in the sum or average.
Use a Dynamic range for the sum and average

T202308a.xlsm
	B	C	D	E	F	G
1	Date	Costs			Sum	Average
2	1-Apr-17	34.83		Total	5,395.84	70.08
3	1-May-17	72.00		2017	906.69	100.74
4	1-Jun-17	74.77		2018	1,535.35	127.95
5	1-Jul-17	77.74		2019	822.45	68.54
6	1-Aug-17	76.50		2020	650.31	54.19
7	1-Sep-17	67.74		2021	523.04	43.59
8	1-Oct-17	130.11		2022	383.00	31.92
9	1-Nov-17	183.44		2023	575.00	71.88
10	1-Dec-17	189.56
11	1-Jan-18	189.56
12	1-Feb-18	169.62
13	1-Mar-18	177.41
14	1-Apr-18	128.08
15	1-May-18	132.35
16	1-Jun-18	128.08
17	1-Jul-18	132.35
18	1-Aug-18	132.35
19	1-Sep-18	113.20
20	1-Oct-18	78.29
21	1-Nov-18	75.77
22	1-Dec-18	78.29
23	1-Jan-19	78.29
24	1-Feb-19	70.72
25	1-Mar-19	71.48
26	1-Apr-19	64.42
27	1-May-19	66.57
28	1-Jun-19	64.42
29	1-Jul-19	66.57
30	1-Aug-19	66.57
31	1-Sep-19	66.29
32	1-Oct-19	69.79
33	1-Nov-19	67.54
34	1-Dec-19	69.79
35	1-Jan-20	69.79
36	1-Feb-20	65.29
37	1-Mar-20	56.51
38	1-Apr-20	54.27
39	1-May-20	56.08
40	1-Jun-20	54.27
41	1-Jul-20	56.08
42	1-Aug-20	56.08
43	1-Sep-20	55.07
44	1-Oct-20	57.84
45	1-Nov-20	55.97
46	1-Dec-20	13.06
47	1-Jan-21	67.82
48	1-Feb-21	61.25
49	1-Mar-21	59.19
50	1-Apr-21	48.36
51	1-May-21	49.97
52	1-Jun-21	48.36
53	1-Jul-21	49.97
54	1-Aug-21	16.12
55	1-Sep-21	67.00
56	1-Oct-21	11.00
57	1-Nov-21	44.00
58	1-Dec-21	0.00
59	1-Jan-22	54.00
60	1-Feb-22	66.00
61	1-Mar-22	78.00
62	1-Apr-22	68.00
63	1-May-22	-10.00
64	1-Jun-22	0.00
65	1-Jul-22	0.00
66	1-Aug-22	49.00
67	1-Sep-22	27.00
68	1-Oct-22	51.00
69	1-Nov-22	0.00
70	1-Dec-22	0.00
71	1-Jan-23	0.00
72	1-Feb-23	0.00
73	1-Mar-23	106.00
74	1-Apr-23	96.00
75	1-May-23	46.00
76	1-Jun-23	188.00
77	1-Jul-23	0.00
78	1-Aug-23	139.00
4eee

Cell Formulas
Range	Formula
F2	F2	=SUM(Costs)
G2	G2	=AVERAGE(Costs)
F3:F9	F3	=SUMIFS(Costs,Dates,">="&E3,Dates,"<"&EDATE(E3,12))
G3:G9	G3	=AVERAGEIFS(Costs,Dates,">="&E3,Dates,"<"&EDATE(E3,12))

 

[vladimiratanasiu]

So this will find the highest date in "Sheet 1" (for my thing, it's just whats in rows N and O), and put the running average of everything in column C - even stuff before the earliest part of Sheet 1.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Month	Costs											Electricity
2	2017	4/1/2017	34.83
3		5/1/2017	72											Period	Costs
4		6/1/2017	74.77											1/1/2019	78.29
5		7/1/2017	77.74											2/1/2019	70.72
6		8/1/2017	76.5											3/1/2019	71.48
7		9/1/2017	67.74											4/1/2019	64.42
8		10/1/2017	130.11											5/1/2019	66.57
9		11/1/2017	183.44											6/1/2019	64.42
10		12/1/2017	189.56											7/1/2019	66.57
11		Total	906.69											8/1/2019	66.57
12	2018	1/1/2018	189.56											9/1/2019	66.29
13		2/1/2018	169.62											10/1/2019	69.79
14		3/1/2018	177.41											11/1/2019	67.54
15		4/1/2018	128.08											12/1/2019	69.79
16		5/1/2018	132.35											Annual total	822.45
17		6/1/2018	128.08
18		7/1/2018	132.35
19		8/1/2018	132.35
20		9/1/2018	113.2
21		10/1/2018	78.29											1/1/2020	69.79
22		11/1/2018	75.77											2/1/2020	65.29
23		12/1/2018	78.29											3/1/2020	56.51
24		Total	1535.35											4/1/2020	54.27
25	2019	1/1/2019	78.29											5/1/2020	56.08
26		2/1/2019	70.72											6/1/2020	54.27
27		3/1/2019	71.48											7/1/2020	56.08
28		4/1/2019	64.42											8/1/2020	56.08
29		5/1/2019	66.57											9/1/2020	55.07
30		6/1/2019	64.42											10/1/2020	57.84
31		7/1/2019	66.57											11/1/2020	55.97
32		8/1/2019	66.57											12/1/2020	13.06
33		9/1/2019	66.29											Annual total	650.31
34		10/1/2019	69.79
35		11/1/2019	67.54
36		12/1/2019	69.79
37		Total	822.45
38	2020	1/1/2020	69.79											1/1/2021	67.82
39		2/1/2020	65.29											2/1/2021	61.25
40		3/1/2020	56.51											3/1/2021	59.19
41		4/1/2020	54.27											4/1/2021	48.36
42		5/1/2020	56.08											5/1/2021	49.97
43		6/1/2020	54.27											6/1/2021	48.36
44		7/1/2020	56.08											7/1/2021	49.97
45		8/1/2020	56.08											8/1/2021	16.12
46		9/1/2020	55.07											9/1/2021	67
47		10/1/2020	57.84											10/1/2021	11
48		11/1/2020	55.97											11/1/2021	44
49		12/1/2020	13.06											12/1/2021	0
50		Total	650.31											Annual total	523.04
51	2021	1/1/2021	67.82
52		2/1/2021	61.25
53		3/1/2021	59.19
54		4/1/2021	48.36
55		5/1/2021	49.97											1/1/2022	54
56		6/1/2021	48.36											2/1/2022	66
57		7/1/2021	49.97											3/1/2022	78
58		8/1/2021	16.12											4/1/2022	68
59		9/1/2021	67											5/1/2022	-10
60		10/1/2021	11											6/1/2022	0
61		11/1/2021	44											7/1/2022	0
62		12/1/2021	0											8/1/2022	49
63		Total	523.04											9/1/2022	27
64	2022	1/1/2022	54											10/1/2022	51
65		2/1/2022	66											11/1/2022	0
66		3/1/2022	78											12/1/2022	0
67		4/1/2022	68											Annual total	383
68		5/1/2022	-10
69		6/1/2022	0
70		7/1/2022	0
71		8/1/2022	49
72		9/1/2022	27
73		10/1/2022	51
74		11/1/2022	0											1/1/2023	0
75		12/1/2022	0											2/1/2023	0
76		Total	383											3/1/2023	106
77	2023	1/1/2023	0											4/1/2023	96
78		2/1/2023	0											5/1/2023	46
79		3/1/2023	106											6/1/2023	188
80		4/1/2023	96											7/1/2023	0
81		5/1/2023	46											8/1/2023	139
82		6/1/2023	188											9/1/2023
83		7/1/2023	0											10/1/2023
84		8/1/2023	139	70.07584										11/1/2023
85		9/1/2023	0											12/1/2023
86		10/1/2023	0											Annual total	575
87		11/1/2023	0
88		12/1/2023	0
89		Total	575
Sheet2

Cell Formulas
Range	Formula
D2:D84	D2	=LET(a,MATCH(MAXIFS(N:N,O:O,"<>"),B:B,0)-1,b,SEQUENCE(a),c,AVERAGEIFS(OFFSET(C2,0,0,a),OFFSET(B2,0,0,a),">0"),IF(b=a,c,""))
C11	C11	=SUM(C2:C10)
O16,C24,C37,C50,C63,C76,C89,O86,O67,O50,O33	O16	=SUM(O4:O15)
B25:C36	B25	=N4
B38:C49	B38	=N21
B51:C62	B51	=N38
B64:C75	B64	=N55
B77:C88	B77	=N74
Dynamic array formulas.

Thank you very much! I adjusted your formula to my worksheets structure and it works wonderful!

 

[Peter_SSs]

Thank you very much! I adjusted your formula to my worksheets structure and it works wonderful!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.