Aug 10 2022 09:28 PM
I have the following timeframes I calculated. I am trying to now calculate the average between all the dates and I cannot figure out the formula. Can anyone assist?
3 yr, 8 mo
5 yr, 1 mo
1 yr, 8 mo
8 yr, 1 mo
4 yr, 1 mo
8 yr, 9 mo
4 yr, 2 mo
0 yr, 9 mo
6 yr, 8 mo
9 yr, 5 mo
3 yr, 0 mo
0 yr, 0 mo
1 yr, 10 mo
4 yr, 2 mo
11 yr, 11 mo
Aug 10 2022 10:02 PM
@usernam123 How did you calculate these "timeframes"? If you could work with real numbers, it would be a lot easier. For instance, you could calculate 3.66667 in one cell and make it look like "3 yr, 8 mo" in another. Something like this:
The formula in C3 (and copied down) is:
=INT(B3)&" yr, "&TEXT(MOD(B3,1)*12,"0")&" mo"
B7 contains:
=AVERAGE(B3:B5)
Aug 11 2022 08:33 AM
Aug 11 2022 08:50 AM
Do you have start dates and end dates? Or did you uses start dates and TODAY()?
Aug 11 2022 08:51 AM
Aug 11 2022 08:55 AM
As a comment, if dates are named range ages,
formula for average could be
=AVERAGE( LEFT(ages, FIND(" yr", ages) ) + SUBSTITUTE( RIGHT(ages, LEN(ages) - FIND(", ", ages) - 1 ), " mo", "")/12 )
transform to text with yr and mo as @Riny_van_Eekelen suggested
Aug 11 2022 09:04 AM
See the attached sample workbook for a way to calculate the average.
Aug 11 2022 09:12 AM
As variant for @Hans Vogelaar sample
=INT( AVERAGE( TODAY() - A2:A25)/365.25 ) & " yr, " & ROUNDDOWN( MOD( AVERAGE( TODAY() - A2:A25)/365.25, 1 )*12, 0 ) & " mo"