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") ?

what formula did you use

"Seb" wrote:

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") ?

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

"bj" wrote:

what formula did you use

"Seb" wrote:

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") ?

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" wrote in ... =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") ?

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" 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 like 11'12"--David Biddulph"Seb" wrote in ... =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") ?

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(CONVE RT(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" wrote in ... 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" wrote ...=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") ?

That will do, thank you very much!

"David Biddulph" wrote:

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(CONVE RT(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" wrote in ... 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" wrote ...=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") ?

=INT(CONVERT(D6,"m","ft"))&"'"&ROUNDDOWN(MOD(CONVE RT(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