Calculate age average from age in month day format

问题:

I have ages in month.day format like this:

35,24
36,11
36,19
37,18
35,12
37,04
35,20
36,01
35,26
36,05
36,16
37,28

for example for the first entry, the age is 35 months and 24 days. How can I average them all to get a proper age average in the same format?

Thanks

回答1:

If it can be reasonably assumed that a month is equal to 30 days then the following will return an average in the form of mm,dd.

=INT((SUMPRODUCT(LEFT(A2:A13, FIND(",", A2:A13)-1)*30+MID(A2:A13, FIND(",", A2:A13)+1, 99))/ROWS(A2:A13))/30)&
  ","&
  INT(MOD((SUMPRODUCT(LEFT(A2:A13, FIND(",", A2:A13)-1)*30+MID(A2:A13, FIND(",", A2:A13)+1, 99))/ROWS(A2:A13)),30))

        

回答2:

Here is a rough approximation:

My formula in B14:

=ROUNDDOWN((SUM(B2:B13)*30.5+SUM(C2:C13))/(ROW(B14)-2)/30.5, 0)

• Sum of the months times 30.5 (rough avg days in month)
• Add the sum of the days
• Divide by the entries (made this flexible for flexible amount of entries)
• Divide out by days to get months average and rounddown

My formula in C14:

=ROUNDDOWN((SUM(B2:B13)*30.5+SUM(C2:C13))/(ROW(B14)-2)-(B14*30.5), 0)

• Sum of months and days again to get total days
• Divide by entries to get average days
• Subtract out days already accounted for in months (B14)
• Rounddown

回答3:

Upon doing the question on STDDEV I found I just need to change to average to get the answer for this:

=QUOTIENT(AVERAGE(LEFT(A1:A12,2)*30 + RIGHT(A1:A12,2)),30)&","&ROUND(MOD(AVERAGE(LEFT(A1:A12,2)*30 + RIGHT(A1:A12,2)),30),2)

It is an array formula so it needs to be confirmed with Ctrl-Shift-Enter. You can change the rounding on the days to what every you want by changing the last number in the formula.