Formula to calculate dynamic average of monthly costs

[vladimiratanasiu]

Hello!

I posted a topic about one week ago (Calculate dynamic average of monthly costs), but didn't get yet a proper solution. Basically, I have a multiannual situation (see C2:C40) with monthly expenses, that are updated continuously with new information (e.g. C35:C39 and so on). Some cells show total expenses of a year (e.g C14, C27, C40 etc.). I need a dynamic formula to calculate the average value of all months, including automatically the new added ones but ignoring the annual totals. I've got the formula from the attached table, but it must be operated manually each time new data are inserted ( e.g. C34 / April 2023) and doesn't take automatically into account other new values added after that period (e.g. C35:C39). In these conditions, I need one solution (formula, macro) to generate a single result that moves down progressively in line with the last value available (see column D), or working in a merged range that expensed automatically when are added new months (see column E).

Thank you!

Book1.xlsx
	A	B	C	D	E
1	Year	Month	Costs
2	2021	01/01/2021	67.82		43.61419
3		01/02/2021	61.25
4		01/03/2021	59.19
5		01/04/2021	48.36
6		01/05/2021	49.97
7		01/06/2021	48.36
8		01/07/2021	49.97
9		01/08/2021	16.12
10		01/09/2021	67
11		01/10/2021	11
12		01/11/2021	44
13		01/12/2021	0
14		Total	523.04
15	2022	01/01/2022	54
16		01/02/2022	66
17		01/03/2022	78
18		01/04/2022	68
19		01/05/2022	-10
20		01/06/2022	0
21		01/07/2022	0
22		01/08/2022	49
23		01/09/2022	27
24		01/10/2022	51
25		01/11/2022	0
26		01/12/2022	0
27		Total	383
28	2023	01/01/2023	0
29		01/02/2023	0
30		01/03/2023	106
31		01/04/2023	96
32		01/05/2023	46
33		01/06/2023	188
34		01/07/2023	10	43.61419
35		01/08/2023	20
36		01/09/2023	30
37		01/10/2023	40
38		01/11/2023	50
39		01/12/2023	60
40		Total	646
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGEIFS(D:D,C:C,"<="&C34)
C14,C40,C27	C14	=SUM(C2:C13)
D34	D34	=AVERAGEIFS(C:C,B:B,"<="&B34)

 

[JamesCanale]

I think this will work in excel 2021.

MrExcelPlayground19.xlsx
	A	B	C	D
1	Year	Month	Costs
2	2021	1/1/2021	67.82
3		2/1/2021	61.25
4		3/1/2021	59.19
5		4/1/2021	48.36
6		5/1/2021	49.97
7		6/1/2021	48.36
8		7/1/2021	49.97
9		8/1/2021	16.12
10		9/1/2021	67
11		10/1/2021	11
12		11/1/2021	44
13		12/1/2021	0
14		Total	523.04
15	2022	1/1/2022	54
16		2/1/2022	66
17		3/1/2022	78
18		4/1/2022	68
19		5/1/2022	-10
20		6/1/2022	0
21		7/1/2022	0
22		8/1/2022	49
23		9/1/2022	27
24		10/1/2022	51
25		11/1/2022	0
26		12/1/2022	0
27		Total	383
28	2023	1/1/2023	0
29		2/1/2023	0
30		3/1/2023	106
31		4/1/2023	96
32		5/1/2023	46
33		6/1/2023	188
34		7/1/2023	10	43.61419
35		8/1/2023
36		9/1/2023
37		10/1/2023
38		11/1/2023
39		12/1/2023
40		Total	446
Sheet3

Cell Formulas
Range	Formula
D2:D34	D2	=LET(a,COUNTA(C:C)-2,bb,SEQUENCE(a),c,AVERAGEIFS(C:C,B:B,">1"),IF(bb=a,c,""))
C14,C40,C27	C14	=SUM(C2:C13)
Dynamic array formulas.

 

[vladimiratanasiu]

I think this will work in excel 2021.

MrExcelPlayground19.xlsx
	A	B	C	D
1	Year	Month	Costs
2	2021	1/1/2021	67.82
3		2/1/2021	61.25
4		3/1/2021	59.19
5		4/1/2021	48.36
6		5/1/2021	49.97
7		6/1/2021	48.36
8		7/1/2021	49.97
9		8/1/2021	16.12
10		9/1/2021	67
11		10/1/2021	11
12		11/1/2021	44
13		12/1/2021	0
14		Total	523.04
15	2022	1/1/2022	54
16		2/1/2022	66
17		3/1/2022	78
18		4/1/2022	68
19		5/1/2022	-10
20		6/1/2022	0
21		7/1/2022	0
22		8/1/2022	49
23		9/1/2022	27
24		10/1/2022	51
25		11/1/2022	0
26		12/1/2022	0
27		Total	383
28	2023	1/1/2023	0
29		2/1/2023	0
30		3/1/2023	106
31		4/1/2023	96
32		5/1/2023	46
33		6/1/2023	188
34		7/1/2023	10	43.61419
35		8/1/2023
36		9/1/2023
37		10/1/2023
38		11/1/2023
39		12/1/2023
40		Total	446
Sheet3

Cell Formulas
Range	Formula
D2:D34	D2	=LET(a,COUNTA(C:C)-2,bb,SEQUENCE(a),c,AVERAGEIFS(C:C,B:B,">1"),IF(bb=a,c,""))
C14,C40,C27	C14	=SUM(C2:C13)
Dynamic array formulas.

Thank you, JamesCanale! Your formula matches exactly what I need and works perfectly!