InchCalc is an Add-in for Microsoft Excel that makes it possible to do calculations in feet and inches. It is handy for architects, builders, and carpenters.
Here is an example spreadsheet showing how to sum a list of dimensions...
Here is what the formulas look like for the above spreadsheet...
For Excel 2007
,
and then click Excel Options. Click Browse, and then locate the InchCalc.xla add-in file that you downloaded above.
For Excel 2000-2003
Click Browse, and then locate the InchCalc.xla add-in file that you downloaded above.
For Excel 2004 Mac
Click Select, and then locate the InchCalc.xla add-in file that you downloaded above and click Open.
InchCalc is now installed. The functions i2s() and s2i() should be available
just like any other Excel functions.
Click here to download and open the demo workbook
InchCalcDemo.xls in Excel and play with it! There
are three sheets in this workbook, be sure to look at them all.
InchCalc adds two new functions to Excel;
s2i() converts a displayable string showing inches and feet into a a number
of inches. The number of inches can be used in formulas and sums. For example,
the s2i() of 2' 6" is 30 because 2 feet + 6 inches = 30 inches.
i2s() converts a number of inches to a displayable string
showing feet and inches. For example, the i2s() of 145 is 12' - 1".
s2i() and i2s() are inverse functions, so i2s( i2s( 25 ) ) = 25.
Typically you will set up your spreadsheet so you can enter all your
measurements into a column of cells, then convert those measures into their
equivalent inches in an adjacent column with s2i(), then do all your
calculations in inches, and finally convert the answer in inches back to a
displayable form with i2s(). The best way to understand this is just open the
sample spreadsheet and just play around with changing the values and formulas.
It is very simple.
The i2s() function optionally supports a couple of different formats for the
generated string. You specify which format you want by adding a second argument
to the i2s() function call.
| i2s() Formats | |
| (default) | f'_-_i_n/d" |
| 1 | f'-i_n/d" |
| 2 | f'_i_n/d" |
| 3 | f'i_n/d" |
| 4 | f'_i-n/d" |
| 5 | f'i-n/d" |
f=feet, _=space, i=whole number of inches, n=numerator of fractional inches, d=denominator of fractional inches
Additionally, the i2s() function supports a second optional parameter that when specified as '1' suppresses the conversion of inches to feet, so the formatted value only includes whole and fractional feet.
The formatting demo spreadsheet below shows how different values look with the different formats...
The s2i() function is pretty flexible and will accept almost any reasonable
combination of feet, inches, fractions, decimals, spaces, and dashes. Note that
double quote character is a bit problematic in Excel since they usually surround a
text string. The easiest way around this is to put each dimension in its own cell and
reference those cells in your calculations rather than trying to type the
dimension directly inside a formula. If you have to put a dimension inside a
formula you can use the function char(34) to
generate a double quote. For example, s2i( "2' 6"+char(34) )
= 30. Or you could use the i2s()
function to generate the string.
Here is an example of how you might do area-based costing calculations with InchCalc...
..and the formulas used...
If you have trouble downloading the add-in file and the sample spreadsheet from the links above, they are both inside the ZIP file below...
http://josh.com/InchCalc/InchCalc.zip
Q: Why do I get values with lots of extra trailing decimal digits when I do conversions like =i2s(144.1)?
A: 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 measurements since most people by convention specify inches in fractions since that it how ruler and other measuring tools work. Note that decimals are ok, as long as they are halves, quarters, eighths, sixteenths, etc. rather than tenths.
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.
Q: Does InchCalc support OpenOffice?
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.
Q: When I try to download the spreadsheet files, all I get is a text file full of garbage. What Can I do?
A: There are lots of things that can get in the way of you directly downloading Excel spreadsheets. These are usually put in place to block viruses. Try downloading the ZIP file instead, then extract the files form the ZIP onto your hard drive and open them from there.
|
12/20/2006 |
|
|
01/24/2007 |
|
| 4/23/2007 |
|
| 8/26/07 |
|
If you have any problems or questions, you can email me at...
InchCalc is (c)2006 Joshua Levine. InchCalc is free to use. If you want to modify or sell InchCalc, or include it in your product, please email me and we can work something out.