Converting Metric to Imperial

[Seb]

I have converted metric into imperial using the convert function which gives
me the result I want, however Excel doesn't write it in this format 11'3"
but rather as 11,3

Is there any way that Excel can show the proper way (11'3") ?

 

[bj]

what formula did you use

 

[Seb]

=CONVERT(D6,"m","ft")

what formula did you use

 

[David Biddulph]

The answer is not in feet and inches, but in feet with decimals.
3.429m gives 11.25m, which is 11ft 3in.

You could try something like
=INT(MROUND(CONVERT(D6,"m","ft"),1/12))&"'"&MOD(MROUND(CONVERT(D6,"m","ft"),1/12),1)*12&""""The complication is to avoid getting things like 11'12"--David Biddulph"Seb" <[email protected]> wrote in message=CONVERT(D6,"m","ft")>> "bj" wrote:>>> what formula did you use>>>> "Seb" wrote:>>>> > I have converted metric into imperial using the convert function whichgives>> > me the result I want, however Excel doesn't write it in this format11'3">> > but rather as 11,3>> >>> > Is there any way that Excel can show the proper way (11'3") ?

 

[Seb]

Thanks David, that works great, however with for example 3.08 m the result is
10'0.999999999999986". Is there any way to avoid this or to have it round up
as well?
Thanks

 

[David Biddulph]

Ah, yes, I should have guessed that rounding errors would creep in.

=INT(MROUND(CONVERT(D6,"m","ft"),1/12))&"'"&ROUND(MOD(MROUND(CONVERT(D6,"m","ft"),1/12),1)*12,0)&""""
will cope with your example, but I fear that at some stage it will fall foul
of another rounding hiccup.

=INT(CONVERT(D6,"m","ft"))&"'"&ROUNDDOWN(MOD(CONVERT(D6,"m","ft"),1)*12,0)&""""
will round the inches down.

Another option which might be more robust might be
=INT(ROUND(CONVERT(D6,"m","in"),0)/12)&"'"&(MOD(ROUND(CONVERT(D6,"m","in"),0),12)&"""")--David Biddulph"Seb" <[email protected]> wrote in messageThanks David, that works great, however with for example 3.08 m the resultis> 10'0.999999999999986". Is there any way to avoid this or to have it roundup> as well?> Thanks>>> "David Biddulph" wrote:>>> The answer is not in feet and inches, but in feet with decimals.>> 3.429m gives 11.25m, which is 11ft 3in.>>>> You could try something like>>=INT(MROUND(CONVERT(D6,"m","ft"),1/12))&"'"&MOD(MROUND(CONVERT(D6,"m","ft"),1/12),1)*12&""""The complication is to avoid getting things like11'12"--David Biddulph"Seb" <[email protected]> wrote inmessage"bj" wrote:>>> what formula did you use>>>> "Seb"wrote:>>>> > I have converted metric into imperial using the convertfunction whichgives>> > me the result I want, however Excel doesn't write itin this format11'3">> > but rather as 11,3>> >>> > Is there any way thatExcel can show the proper way (11'3") ?>>>>

 

[Seb]

That will do, thank you very much!

Ah, yes, I should have guessed that rounding errors would creep in.

=INT(MROUND(CONVERT(D6,"m","ft"),1/12))&"'"&ROUND(MOD(MROUND(CONVERT(D6,"m","ft"),1/12),1)*12,0)&""""
will cope with your example, but I fear that at some stage it will fall foul
of another rounding hiccup.

=INT(CONVERT(D6,"m","ft"))&"'"&ROUNDDOWN(MOD(CONVERT(D6,"m","ft"),1)*12,0)&""""
will round the inches down.

Another option which might be more robust might be
=INT(ROUND(CONVERT(D6,"m","in"),0)/12)&"'"&(MOD(ROUND(CONVERT(D6,"m","in"),0),12)&"""")--David Biddulph"Seb" <[email protected]> wrote in messagenews:[email protected]...> Thanks David, that works great, however with for example 3.08 m the resultis> 10'0.999999999999986". Is there any way to avoid this or to have it roundup> as well?> Thanks>>> "David Biddulph" wrote:>>> The answer is not in feet and inches, but in feet with decimals.>> 3.429m gives 11.25m, which is 11ft 3in.>>>> You could try something like>>=INT(MROUND(CONVERT(D6,"m","ft"),1/12))&"'"&MOD(MROUND(CONVERT(D6,"m","ft"),1/12),1)*12&""""The complication is to avoid getting things like11'12"--David Biddulph"Seb" <[email protected]> wrote inmessagenews[email protected]...>=CONVERT(D6,"m","ft")>> "bj" wrote:>>> what formula did you use>>>> "Seb"wrote:>>>> > I have converted metric into imperial using the convertfunction

whichgives>> > me the result I want, however Excel doesn't write itin this format11'3">> > but rather as 11,3>> >>> > Is there any way thatExcel can show the proper way (11'3") ?>>>>

 

[Gord Dibben]

Thanks for posting to the news group.

I never would have come up with this solution.


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

=INT(CONVERT(D6,"m","ft"))&"'"&ROUNDDOWN(MOD(CONVERT(D6,"m","ft"),1)*12,0)&""""

will round the inches down.

If rounding down is acceptable, here is a (far more cryptic to decipher)
formula that is 12 characters shorter than yours (well, actually 13
characters shorter if you remove the "neatening up" space that I placed
between the feet and inches sections)...

=TEXT(CONVERT(D6,"m","ft")-MOD(CONVERT(D6,"m","ft"),1)/2,"d' h\""")

Rick