Formula to calculate automatically progressive average of monthly expenses

[vladimiratanasiu]

Hello!

I have a budget table with monthly expenses of an organization. Each cell filled in yellow summarizes costs resulted from all days of that month. I need a formula, to calculate automatically in the red cell progressive average of results (e.g. March 2022 – 1; September 2022 – 3.78), from the first month up to the last current one. Sometimes there are no monthly costs, but I wish they be taken into account as average elements, too.

Thank you!

Progressive average.xlsx
	A	B
1	31-Jan-22	..............
2		1
3	28-Feb-22	…...........
4		2
5	31-Mar-22	…...........
6		0
7	30-Apr-22	…...........
8		4
9	31-May-22	…...........
10		5
11	30-Jun-22	…...........
12		6
13	31-Jul-22	…...........
14		7
15	31-Aug-22	…...........
16		0
17	30-Sep-22	…...........
18		9
19	31-Oct-22	…...........
20		0
21	30-Nov-22	…...........
22		0
23	31-Dec-22	…...........
24		0
25	Progressive average
26	Jan-22	1.00
27	Feb-22	1.50
28	Mar-22	1.00
29	Apr-22	1.75
30	…...................	…...........
31	Aug-22	3.125
32	Sep-22	3.78
33	Oct-22	0.00
34	Nov-22	0.00
35	Dec-22	0.00
Sheet1

Cell Formulas
Range	Formula
B26	B26	=B2
B27	B27	=(B2+B4)/2
B28	B28	=(B2+B4+B6)/3
B29	B29	=(B2+B4+B6+B8)/4
B31	B31	=(B2+B4+B6+B8+B10+B12+B14+B16)/8
B32	B32	=(B2+B4+B6+B8+B10+B12+B14+B16+B18)/9

 

[Peter_SSs]

Is this what you mean?

22 09 25.xlsm
	A	B
1	31-Jan-22	..............
2		1
3	28-Feb-22	…...........
4		2
5	31-Mar-22	…...........
6		0
7	30-Apr-22	…...........
8		4
9	31-May-22	…...........
10		5
11	30-Jun-22	…...........
12		6
13	31-Jul-22	…...........
14		7
15	31-Aug-22	…...........
16		0
17	30-Sep-22	…...........
18		9
19	31-Oct-22	…...........
20		0
21	30-Nov-22	…...........
22		0
23	31-Dec-22	…...........
24		0
25	Progressive average	3.77777778
Average

Cell Formulas
Range	Formula
B25	B25	=AVERAGE(B1:INDEX(B1:B24,MONTH(TODAY())*2))

 

[vladimiratanasiu]

Is this what you mean?

22 09 25.xlsm
	A	B
1	31-Jan-22	..............
2		1
3	28-Feb-22	…...........
4		2
5	31-Mar-22	…...........
6		0
7	30-Apr-22	…...........
8		4
9	31-May-22	…...........
10		5
11	30-Jun-22	…...........
12		6
13	31-Jul-22	…...........
14		7
15	31-Aug-22	…...........
16		0
17	30-Sep-22	…...........
18		9
19	31-Oct-22	…...........
20		0
21	30-Nov-22	…...........
22		0
23	31-Dec-22	…...........
24		0
25	Progressive average	3.77777778
Average

Cell Formulas
Range	Formula
B25	B25	=AVERAGE(B1:INDEX(B1:B24,MONTH(TODAY())*2))

Thank you for the quick answer. I tried to adjust your formula, but my budget has a more complex structure and the final results are not the right ones. Giving the monthly intermediate results formula takes them into account too, resulting some wrong values. I attach a sample of two columns that respects the original structure of my table, hoping them to be useful in modify the initial formula.

Book3xlsx.xlsx
	A	B	C
1	Period
2		Internet / TV	Phone
3	January
35	Total	110.60	74.02
36
37	February
67	Total	122.20	113.49
68
69	March
101	Total	0.00	0.00
102
103	April
134	Total	88.60	72.00
135
136	May
168	Total	63.58	38.00
169
170	June
201	Total	61.06	19.00
202
203	July
235	Tota	0.00	0.00
236
237	August
269	Total	75.00	74.00
270
271	September
302	Total	89.00	14.00
303
304	October
336	Total	0.00	0.00
337
338	November
369	Total	0.00	0.00
370
371	December
403	Total	0.00	0.00
404
405	Annual expenses	610.04	404.51
406	Medium expenses / month	50.84	33.71
407	Progressive expenses / month	55.30	29.50
408		67.78	44.95
Sheet1

Cell Formulas
Range	Formula
B35:C35,B403:C403,B369:C369,B336:C336,B269:C269,B235:C235,B168:C168,B101:C101	B35	=SUM(B4:B34)
B67:C67	B67	=SUM(B38:B66)
B134:C134,B302:C302,B201:C201	B134	=SUM(B104:B133)
B405:C405	B405	=B35+B67+B101+B134+B168+B201+B235+B269+B302+B336+B369+B403
B406:C406	B406	=B405/12
B407:C407	B407	=AVERAGE(B1:INDEX(B1:B404,MONTH(TODAY())*2))
B408:C408	B408	=(B35+B67+B101+B134+B168+B201+B235+B269+B302)/9

 

[vladimiratanasiu]

Is this what you mean?

22 09 25.xlsm
	A	B
1	31-Jan-22	..............
2		1
3	28-Feb-22	…...........
4		2
5	31-Mar-22	…...........
6		0
7	30-Apr-22	…...........
8		4
9	31-May-22	…...........
10		5
11	30-Jun-22	…...........
12		6
13	31-Jul-22	…...........
14		7
15	31-Aug-22	…...........
16		0
17	30-Sep-22	…...........
18		9
19	31-Oct-22	…...........
20		0
21	30-Nov-22	…...........
22		0
23	31-Dec-22	…...........
24		0
25	Progressive average	3.77777778
Average

Cell Formulas
Range	Formula
B25	B25	=AVERAGE(B1:INDEX(B1:B24,MONTH(TODAY())*2))

If it's useful for you, I attach also a link with my last table, keeping its original size. Progressive average.xlsx

 

[Peter_SSs]

I attach a sample of two columns that respects the original structure of my table,

Hmm, that is nothing at all like what you presented initially! No wonder the suggestion did not work.

The first thing that you need to do is to clean up and standardise your data. In column A you have several 'Total' cells that contain "Total", several that contain "Total " (trailing space) and one that contains "Tota"

Once that is done, you could try this formula in B407

=AVERAGEIFS(B3:INDEX(B:B,AGGREGATE(15,6,ROW($A1:$A404)/($A1:$A404="Total"),MONTH(TODAY()))),$A3:INDEX($A:$A,AGGREGATE(15,6,ROW($A1:$A404)/($A1:$A404="Total"),MONTH(TODAY()))),"Total")

 

[vladimiratanasiu]

=AVERAGEIFS(B3:INDEX(B:B,AGGREGATE(15,6,ROW($A1:$A404)/($A1:$A404="Total"),MONTH(TODAY()))),$A3:INDEX($A:$A,AGGREGATE(15,6,ROW($A1:$A404)/($A1:$A404="Total"),MONTH(TODAY()))),"Total")

I've made all necessary corrections, and your formula gives exactly the results I need for! Thank you very much

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.