• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Correct conversion of decimal inches to feet / inches with fractions

frisbeenut

New Member
I got the formula from the internet which will convert a given decimal number in feet / inches with fractions and it is somewhat useful.

=INT(MROUND(A1,1/16)/12) & "' -" & TEXT(MOD(MROUND(A1,1/16),12),"# #/##") & """"
(Note: currently configured for 1/16ths)

37.25 will be converted to 3' -1 1/4"
Which is correct.

However, in my opinion it does not convert the number correctly when there are either zero inches or zero feet
37.25 will display as 3' -1/4", which is incorrect. It should be 3'-0 1/4"

Similarly 0.25 will display 0' -1/4". It should be simply 1/4"
Lastly, 6.25 will display as 0'-6 1/4". This might be acceptable but there are times it is better to convert to 6 1/4".

I think it would be best to use a custom VBA function to handle this conversion. It would also be great if the function accepted two parameters, the first to be the number to convert and the second being the fractional accuracy desired, 2, 4, 8, 16 and so on, or a custom accuracy, like thirds. (Maybe two additional T / F parameters to control zero feet and zero inches suppression, but that is not totally necessary, but would be great for all users.)

Any help will be appreciated.

Thanks
 

Hui

Excel Ninja
Staff member
try the following:
=TEXT(INT(MROUND(A1,1/16)/12),"0' -;;;") & TEXT(MOD(MROUND(A1,1/16),12),"[>0.999]# #/##;[<0.999]0 #/##") & """"
 

frisbeenut

New Member
try the following:
=TEXT(INT(MROUND(A1,1/16)/12),"0' -;;;") & TEXT(MOD(MROUND(A1,1/16),12),"[>0.999]# #/##;[<0.999]0 #/##") & """"
Thank you for the formula. I hope someone can do a function in VBA as well.

For full formula functionality, is it possible to have it work with negative numbers. I would be best if it did not need an if statement.
 

Hui

Excel Ninja
Staff member
=IF(A1<0,"-","")&TEXT(INT(MROUND(ABS(A1),1/16)/12),"0' -;;;") & TEXT(MOD(MROUND(ABS(A1),1/16),12),"[>0.999]# #/##;[<0.999]0 #/##") & """"
 

frisbeenut

New Member
Thanks, both those formulas do work fairly well. Sadly, like many interactions with computer programs and people, my CAD program uses a different formats for input. it requires the format as follows

3'1-1/2" not 3'-1 1/2"
Having it in this format might be useful as well.
 

frisbeenut

New Member
Is anyone able to create a VBA function that will do the same as

=IF(I28<0,"-","")&TEXT(INT(MROUND(ABS(I28),1/FA)/12),"0';;;") & TEXT(MOD(MROUND(ABS(I28),1/FA),12),"[>0.999]#-#/##;[<0.999]0-#/##") & """"

Where the first parameter is the value to be converted and the second parameter being fractional accuracy, FA.
Thanks
 
Last edited:

Marc L

Excel Ninja
Just try Evaluate method to use a formula, to see in VBA inner help and in threads within this forum …​
 

Hui

Excel Ninja
Staff member
=IF(A1<0,"-","")&TEXT(INT(MROUND(ABS(A1),1/16)/12),"0' ;;;") & TEXT(MOD(MROUND(ABS(A1),1/16),12),"[>0.999]# #/##;[<0.999]0 #/##") & """"
 

frisbeenut

New Member
I am hoping for additional help for a formula to convert
decimal cubic feet to fractional cubic yards (84.51 to 3-1/8 c.y.)

and similarly convert decimal cubic yards to fractional cubic yards (3.13 to 3-1/8 c.y.)
with the ability to control fractional accuracy
thanks
 

frisbeenut

New Member
This will convert cf to cy and display it in fractions
=IF(A1<0,"-","")& TEXT(MROUND(ABS(A1/27),1/FA),"[>0.999]#-#/##;[<0.999]0-#/##") & " c.y."

And this is useful too
=IF(A1<0,"-","")& TEXT(MROUND(ABS(A1),1/FA),"[>0.999]#-#/##;[<0.999]0-#/##") & """"

Where fractional accuracy FA is a defined name

I hope it can be useful to others
 
Top