Adding together and averaging a column of data in the format years, months and days

[jenniwren]

Could anyone help me with a formula for totalling and averaging a column of data which is in the format years, months and days. I have used the formula =YEAR(AT2)-YEAR(AB2)-IF(OR(MONTH(AT2)<MONTH(AB2),AND(MONTH(AT2)=MONTH(AB2),DAY(AT2)<DAY(AB2))),1,0)&" years, "&MONTH(AT2)-MONTH(AB2)+IF(AND(MONTH(AT2)
<=MONTH(AB2),DAY(AT2)<DAY(AB2)),11,IF(AND(MONTH(AT2)<MONTH(AB2),DAY(AT2)
>=DAY(AB2)),12,IF(AND(MONTH(AT2)>MONTH(AB2),DAY(AT2)<DAY(AB2)),-1)))&" months,
"&AT2-DATE(YEAR(AT2),MONTH(AT2)-IF(DAY(AT2)<DAY(AB2),1,0),DAY(AB2))&" "

to give me a value in years months and days - which is the time from treatment to last follow up.

I now want to average these values to give an average length of time to last follow up.

Could anyone give me any pointers - I have tried the usual sum all but I don't think it likes the date values

Thank you for your help and guidence

Jenny

[Tony Valko]

Can you post a few rows worth of data and tell us what result you expect?

[jenniwren]

Hi Tony

this is the type of data I have in the column

"4 years, 6 months, 8 "
"6 years, 6 months, 22 "
"6 years, 2 months, 10 "
"4 years, 8 months, 25 "
"2 years, 2 months, 28 "
"5 years, 2 months, 29 "
"5 years, 2 months, 2 "
"6 years, 3 months, 15 "

Expected average
40 years, 11 months, 10 days/8
roughly
5 years, 1 month, 12 days

I think its a bit tricky trying to average years, months and days

Do you think I might be better working out the follow up time in days and then averaging the days and converting that back into years?

Thank you again for your help

Best wishes

Jenny

[Tony Valko]

I have no idea how you are arriving at those averages!

I thought that if this was your data:

Data Range

	A
1	4 years, 6 months, 8
2	6 years, 6 months, 22
3	6 years, 2 months, 10
4	4 years, 8 months, 25
5	2 years, 2 months, 28
6	5 years, 2 months, 29
7	5 years, 2 months, 2
8	6 years, 3 months, 15
9

Then these would be the averages:

Data Range

	A	B	C	D	E
1	4 years, 6 months, 8		Years	Months	Days
2	6 years, 6 months, 22		4	6	8
3	6 years, 2 months, 10		6	6	22
4	4 years, 8 months, 25		6	2	10
5	2 years, 2 months, 28		4	8	25
6	5 years, 2 months, 29		2	2	28
7	5 years, 2 months, 2		5	2	29
8	6 years, 3 months, 15		5	2	2
9			6	3	15
10		Average	4.75	3.875	17.375