Inches to Feet-Inches
Inches to Feet-Inches
(OP)
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
thanks, Dik
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Contact USThanks. We have received your request and will respond promptly. Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines |
|
Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.
Here's Why Members Love Eng-Tips Forums:
Register now while it's still free!
Already a member? Close this window and log in.
RE: Inches to Feet-Inches
=INT(F12/12)&"'"&"-"&MOD(F12,12)&""""
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
format the cell for the inches using the fractions type under format/cells/number/catetory/fractions.
It is a little inelegant in that when you choose eighths you get 2/8 and not 1/4, but it is something.
regards,
chichuck
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
=INT(F12/12)&"'"&" - "&INT(MOD(F12,12))&" "&ROUND((F12-(INT(F12/12)*12)-INT(MOD(F12,12)))*32,0)&"/32 """
RE: Inches to Feet-Inches
I wonder if anybody know the way to convert the fractions to 16ths? If possible, it would automatically convert the 16ths to simplest fractions as well. For instance, 8/16 to 1/2. Any genius outthere?
RE: Inches to Feet-Inches
Converting 98 inches to 8'2" however is achievable in a few steps, but the output will be a text field, not a number field. You can create a column with just the inches component by using the formula: =MOD(A1,12). Assuming 98 inches is in cell A1, that formula will return a value of 2. Extracting the feet involves the INT formula: =INT(A1/12). You now have one column with the feet, another with the inches.
If you're not picky, and you're just looking for presentation, then you can use custom formatting to make them look correct. Format Cell --> Custom --> type in the box [0"ft"] or if you want the space first, [0" ft"]. Similarly for the inches. You can use the ' and '' (use two single quotes) marks if you like, and you can also put a - before the inches ["- "0" in"]. Just put whatever text you want in quotes, and remember double quotes inside double quotes will not work.
If you want the ft and in in one field, then you have to give up the number quality. (But you can preserve the decimal number in a hidden field for calculation purposes.)
Clyde's solution above should work.
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
Dik
RE: Inches to Feet-Inches
A modification should allow any value over 12 to display feet as well as inches.
CODE
Dim Num As Long, sixteenths As Integer
Num = Fix(Dec_Arg)
sixteenths = Abs(Fix((Dec_Arg - Num) * 16))
Select Case sixteenths
Case 1, 3, 5, 7, 9, 11, 13, 15
Imp_Frac = Num & " " & sixteenths & "/16"
Case 2, 6, 10, 14
Imp_Frac = Num & " " & sixteenths / 2 & "/8"
Case 4, 12
Imp_Frac = Num & " " & sixteenths / 4 & "/4"
Case 8
Imp_Frac = Num & " 1/2"
Case Else
Imp_Frac = Num
End Select
End Function
RE: Inches to Feet-Inches
So, is there a way to convert that inches in feet-inches to fraction?
I interpreted the above to mean "is there a way to convert inches to feet-inches where the inches are expressed as a fraction." Here are some inputs and outputs:
96 8' - 0 0/32 "
96.25 8' - 0 8/32 "
50 4' - 2 0/32 "
50.125 4' - 2 4/32 "
112 9' - 4 0/32 "
112.125 9' - 4 4/32 "
115.35 9' - 7 11/32 "
Perhaps if you express the problem clearly you will get more helpful responses.
RE: Inches to Feet-Inches
96 8' - 0"
96.25 8' - 1/4"
50 4' - 2"
50.125 4' - 2 1/8"
112 9' - 4"
112.125 9' - 4 1/8"
115.35 9' - 7 7/20"
=INT(F12/12)&"'"&" - "&TEXT(MOD(F12,12),"# #/##")&""""
RE: Inches to Feet-Inches
CODE
RE: Inches to Feet-Inches
CODE
RE: Inches to Feet-Inches
Using the code you've provided, I typed 22.02 into cell A1. This gives the following result: 1'-10 0/1 "
Why does it not give 1'-10 0/16" or just 1'-10" ?
RE: Inches to Feet-Inches
I also changed the SIGN part, to show 0'-0" if there is a value of 0 entered, instead of - 0'-0".
CODE
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Inches to Feet-Inches
Thanks for fixing the formula, it seems to be bulletproof now. Great result of combined efforts. Thank you dik for bringing up interesting problem!
yakpol
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
CODE
RE: Inches to Feet-Inches
CODE
should work for any number now!
RE: Inches to Feet-Inches
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Inches to Feet-Inches
CODE
RE: Inches to Feet-Inches
Can you make one that does not break out the feet, but would yield 25 15/16" for instance?
RE: Inches to Feet-Inches
CODE
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Inches to Feet-Inches
CODE
RE: Inches to Feet-Inches
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" ?
RE: Inches to Feet-Inches
CODE
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
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.
RE: Inches to Feet-Inches
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!
RE: Inches to Feet-Inches
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
CODE
yakpol
RE: Inches to Feet-Inches
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.
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
JP.
RE: Inches to Feet-Inches
http://josh.com/InchCalc/index.htm
-josh
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
This is an inherent problem in Excel. Try typing the following formula into Excel (any Excel spreadsheet, InchCalc *NOT* required);
=1*(.5-.4-.1)
You might be very surprised to see that the answer is NOT zero.
I could make InchCalc hide this problem from you by rounding in the incoming inches to, say, 10 decimal places but I generally prefer to have things fail quickly and explicitly rather trying to hide the problem only to have it show up unexpectedly in a seemingly unrelated cell.
You can read the Microsoft Knowledgebase article here;
h
Ultimately the solution is to not use decimals unless they are an even power of two in the denominator (0.125 is ok, 0.100 is not). This is not too much of a problem for InchCalc since most people by convention specify inches in fractions (1-3/4" rather than 1.74"). This is, however, a huge problem in many other non-InchCalc spreadsheets (pretty much all financial spreadsheets that do math on currencies have these errors hiding in them- often leading to WRONG answers).
Maybe someday Excel will permanently solve this problem by adding a Binary Coded Decimal data type. Most modern computer programming languages have this (in Java it is called BigDecimal). It is slow, but you never get decimal error creep.
RE #2 - OpenOffice support:
While it certainly would be possible to make a version of InchCalc that works with OpenOffice, I've found that most professionals use Excel. Additionally, OpenOffice's documentation for creating add-ins is not great, so it would be more difficult that it should be. That said, if there were enough people who really wanted it, I be happy to put in the effort.
RE: Inches to Feet-Inches
=IF(TRUNC(ft)=0,IF(SIGN(ft)=1,"","-"),TRUNC(ft)&"' ")&TEXT(MOD(IF(SIGN(ft)=1,1,-1)*ft,1)*12,IF(TRUNC(ft)=0,"0 ","- 0 ")&IF(ABS(MOD(ft,1)*12-ROUND(MOD(ft,1)*12,0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(ft,1)*12,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),""))&""""
Ben
RE: Inches to Feet-Inches
Couple of things ...
using following formula in your post ...
=IF(TRUNC(ft)=0,IF(SIGN(ft)=1,"","-"),TRUNC(ft)&"' ")&TEXT(MOD(IF(SIGN(ft)=1,1,-1)*ft,1)*12,IF(TRUNC(ft)=0,"0 ","- 0 ")&IF(ABS(MOD(ft,1)*12-ROUND(MOD(ft,1)*12,0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(ft,1)*12,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),""))&""""
that converts 10.106 to 10' - 1 1/4" (10'-2 1/16" in your post is most likely a typo)
Anyway, following is my shorter formula ...
=INT(B2)&"'-"&TRIM(TEXT(ROUND(MOD(B2,1)*12*16,0)/16,"# ??/??")&"""")
that converts 10.106 to 10'-1 1/4 "
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Inches to Feet-Inches
So I guess my questiosn is how I get the numbers I want to mainipulate into the same cell's as the preceding formulas that then convert those numbers to feet and inches??
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
as a simple example, say I put the number 1.25 in once cell ... then 2.50 in another cell ... a third cell adds the two .... EXCEL would report the result as 3.75 .... if, however I wanted it to read 3'-9" via the nice and handy formulas created above, how would I do that?
In other words, the cell (lets call "C1") that sums two numbers would have something like "=A1+B1" .... so how would I input (somehow) the handy formuala that converts to feet and inches (the "=(IF ....)" in the same cell that also has "=A1+B1"?
How do I combine the summing equation portion with the formatting formula that converts to feet and inches??
TIA
RE: Inches to Feet-Inches
A B C
1 1.25 2.50 3.75
...where C1 is actually the formula "=A1+B1".
You could then paste the following formula in cell D1...
=INT(C1)&"'-"&TRIM(TEXT(ROUND(MOD(C1,1)*12*16,0)/16,"# ??/??")&"""")
...and you would see 3' - 9" in D1.
You could change the "c1" in the above formula to refer to any cell that has a number of feet expressed as a decimal. It can even be a relaive cell reference so that you could patse the formula into a whole column of cells and it would display a whole column of numbers in the foot/inch format.
Make sense?
If you use InchCalc, it is slightly more straightforward since you get a new Excel function called i2s() to do the same conversion, so you could just put...
=i2s( =a1+b1)
...in cell C1 and C1 would then display the sum in foot/inch format
-josh
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
If you have entries 1.25 in cell A1, and 2.5 in cell B1, then you can also use ...
CODE
CODE
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Inches to Feet-Inches
Hello everyone,
I would like to add a question to the mix ... How about keeping the inch and displaying the fraction? 6.375" shown as 6 3/8"
Thanks for posting, this site is a great help.
RE: Inches to Feet-Inches
=i2s( 6.375 )
...will show 6' - 3/8" in the cell. You can also use the optional formating parameter to chaneg the output. Forf example...
=i2s( 6.375 , 1 )
...will show 6' 3/8" (no dash). There are many other formats available.
You can download the free InchCalc package here...
http://josh.com/inchcalc/
-josh
RE: Inches to Feet-Inches
Josh,
I loaded the InchCalc and it works great for single digit inch enrties, but it converts any number larger than 12.00" to feet. 16.25" becomes 1'-4 1/4" and not 16 1/4". Are there additional ways to modify the output than just the end display?
I've tried various modifications of some of the formulas earlier in this thread, but with no success.
RE: Inches to Feet-Inches
For example...
=i2s( 144.0625 , 3 , 1 )
...will display...
144 1/16"
You can download the new version from...
http://josh.com/inchcalc
Give it a try and let me know if this is what you need.
-josh
RE: Inches to Feet-Inches
Josh,
It works fine in the demo, but when I load the new .xla file and try to use it, "Compile error: Variable not defined" pops up and the VBA window opens with this displayed ... "f = Int(a / 12)" but with the "f =" highlighted in blue.
Where might my error be?
Taylor
RE: Inches to Feet-Inches
-josh
RE: Inches to Feet-Inches
Josh, Yes indeed! Whatever you did corrected the error. This is a very nice tool to have, thanks for all your help.
Taylor
RE: Inches to Feet-Inches
BigInch
-born in the trenches.
http://virtualpipeline.spaces.msn.com
RE: Inches to Feet-Inches
RE: Inches to Feet-Inches
'pose I should change my name now, too.
BigInch
-born in the trenches.
http://virtualpipeline.spaces.msn.com
RE: Inches to Feet-Inches
I'm trying to use InchCalc and getting results like this:
12 1' - 0"
12.1 1' - 9.99999999999996E-02"
12.2 1' - .199999999999999"
12.3 1' - .300000000000001"
12.4 1' - .4"
12.5 1' - 0 1/2"
12.6 1' - .6"
12.7 1' - .699999999999999"
12.8 1' - .800000000000001"
12.9 1' - .9"
13 1' - 1"
13.1 1' - 1.1"
13.2 1' - 1.2"
13.3 1' - 1.3"
13.4 1' - 1.4"
13.5 1' - 1 1/2"
13.6 1' - 1.6"
13.7 1' - 1.7"
13.8 1' - 1.8"
13.9 1' - 1.9"
14 1' - 2"
14.1 1' - 2.1"
14.2 1' - 2.2"
14.3 1' - 2.3"
14.4 1' - 2.4"
14.5 1' - 2 1/2"
14.6 1' - 2.6"
14.7 1' - 2.7"
14.8 1' - 2.8"
Can anyone help? I am using the formula =i2s(A1) and thought I would get #' - #" #/# and not fractions of an inch.
Cheers
RE: Inches to Feet-Inches
I'm not sure what you mean by "not getting fractions of an inch". Can you give some examples of =i2s() output and what you would want the output to look like?
-josh
RE: Inches to Feet-Inches
Hg
Eng-Tips policies: FAQ731-376: Eng-Tips.com Forum Policies
RE: Inches to Feet-Inches
The example above used an input of 12.20 as opposed to 12.25. I think the correct output for 1.20 is 1' - .20" as shown.
In practice, non-fractional decimals (like 0.20) should not come up in measurements since most people use multipules of 1.32nds or 1/64ths.
RE: Inches to Feet-Inches
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Inches to Feet-Inches
I've put a lot of thought into what the correct result should be when converting non-fractional measuraments like "0.9999999/256" and I think returning an ugly but correct answer is preferable to giving a neat-looking, but incorrect answer.
If all you really want is an aproximation anyway, you could always fix-up the "0.999999999" first by running it though a round() or int(). At least this way you are explicit stating that you want an aproximation rather than an exact answer.
But believe me, if you can think of a better and more consistant way to handle this issue, I am all ears!
Thanks,
josh
RE: Inches to Feet-Inches
I am an architect (originally from australia now in the US) and until now have worked exclusively in mm.
The software package we use (Catia) spits out data to spreadsheets in inches however the contractor wants the information in feet, inches and fractions of inches so now I understand what it is the InchCalc addin is doing I can apply a rounding factor to my data to ensure it is in the right format.
Obviously building tolerances are very different to those in the aerospace industry.
cheers everyone