Calculate dynamic average of monthly costs

[vladimiratanasiu]

Hello!

I have a situation with costs for different services. I need a dynamic formula to calculate the monthly average value, including automatically the new added months but ignoring the annual totals (see formulas from D31, D32, D33).

Thank you!

Book1
	A	B	C	D
1	Year	Month	Costs
2	2021	Jan-21	67.82
3		Feb-21	61.25
4		Mar-21	59.19
5		Apr-21	48.36
6		May-21	49.97
7		Jun-21	48.36
8		Jul-21	49.97
9		Aug-21	16.12
10		Sep-21	67.00
11		Oct-21	11.00
12		Nov-21	44.00
13		Dec-21	0.00
14		Total	523.04
15	2022	Jan-22	54.00
16		Feb-22	66.00
17		Mar-22	78.00
18		Apr-22	68.00
19		May-22	-10.00
20		Jun-22	0.00
21		Jul-22	0.00
22		Aug-22	49.00
23		Sep-22	27.00
24		Oct-22	51.00
25		Nov-22	0.00
26		Dec-22	0.00
27		Total	383.00
28	2023	Jan-23	0.00
29		Feb-23	0.00
30		Mar-23	106.00
31		Apr-23	96.00	39.57
32		May-23	46.00	39.79
33		Jun-23	188.00	44.73
34		Jul-23	0.00
35		Aug-23	0.00
36		Sep-23	0.00
37		Oct-23	0.00
38		Nov-23	0.00
39		Dec-23	0.00
40		Total	436.00
Sheet1

Cell Formulas
Range	Formula
D31	D31	=AVERAGE(C2:C13,C15:C26,C28:C31)
D32	D32	=AVERAGE(C2:C13,C15:C26,C28:C32)
D33	D33	=AVERAGE(C2:C13,C15:C26,C28:C33)

 

[Fluff]

How about in D31

=AVERAGEIFS(C:C,B:B,"<="&B31)

 

[vladimiratanasiu]

How about in D31

=AVERAGEIFS(C:C,B:B,"<="&B31)

Thank you for response! Your formula is ok, but requests periodical manual adjustments. However, I need it (if possible) to be a single one for all months and extend dynamically the target range, including automatically in calculations the new periods and their corresponding values added in the future. Thank you!

 

[Fluff]

I'm afraid I don't know what you mean. Just drag the formula down & it will give the same values you have shown.

 

[vladimiratanasiu]

I'm afraid I don't know what you mean. Just drag the formula down & it will give the same values you have shown.

I mean, in this case, it stops calculating the average at cell B31 (April 2023). But when I add new values for the next months (B32, B33 etc.), it doesn't include and process them automatically. I need it to run in this frame, working either in a merged range common for all months, or as a single value that moves down in line with the last value available.

Book1
	A	B	C	D
1	Year	Month	Costs
2	2021	Jan-21	67.82	39.57
3		Feb-21	61.25
4		Mar-21	59.19
5		Apr-21	48.36
6		May-21	49.97
7		Jun-21	48.36
8		Jul-21	49.97
9		Aug-21	16.12
10		Sep-21	67.00
11		Oct-21	11.00
12		Nov-21	44.00
13		Dec-21	0.00
14		Total	523.04
15	2022	Jan-22	54.00
16		Feb-22	66.00
17		Mar-22	78.00
18		Apr-22	68.00
19		May-22	-10.00
20		Jun-22	0.00
21		Jul-22	0.00
22		Aug-22	49.00
23		Sep-22	27.00
24		Oct-22	51.00
25		Nov-22	0.00
26		Dec-22	0.00
27		Total	383.00
28	2023	Jan-23	0.00
29		Feb-23	0.00
30		Mar-23	106.00
31		Apr-23	96.00
32		May-23	46.00
33		Jun-23	188.00
34		Jul-23	10.00
35		Aug-23	20.00
36		Sep-23	30.00
37		Oct-23	40.00
38		Nov-23	50.00
39		Dec-23	60.00
40		Total	436.00
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGEIFS(C:C,B:B,"<="&B31)

 

[Fluff]

If you only want the formula in the last used row then, you will need VBA.

 

[vladimiratanasiu]

If you only want the formula in the last used row then, you will need VBA.

Ok. However, is it possible in the case of a merged range (as the yellow one from my last table) to be a proper formula that meets the mentioned conditions? Thank you!

 

[Fluff]

Merged cells are an abomination & should be avoided like the plague.
Also you would need to change the size of the merged cells whenever you add more data, so I don't understand the point of doing that.

 

[vladimiratanasiu]

Merged cells are an abomination & should be avoided like the plague.
Also you would need to change the size of the merged cells whenever you add more data, so I don't understand the point of doing that.

Ok. In these conditions, could you / anyone else help me with a VBA code, to calculate a single dynamic average for all monthly costs, in line with the last value inserted every time in the table?