Forum Discussion
Calculate average between multiple timeframes
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
As variant for HansVogelaar sample
=INT( AVERAGE( TODAY() - A2:A25)/365.25 ) & " yr, " & ROUNDDOWN( MOD( AVERAGE( TODAY() - A2:A25)/365.25, 1 )*12, 0 ) & " mo"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
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)- I used the DATEDIF function. I understand it would be easier with real numbers which is why I am asking if anyone knows another way.
Do you have start dates and end dates? Or did you uses start dates and TODAY()?
