Calculating Average Age in YY:MM

[ScubaSteveFF]

Can anyone advice me if it is possible to calculate an average after I have input some date to show individual ages as YY:MM?

I have a number of lists with ages input manually as YY:MM but I need to calculate an average of each of these.

Can this be done?

 

[Stiuart_W]

Unless I'm being really thick and that wouldn't be a major surprise! But....

yy:mm is a DATE format. So 12:4 means 2012 April. It does NOT mean 12 years and 4 months (OLD)

But I would say to do what you want and assuming what you mean by yy:mm is Years & Months Old then average would be:

Sum of (i= 1 to n) (12 x yy(i) + mm(i))/(n*12)

Ie convert the age to months and then average those and then convert back to Years and Months

 

[Menumorut]

Hello,

You simply need to format cells to Month/Year, input data and use formula =average(a1:a10) *change the range with you range* and that's it.

If you already have data in as YY:MM there is a trick: use "text to columns" (on the data tab) -> delimited -> Other (and in the empty box set .
Then, in a empty column near user formula: =concatenate(b1,"/",a1) *considering that now you have month in column B and year in column A*, copy the cells that have this formula and paste over them but "as values".
Format cells to MM/YY then do the "text to columns" button again but this time uncheck all boxes. This will update the formatting.

Then you can use the average formula.

 

[footoo]

Presumably the ages (YY:MM) must be formatted as text. Here's a UDF :

Function AvAge(age As Range) As String
Dim cel As Range, arr As Variant, ttl#, c%
For Each cel In age
    If cel <> "" Then
        arr = Split(Replace(cel, ":", " "))
        ttl = ttl + arr(0) + arr(1) / 12
        c = c + 1
    End If
Next
AvAge = Int(ttl / c) & ":" & Round((ttl / c - Int(ttl / c)) * 12, 0)
End Function

Enter on the worksheet like : =AvAge(A1:A10)

The ages must all be entered as YY:MM or YY:M, otherwise the UDF will return an error.

If you prefer a non-vba solution that involves using a helper column, post again