# Thread: Customised excel formulas (97/SR1)

1. ## Customised excel formulas (97/SR1)

Hi,

I am trying to set up a customised spreadsheet that contain unit conversion "functions", that I will be able to call every time I open a new spreadsheet. I am dealing with a lot of measurements that are entered in English units and I have to convert them into SI or metric units in order to use them in my calculations. For example, converting from inches into meters, lbs into kgs, Btu into Watts, etc. In fact, the conversion units that I have involve a combination of units that I mentioned. Is there a way to set up a general spreadsheet that contains the conversion units that I wish to use, and then be able to call them by a specific unit name whenever I work in another spreadsheet?

Another example of converting from inches to meters:
in A1 I have set a value of 1 inch. In A2 I want the value to read 0.025 meters (without directly entering the formula of =0.025*A1). Instead I would like to enter in A2 a function name of "inch-to-m(A1)".

Thanks,
Hanan.

2. ## Re: Customised excel formulas (97/SR1)

Hanan,

Microsoft already has a function "Convert" that does all the conversions you mention...
=CONVERT(1,"in","m") returns 0.0254
=CONVERT(1000,"btu","wh") returns 293.071851

The function is in the AnalysisTooPak, available by check marking it in Tools | Add-ins.

Regards,
Jim Cone
San Francisco, CA

3. ## Re: Customised excel formulas (97/SR1)

This seems to work well for basic units such as inches and pounds. However, when I try to incorporate more compicated units such as heat transfer coefficient (J/kg/K or Btu/lb/F) or density (kg/m^3 or lb/ft^3) the CONVERT option gives me an error. What options do I have in this case?

By the way, I do know the convertion values for the units that I am dealing with, but I need a general call function (such as CONVERT) to be able to use unit conversions in my different spreadsheets. If you need an example spreadsheet, I would gladly provide it.

Thanks,
Hanan.

4. ## Re: Customised excel formulas (97/SR1)

If you want to create one, you would have to create a custom function. I think the easiest way might be to create a hidden sheet with all the conversions:
row 1, cols B to whatever, a list of all the "lookup" abbreviations (in, mm, kg/m3, etc) and down col A, rows 2 to whatever, the same list.

The items in colA are the units you have, and row 2 is the units you want. In the intersection of each you would put the amount to multiply the current unit value to get the new unit's value

Then your custom function could be called like:
=myConvert(a1,"mm","in")

the function would use match to find the row that "mm" is in col A, and use match to find the column that "in" is in row 2, and get the intersect of this col and row and multiply that number by A1 and report that number.

You, of course, would want some error indication to let them know when the unit abbreviations are incorrectly typed.

If you want it available to many spreadsheets, save it as an addin and attach the addin.

Does that get you started?
Steve

5. ## Re: Customised excel formulas (97/SR1)

Here are some converter program addins, maybe the would be of use.

Steve

6. ## Re: Customised excel formulas (97/SR1)

Here is another (not free).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•