Inches to Feet-Inches 16

[dik]

Does anyone have a formula to convert inches to feet-inches, eg. 96 is converted to 8'-0"? If you do, can you cut and paste it?

thanks, Dik

 

[ivymike]

probably a consequence of using INT() instead of ROUND(), but I haven't taken the time to mess with it.

 

[jghrist]

Try

=IF(SIGN(A1)=-1,"- ","")&INT(ABS(A1)/12)&"'-"&INT(MOD(ABS(A1),12))&IF(FLOOR(ABS(A1)-INT(ABS(A1)),1/16)=0,""""," "&TEXT(FLOOR(ABS(A1)-INT(ABS(A1))+1/32,1/16),"?/??")&"""")

 

[yakpol]

I hope it's the last iteration:

=IF(SIGN(A1)=-1,"- ","")&IF(ROUNDUP(ABS(A1/12),0)-ABS(A1/12)<=1/12/32,ROUNDUP(ABS(A1/12),0) &"'-0""",INT(ABS(A1)/12)& "'-"&IF(ABS(A1)-FLOOR(ABS(A1),1/6)<CEILING(ABS(A1),1/16)-ABS(A1),TEXT(FLOOR(MOD(ABS(A1),12),1/16),"# #/##"),TEXT(CEILING(MOD(ABS(A1),12),1/16),"# #/##"))&"""")

should work for any number now!

 

[joerd]

Isn't it great to see how "bulletproofing" a formula makes it twice as long? Yakpol's first formula deserves a star for simplicity, and his last a star for comprehensiveness (and getting close to being unintelligible) ;-)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yakpol]

Takes awhile to get correct formula... previosly posted long version still was not bulletproof giving 1'-12" answer if 23 31/32 is in cell A1. No more relying on built-in excel formulas shortest answer is:

=IF(SIGN(A1)=-1,"- ","")&INT(ROUND(ABS(A1)*16,0)/16/12)&"'-"&TEXT((MOD(ROUND(ABS(A1)*16,0)/16,12)),"# #/##")&""""

 

[IFRs]

These are great formulas! SUper work and great thread!

Can you make one that does not break out the feet, but would yield 25 15/16" for instance?

 

[joerd]

That would be

=IF(SIGN(A1)=-1,"- ","")&TEXT((ROUND(ABS(A1)*16,0)/16),"# #/##")&""""

, if yakpol agrees.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yakpol]

Just formatting cell as a fraction would do, however you might get 2/16" instead of 1/8"... Joerd's formula would do as well without

= TEXT(ROUND(A1*16,0)/16,"# #/##")

 

[TampaBridgeDesign]

One more question for this topic........

If you use 72.25 as the number you're converting the result is 6'-1/4".

How would the formula be modified in order to have the result read 6'-0 1/4" ?

 

[yakpol]

Just a little modification changing '#' for '0' will do

=IF(SIGN(A1)=-1,"- ","")&INT(ROUND(ABS(A1)*16,0)/16/12)&"'-"&TEXT((MOD(ROUND(ABS(A1)*16,0)/16,12)),"0 #/##")&""""

 

[stuna]

You can also use Mathcad. It will convert your units automatically.

 

[jpbernier]

Hi folks-
I am a very novice Excel user. I have a project that requires input of values in 0'-0" format. I understand from your thread that you are converting various decimal and fractional values to architectural units... my goal is to enter architectural units, and have them respond to various formulas for quantities / lengths / volumes, etc. I know there are various Architectural Calculators that perform this quite easily, but can it be done in Excel? Many thanks in advance!
JP.

 

[jpbernier]

Here's an example of what I need.
One small part of what I'm doing is a parametric extraction of lumber required to build a flight of stairs. I have the whole thing worked out, except that I have to FIRST convert my units to decimal values, and then enter those. I can see that your formula's posted will turn them back into arch units, but if I could just enter my floor-to-floor value (8'-10 1/8") instead first converting, (106.625 inches) and then converting back, that would really rock!

 

[yakpol]

jpbernier,
The formula you require gets really nusty, but it shall work if you keep very strong format of input data (like 3'-0 1/2" ) . I still don't promise complete bulletproofness of it

=LEFT(A1,SEARCH("'",A1)-1)*12+MID(A1,SEARCH("-",A1,2)+1,LEN(A1)-SEARCH("-",A1,2)-1)

yakpol

 

[jpbernier]

Yakpol-
I appreciate your super-quick response! I've tried applying your formula, but I can't get it to do anything but generate #ref! and #value! errors. Maybe just a little tip on where to put it / how to apply it?
Sorry to sound so dumb, I know I totally suck at this.

 

[jghrist]

It would be simpler to enter feet in one cell and inches in another. Then put a formula in a third cell that multiplies the feet cell by 12 and adds it to the inches cell.

 

[yakpol]

I wonder why. Place the text you want to convert in cell A1 and formula in cell B1.

 

[jpbernier]

Yakpol, I appreciate your fine efforts, but I'm afraid that was much too complicated for my little primitive brain. However, jghrist... that was the simple solution I was looking for!! After doing little formatting to get the x' and x" I was on my way! Thank you so much for everyone's help!!
JP.

 

[bigjosh]

Here is a free, ready-made Excel add-in that will let you do calculations in feet and inches...

http://josh.com/InchCalc/index.htm

-josh

 

[IFRs]

BigJosh - (1) it seems to choke on 144.1 - can you check it out? (2) it does not seem to work with OpenOffice - is this a know issue?