Calculating average of ages (written as datedif)

[balena]

Hi, I have all the ages written as differences between two dates (the date of an event and their birthday) and I would like to calculate their average, but I'm not sure if that's possible with the words "years" and "months" written into the formula. I'm completely new to Excel and would really appreciate the help. Thank you in advance!

 

[Georgiboy]

Welcome to the forum, perhaps the below will help:

Book1
	A																				U	V	W	X	Y
2	12/08/1983																				17/04/2023	39	8		39 years, 8 months
3	13/08/1983																				18/04/2023	39	8		39 years, 8 months
4	14/08/1980																				19/04/2023	42	8		42 years, 8 months
5	15/08/1983																				20/04/2023	39	8		39 years, 8 months
6	16/04/1983																				21/04/2023	40	0		40 years, 0 months
7	17/08/1976																				22/04/2023	46	8		46 years, 8 months
8	18/08/1983																				23/04/2023	39	8		39 years, 8 months
9	19/08/1942																				24/04/2023	80	8		80 years, 8 months
10	20/08/1983																				25/04/2023	39	8		39 years, 8 months
11	21/08/1983																				26/04/2023	39	8		39 years, 8 months
12	Average																					44.2	7.2		44 years, 7 months
Sheet1

Cell Formulas
Range	Formula
V2:V11	V2	=DATEDIF(A2:A11,U2:U11,"y")
W2:W11	W2	=DATEDIF(A2:A11,U2:U11,"ym")
Y2:Y12	Y2	=LET(y,DATEDIF(A2:A11,U2:U11,"y"),m,DATEDIF(A2:A11,U2:U11,"ym"),VSTACK(y&" years, " & m& " months",ROUND(AVERAGE(y),0) & " years, "& ROUND(AVERAGE(m),0)&" months"))
V12:W12	V12	=AVERAGE(V2#)
Dynamic array formulas.

 

[balena]

Welcome to the forum, perhaps the below will help:

Book1
	A																				U	V	W	X	Y
2	12/08/1983																				17/04/2023	39	8		39 years, 8 months
3	13/08/1983																				18/04/2023	39	8		39 years, 8 months
4	14/08/1980																				19/04/2023	42	8		42 years, 8 months
5	15/08/1983																				20/04/2023	39	8		39 years, 8 months
6	16/04/1983																				21/04/2023	40	0		40 years, 0 months
7	17/08/1976																				22/04/2023	46	8		46 years, 8 months
8	18/08/1983																				23/04/2023	39	8		39 years, 8 months
9	19/08/1942																				24/04/2023	80	8		80 years, 8 months
10	20/08/1983																				25/04/2023	39	8		39 years, 8 months
11	21/08/1983																				26/04/2023	39	8		39 years, 8 months
12	Average																					44.2	7.2		44 years, 7 months
Sheet1

Cell Formulas
Range	Formula
V2:V11	V2	=DATEDIF(A2:A11,U2:U11,"y")
W2:W11	W2	=DATEDIF(A2:A11,U2:U11,"ym")
Y2:Y12	Y2	=LET(y,DATEDIF(A2:A11,U2:U11,"y"),m,DATEDIF(A2:A11,U2:U11,"ym"),VSTACK(y&" years, " & m& " months",ROUND(AVERAGE(y),0) & " years, "& ROUND(AVERAGE(m),0)&" months"))
V12:W12	V12	=AVERAGE(V2#)
Dynamic array formulas.

Thank you so much for your help!

 

[Alex Blakenburg]

As far as I can tell this works too.

20230417 Average Age Calc balena.xlsx
	E	F	G	H														V	W	X	Y	Z	AA
1	Start Date																				End Date		Overall average
2	12/08/1983																				17/04/2023		44 Years, 9 Months
3	13/08/1983																				18/04/2023
4	14/08/1980																				19/04/2023
5	15/08/1983																				20/04/2023
6	16/04/1983																				21/04/2023
7	17/08/1976																				22/04/2023
8	18/08/1983																				23/04/2023
9	19/08/1942																				24/04/2023
10	20/08/1983																				25/04/2023
11	21/08/1983																				26/04/2023
My Test (2)

Cell Formulas
Range	Formula
AA2	AA2	=LET(avgMths,AVERAGE(((YEAR(Y2:Y11)-YEAR(E2:E11))*12+(MONTH(Y2:Y11)-MONTH(E2:E11)))), Yrs, INT(avgMths/12), Mths,INT(MOD(avgMths/12,1)*12), Yrs & " Years, " & Mths & " Months")

 

[balena]

As far as I can tell this works too.

20230417 Average Age Calc balena.xlsx
	E	F	G	H														V	W	X	Y	Z	AA
1	Start Date																				End Date		Overall average
2	12/08/1983																				17/04/2023		44 Years, 9 Months
3	13/08/1983																				18/04/2023
4	14/08/1980																				19/04/2023
5	15/08/1983																				20/04/2023
6	16/04/1983																				21/04/2023
7	17/08/1976																				22/04/2023
8	18/08/1983																				23/04/2023
9	19/08/1942																				24/04/2023
10	20/08/1983																				25/04/2023
11	21/08/1983																				26/04/2023
My Test (2)

Cell Formulas
Range	Formula
AA2	AA2	=LET(avgMths,AVERAGE(((YEAR(Y2:Y11)-YEAR(E2:E11))*12+(MONTH(Y2:Y11)-MONTH(E2:E11)))), Yrs, INT(avgMths/12), Mths,INT(MOD(avgMths/12,1)*12), Yrs & " Years, " & Mths & " Months")

Thank you!!!

 

[Alex Blakenburg]

You're welcome.