Formula to calculate automatically progressive average

[vladimiratanasiu]

Hello!

I have a situation with monthly costs. I need a formula to calculate and show automatically the average of all costs at some point (as it's shown in the attached table), each time a new value is inserted and excluding the annual totals. Thank you!

Book1.xlsx
	A	B	C	D
1	Year	Month	Cost	Medium cost
2	2017	04/01/2017	34.83	34.83
3		5/1/2017	72	53.42
4		06/01/2017	74.77	60.53
5		07/01/2017	77.74	64.84
6		08/01/2017	76.5	67.17
7		09/01/2017	67.74	67.26
8		10/01/2017	130.11	76.24
9		11/01/2017	183.44	89.64
10		12/01/2017	189.56	100.74
11		Total	906.69
12	2018	01/01/2018	189.56	109.63
13		02/01/2018	169.62	115.08
14		03/01/2018	177.41	120.27
15		04/01/2018	128.08	120.87
16		05/01/2018	132.35	121.69
17		06/01/2018	128.08	122.12
18		07/01/2018	132.35	122.76
19		08/01/2018	132.35	123.32
20		09/01/2018	113.2	122.76
21		10/01/2018	78.29	120.42
22		11/01/2018	75.77	118.19
23		12/01/2018	78.29	116.29
24		Total	1535.35
25	2019	01/01/2019	78.29	114.56
26		02/01/2019	70.72	112.65
27		03/01/2019	71.48	110.94
28		04/01/2019	64.42	109.08
29		05/01/2019	66.57	107.44
30		06/01/2019	64.42	105.85
31		07/01/2019
32		08/01/2019
33		09/01/2019
34		10/01/2019
35		11/01/2019
36		12/01/2019
37		Total	415.9
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGE(C2)
D3:D10	D3	=AVERAGE($C$2:C3)
C11	C11	=SUM(C2:C10)
D12	D12	=AVERAGE($C$2:$C$10,$C$12)
D13:D23	D13	=AVERAGE($C$2:$C$10,$C$12:C13)
C24,C37	C24	=SUM(C12:C23)
D25	D25	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25)
D26:D30	D26	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25:C26)

 

[etaf]

=IF(B2="total","",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))

Book1
	A	B	C	D	E	F
1	Year	Month	Cost	Medium cost
2	2017	42739	34.83	34.83		34.83
3		1/5/17	72	53.415		53.415
4		42741	74.77	60.53333333		60.5333333
5		42742	77.74	64.835		64.835
6		42743	76.5	67.168		67.168
7		42744	67.74	67.26333333		67.2633333
8		42745	130.11	76.24142857		76.2414286
9		42746	183.44	89.64125		89.64125
10		42747	189.56	100.7433333		100.743333
11		Total	906.69
12	2018	43101	189.56	109.625		109.625
13		43102	169.62	115.0790909		115.079091
14		43103	177.41	120.2733333		120.273333
15		43104	128.08	120.8738462		120.873846
16		43105	132.35	121.6935714		121.693571
17		43106	128.08	122.1193333		122.119333
18		43107	132.35	122.75875		122.75875
19		43108	132.35	123.3229412		123.322941
20		43109	113.2	122.7605556		122.760556
21		43110	78.29	120.42		120.42
22		43111	75.77	118.1875		118.1875
23		43112	78.29	116.287619		116.287619
24		Total	1535.35
25	2019	43466	78.29	114.5604545		114.560455
26		43467	70.72	112.6543478		112.654348
27		43468	71.48	110.93875		110.93875
28		43469	64.42	109.078		109.078
29		43470	66.57	107.4430769		107.443077
30		43471	64.42	105.8496296		105.84963
31		43472	66.57	104.4467857		104.446786
32		43473	66.57	103.1406897		103.14069
33		43474	66.29	101.9123333		101.912333
34		43475	69.79	100.876129		100.876129
35		43476	67.54	99.834375		99.834375
36		43477	69.79	98.92393939		98.9239394
37		Total	822.45
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGE(C2)
D3:D10	D3	=AVERAGE($C$2:C3)
C11	C11	=SUM(C2:C10)
D12	D12	=AVERAGE($C$2:$C$10,$C$12)
D13:D23	D13	=AVERAGE($C$2:$C$10,$C$12:C13)
C24,C37	C24	=SUM(C12:C23)
D25	D25	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25)
D26:D36	D26	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25:C26)
F2:F37	F2	=IF(B2="total","",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))

 

[vladimiratanasiu]

=IF(B2="total","",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))

Book1
	A	B	C	D	E	F
1	Year	Month	Cost	Medium cost
2	2017	42739	34.83	34.83		34.83
3		1/5/17	72	53.415		53.415
4		42741	74.77	60.53333333		60.5333333
5		42742	77.74	64.835		64.835
6		42743	76.5	67.168		67.168
7		42744	67.74	67.26333333		67.2633333
8		42745	130.11	76.24142857		76.2414286
9		42746	183.44	89.64125		89.64125
10		42747	189.56	100.7433333		100.743333
11		Total	906.69
12	2018	43101	189.56	109.625		109.625
13		43102	169.62	115.0790909		115.079091
14		43103	177.41	120.2733333		120.273333
15		43104	128.08	120.8738462		120.873846
16		43105	132.35	121.6935714		121.693571
17		43106	128.08	122.1193333		122.119333
18		43107	132.35	122.75875		122.75875
19		43108	132.35	123.3229412		123.322941
20		43109	113.2	122.7605556		122.760556
21		43110	78.29	120.42		120.42
22		43111	75.77	118.1875		118.1875
23		43112	78.29	116.287619		116.287619
24		Total	1535.35
25	2019	43466	78.29	114.5604545		114.560455
26		43467	70.72	112.6543478		112.654348
27		43468	71.48	110.93875		110.93875
28		43469	64.42	109.078		109.078
29		43470	66.57	107.4430769		107.443077
30		43471	64.42	105.8496296		105.84963
31		43472	66.57	104.4467857		104.446786
32		43473	66.57	103.1406897		103.14069
33		43474	66.29	101.9123333		101.912333
34		43475	69.79	100.876129		100.876129
35		43476	67.54	99.834375		99.834375
36		43477	69.79	98.92393939		98.9239394
37		Total	822.45
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGE(C2)
D3:D10	D3	=AVERAGE($C$2:C3)
C11	C11	=SUM(C2:C10)
D12	D12	=AVERAGE($C$2:$C$10,$C$12)
D13:D23	D13	=AVERAGE($C$2:$C$10,$C$12:C13)
C24,C37	C24	=SUM(C12:C23)
D25	D25	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25)
D26:D36	D26	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25:C26)
F2:F37	F2	=IF(B2="total","",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))

Thank you for your prompt answer! Your formula is available only by adjusting it manually. However, it doesn't move down automatically, in line with other new values inserted. Could you adjust it, to consider these specific conditions? Thank you!

Book1.xlsx
	A	B	C	D	E
1	Year	Month	Cost	Medium cost
2	2017	04/01/2017	34.83	34.83	34.83
3		5/1/2017	72	53.42	53.415
4		06/01/2017	74.77	60.53	60.53333
5		07/01/2017	77.74	64.84	64.835
6		08/01/2017	76.5	67.17	67.168
7		09/01/2017	67.74	67.26	67.26333
8		10/01/2017	130.11	76.24	76.24143
9		11/01/2017	183.44	89.64	89.64125
10		12/01/2017	189.56	100.74	100.7433
11		Total	906.69
12	2018	01/01/2018	189.56	109.63	109.625
13		02/01/2018	169.62	115.08	115.0791
14		03/01/2018	177.41	120.27	120.2733
15		04/01/2018	128.08	120.87	120.8738
16		05/01/2018	132.35	121.69	121.6936
17		06/01/2018	128.08	122.12	122.1193
18		07/01/2018	132.35	122.76	122.7588
19		08/01/2018	132.35	123.32	123.3229
20		09/01/2018	113.2	122.76	122.7606
21		10/01/2018	78.29	120.42	120.42
22		11/01/2018	75.77	118.19	118.1875
23		12/01/2018	78.29	116.29	116.2876
24		Total	1535.35
25	2019	01/01/2019	78.29	114.56	114.5605
26		02/01/2019	70.72	112.65	112.6543
27		03/01/2019	71.48	110.94	110.9388
28		04/01/2019	64.42	109.08	109.078
29		05/01/2019	66.57	107.44	107.4431
30		06/01/2019	64.42	105.85	105.8496
31		07/01/2019	55	104.03
32		08/01/2019	44	101.96
33		09/01/2019
34		10/01/2019
35		11/01/2019
36		12/01/2019
37		Total	514.9
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AVERAGE(C2)
D3:D10	D3	=AVERAGE($C$2:C3)
C11	C11	=SUM(C2:C10)
D12	D12	=AVERAGE($C$2:$C$10,$C$12)
D13:D23	D13	=AVERAGE($C$2:$C$10,$C$12:C13)
C24,C37	C24	=SUM(C12:C23)
E37,E2:E30	E2	=IF(B2="total","",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))
D25	D25	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25)
D26:D32	D26	=AVERAGE($C$2:$C$10,$C$12:$C$23,$C$25:C26)

 

[etaf]

=IF(OR(B2="total",C2=""),"",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))
and copy down as far as you want

 

[vladimiratanasiu]

=IF(OR(B2="total",C2=""),"",AVERAGEIFS($C$2:C2,$B$2:B2,"<>"&"Total"))
and copy down as far as you want

Thank you! It meets properly the mentioned requirements.

 

[etaf]

you are welcome