Averaging

[willquill]

I have a column of approx 350 entries. Generally the data is as follows

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}td {padding:0px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl24 {color:#333333; font-size:9.0pt; font-family:"Courier New", monospace; mso-font-charset:0; text-align:left; vertical-align:middle; border:1.0pt solid black;}ruby {ruby-align:left;}rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:"Times New Roman", serif; mso-font-charset:0; mso-char-type:none; display:none;}--></style>

28 years 9 months 30 days

<colgroup><col width="178"> </colgroup><tbody>
<!--StartFragment-->
<!--EndFragment-->
</tbody>

The formatting is general, is there a way to average the column?

Thank you,

willquill

 

[Joe4]

The formatting is general, is there a way to average the column?

That tells me that the data in that column is entered as Text, so your answer would be no (you cannot average text, only numbers).
So I think you would need to first convert those entries to numbers if you wanted to average them.

 

[Herodotus07]

To the best of my knowledge, the format "28 years 9 months 30 days" is not a date format that Excel will recognize and average. Your best bet is to convert the time periods into one unit of measure, i.e. days, and average the values that way.

If you want it in your originally stated format, you could always use this beast... where E6 is the average value in days

=ROUNDDOWN(E6/365,0)&" years "&ROUNDDOWN(12*(E6/365-ROUNDDOWN(E6/365,0)),0)&" months "&ROUND(((12*(E6/365-ROUNDDOWN(E6/365,0))-ROUNDDOWN(12*(E6/365-ROUNDDOWN(E6/365,0)),0))*30),0) & " days"

 

[FormR]

Here is another possible option that can be tried:

Excel 2013/2016
	A	B	C	D	E	F	G
1	10 years 9 months 15 days		Avg Days	5760	15 years	9 months	15 days
2	20 years 9 months 15 days
Sheet1

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(("0"&TRIM(MID(SUBSTITUTE(A1:A2," ",REPT(" ",99)),{1,3,5}*99-98,99)))*{365,30,1})/COUNTA(A1:A2)
E1	=INT(D1/365)&" years"
F1	=INT(MOD(D1,365)/30)&" months"
G1	=MOD(MOD(D1,365),30) &" days"

 

[barry houdini]

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}td {padding:0px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl24 {color:#333333; font-size:9.0pt; font-family:"Courier New", monospace; mso-font-charset:0; text-align:left; vertical-align:middle; border:1.0pt solid black;}ruby {ruby-align:left;}rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:"Times New Roman", serif; mso-font-charset:0; mso-char-type:none; display:none;}--></style>

28 years 9 months 30 days

<tbody>
</tbody>

Is this derived from a DATEDIF formula? It's easier to average the dates rather than trying to average text values (as previously advised) so if you have DOBS in A2:A350, for example and your DATEDIF is calculating age at today's date then for average age you can use just

=DATEDIF(AVERAGE(A2:A350),TODAY(),"y")&" years "&DATEDIF(AVERAGE(A2:A350),TODAY(),"ym")&" months "&DATEDIF(AVERAGE(A2:A350),TODAY(),"md")&" days"

If A2:A350 contains any blanks they will be ignored so you can make the range larger for future expansion purposes if you want

 

[barry houdini]

This formula actually averages using just the text values, assumes no blanks in the range

=TEXTJOIN(" ",1,DATEDIF(0,AVERAGE(MMULT(MID(0&B2:B350,FIND({"y","m","d"},0&B2:B350)-3,2)+0,{365.25;30.5;1})),{"y","ym","md"})&{" years"," months"," days"})

This uses TEXTJOIN function which is only available if you have Office 365 subscription I think

Without TEXTJOIN you can do essentially the same but DATEDIF is repeated 3 times like this

=DATEDIF(0,AVERAGE(MMULT(MID(0&B2:B350,FIND({"y","m","d"},0&B2:B350)-3,2)+0,{365.25;30.5;1})),"y")&" years "&DATEDIF(0,AVERAGE(MMULT(MID(0&B2:B350,FIND({"y","m","d"},0&B2:B350)-3,2)+0,{365.25;30.5;1})),"ym")&" months "&DATEDIF(0,AVERAGE(MMULT(MID(0&B2:B350,FIND({"y","m","d"},0&B2:B350)-3,2)+0,{365.25;30.5;1})),"md")&" days"

 

[willquill]

Thank you everyone for your help. Tis will be tonights project.

willquill