Forum Discussion
Removing decimals in a MOD formula
I am trying to create a formula which returns the average length of time in years and months from a data set.
- In column Z of my dataset I have lengths of time in months.
- In a new cell on a separate tab I have the average of these lengths of time (pulled by the =AVERAGE formula), again in months
- In the cell next to this one I have a formula to convert this average length of time (in months) into years and months: =INT(B6/12) & " years and " &MOD(B6,12)& " months"
My problem is, this final formula is coming back with multiple decimal places for the months portion (see below.) Is there any way I can get it to pull back the months portion with no decimals, just as it does for the years portion?
LL19911695 Like this perhaps?
=INT(B6/12) & " years and " & TEXT(MOD(B6,12),"0") & " months"
Alternatively:
=INT(B6/12) & " years and " & ROUND(MOD(B6,12),0) & " months"
3 Replies
LL19911695 Like this perhaps?
=INT(B6/12) & " years and " & TEXT(MOD(B6,12),"0") & " months"
Alternatively:
=INT(B6/12) & " years and " & ROUND(MOD(B6,12),0) & " months"
- That worked, thank you 🙂
LL19911695 Obviously, you can also wrap the MOD function in INT, similar to the year portion. Though, that would result in 9.
