converting decimal to feet - inches (fractional) ?????

[jtnawroc]

This is a continuance of the Q and A below. Except the answer below converted a decimal like 1.44 to and answer like 1' 5.28".
except the final format I am after requires the 5.28" to be fractional such as:
1' - 5 1/4"

Is there a formula for this format?

below is the link to the previous Q and A which I found but I cant seem to augment the formula correctly. Much thanks.

http://www.mrexcel.com/forum/showthread.php?t=72114

 

[schielrn]

Welcome to the Board!

Maybe try:

=ROUNDDOWN(A1,0)&"' - "&TEXT(MOD(A1,1)*12,"# ##/##")&""""

But isn't your answer really 1' - 5 7/25" or do you always want it in fourths? If that is the case, then you want:

=ROUNDDOWN(A1,0)&"' - "&TEXT(MOD(A1,1)*12,"# #/4")&""""

Hope that helps and post back if you ware looking for something different.

 

[jtnawroc]

well I'm after the decimal to be broken down first to whole feet then the to whole inches then to either 4ths or 8ths, ie. 2", 2 1/8", 2 1/4", 2 3/8", 2 1/2", 2 5/8", 2 3/4", 2 7/8", 3"
ty

 

[schielrn]

I think this will work, but don't really have much to test against, can you post some decimals and expected formulas to test maybe?

=IF(MOD(ROUND(MOD(MOD(A2,1)*12,1),2)*100/8,1)=0,ROUNDDOWN(A2,0)&"' - "&TEXT(MOD(A2,1)*12,"# #/8")&"""",ROUNDDOWN(A2,0)&"' - "&TEXT(MOD(A2,1)*12,"# #/4")&"""")

Hope that helps.

 

[barry houdini]

To round to the nearest eighth of an inch try this formula

=INT(ROUND(A1*96,0)/96)&"' - "&TEXT(MOD(ROUND(A1*96,0)/96,1)*12,"0 #/#\""")

 

[jtnawroc]

There seems to be some rounding errors. What if after the first foot integer was pulled out I could then use a if:then table for the remainder decimal? would that work?
I could import a table from .00 through .99 with cooresponding answers.

 

[barry houdini]

There seems to be some rounding errors.

Did you try the formula I suggested? Can you detail some sample errors, if any?

 

[jtnawroc]

no. Thats it!!! Thanks a million!!

 

[arkusM]

Did you try the formula I suggested? Can you detail some sample errors, if any?

I tested and it seems to work.
I used an conversion from mm to ft (=CONVERT(C12,"mm","ft")) C12 = 5515(mm)

to result was 18.09383202 ft, Barry your formula gave me 18' - 1 1/8"
I up'ed the mm count by 3mm ~ 1/8" and it seemed to up by 1/8" intervals.

I did not test decimals of mm because rounding to the nearest 1/8" sorta makes that level of precision in mm's... well too precise?

Thanks for this, as a hobbiest woodworker I will be using this for sure!!

 

[beezy]

To round to the nearest eighth of an inch try this formula

=INT(ROUND(A1*96,0)/96)&"' - "&TEXT(MOD(ROUND(A1*96,0)/96,1)*12,"0 #/#\""")

How can I modify this code to round to the nearest 1/4, 2/4(1/2) or 3/4