[ms office - 2010] A formula from hell :D

[localzuk]

So, I have been asked to turn a column of "age in months" into a a column with year/months format:

 

Eg. 126 becomes 11/6

 

So, I have come up with this rather beautifully ugly formula:

 

=ROUND(B3/12,0)&"/"&ROUND((MID(ROUND(B3/12,2),FIND(".",ROUND(B3/12,2),2),2))*12,0)

 

Can anyone do it more elegantly?

[mavhc]

Isn't 126months 10/6?

[localzuk]

Isn't 126months 10/6?

 

Yes. Oops. Rounding error. Need a floor rather than round.

[localzuk]

Corrected to =FLOOR.MATH(B3/12,1)&"/"&ROUND((MID(ROUND(B3/12,2),FIND(".",ROUND(B3/12,2),2),2))*12,0)

[mavhc]

If you don't mind the /12 being there you can just format the cell to # ?/12

 

Or do =INT(A11/12)&"/"&(A11-12*INT(A11/12))

Edited June 8, 2016 by mavhc

[localzuk]

If you don't mind the /12 being there you can just format the cell to # ?/12

 

That just gives me 126 again. Plus, the output has to be in the exact format mentioned (as it is imported into something else in that format, don't ask my why that format though).

[kearton]

Why don't you just use MOD and QUOTIENT to get the 2 numbers you need?

[localzuk]

That'd be because I'd not come across those 2 functions before... (Not a big Excel user).

[localzuk]

That's much nicer: =QUOTIENT(B3,12)&"/"&MOD(B3,12)

[kearton]

I learnt about Modulus and Quotients at High School

Albeit a long time (and distance from UK) ago...

 

There isn't a lot (mathematical) that Excel doesn't do...

Edited June 8, 2016 by kearton

[localzuk]

I learnt about Modulus and Quotients at High School

Albeit a long time (and distance from UK) ago...

 

There isn't a lot (mathematical) that Excel doesn't do...

 

They ring a bell to me now. Just I rarely use Excel.