Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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)".

    I hope I made sense...

    Thanks,
    Hanan.

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customised excel formulas (97/SR1)

    Thanks for the reply.

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Customised excel formulas (97/SR1)

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

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customised excel formulas (97/SR1)

    Here is another (not free).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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