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

 

[bigjosh]

RE #1 - InchCalc chokes on i2s(144.1):

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;

http://support.microsoft.com/default.aspx/kb/214118

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.

 

[Ben4piping]

I cannot remember who developed this formula initially. Some changes were made by me, but I cannot explain how it works. The formula is used in a spreadsheet to calculate summations. Separate rows and columns contain dimensions in (+ or -) metric, feet, inches, fractions. These are summed and converted to decimal feet, then expressed as ft-in-fraction (10.106 converts to 10' - 2 1/16"). The user can also do other math operations on the decimal values in cells.

=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

 

[yogia]

Hi Ben:

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

 

[ranmoo]

this is a really novice question. I have cut and pasted the formulas that have been suggested above for converting decimal numbers to feet and inches (and fractions thereof) .... but how do you get actual numbers into the given cell where the long "=IF(......)" formulas are at? I have tried, putting formulas in front of the "IF(...." statements and just keep getting errors messages with the cell's formula.

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

 

[davidbeach]

Number in one cell, formula with the result in a different cell.

 

[ranmoo]

I am missing something very very basic here .... so apologize.

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

 

[bigjosh]

In your example, lets say you have this spreadsheet...

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

 

[davidbeach]

=INT(C1)&"'-"&TRIM(TEXT(ROUND(MOD(C1,1)*12*16,0)/16,"# ??/??")&"""")

Assuming that formula works (I haven't checked) you could put it in cell C1 and change all references to "C1" in the formula and change them to "A1+B1". But in any case, the formula and the number(s) to convert are in different cells.

 

[yogia]

Hi ranmoo:

If you have entries 1.25 in cell A1, and 2.5 in cell B1, then you can also use ...

=INT(A1+B1)&"'-"&TRIM(TEXT(ROUND(MOD(A1+B1,1)*12*16,0)/16,"# ??/??")&"""")

or, use

=INT(C1)&"'-"&TRIM(TEXT(ROUND(MOD(C1,1)*12*16,0)/16,"# ??/??")&"""")

and both do work to give the correct result of 3'-9"


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[taylor125]

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.

 

[bigjosh]

With InchCalc, the following formula...

=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

 

[taylor125]

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.

 

[bigjosh]

I just published an updated version of InchCalc that now includes a second optional parameter to the i2s(( function that will supress the conversion to feet.

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

 

[taylor125]

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

 

[bigjosh]

Sorry, I think I messed up the upload. Please try downloading again and let me know if it still is not correct.

-josh

 

[taylor125]

Josh, Yes indeed! Whatever you did corrected the error. This is a very nice tool to have, thanks for all your help.

Taylor

 

[BigInch]

TG I'm in Spain!

BigInch-born in the trenches.

http://virtualpipeline.spaces.msn.com

 

[jghrist]

Yeah, but in Spain, how interesting do conversions get? Could you generate 56 responses to a problem of converting meters to millimeters?

 

[BigInch]

I give you that one. happily.

'pose I should change my name now, too.

BigInch-born in the trenches.

http://virtualpipeline.spaces.msn.com

 

[turtleshead]

hello,

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