×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Units in Excel

Units in Excel

Units in Excel

(OP)
Are there any add-ins available for Unit conversion in Excel?

RE: Units in Excel

The Analysis ToolPak includes a worksheet function called "Convert" which includes some (but not all those needed) units to convert. Search help for "Unit conversion"

Alternatively if you have specific units for frequent conversion you might want to make your own function :

*************************************************
Public Function ConvertUnit(Value, ConvertFromUnit, ConvertToUnit)
Dim ConvertToSI As Double
Dim ConvertFromSI As Double
Dim UnitToType As Integer
Dim UnitFromType As Integer

Select Case True
Case ConvertFromUnit = "psi"
UnitFromType = 1
ConvertToSI = 6895
Case ConvertFromUnit = "bar"
UnitFromType = 1
ConvertToSI = 100000
Case ConvertFromUnit = "psig"
UnitFromType = 1
ConvertToSI = 6895
Case ConvertFromUnit = "barg"
UnitFromType = 1
ConvertToSI = 100000
Case ConvertFromUnit = "N/mm2"
UnitFromType = 1
ConvertToSI = 1000000
Case ConvertFromUnit = "N/m2"
UnitFromType = 1
ConvertToSI = 1
Case ConvertFromUnit = "ksi"
UnitFromType = 1
ConvertToSI = 6.895
Case ConvertFromUnit = "ºF"
UnitFromType = 2
ConvertToSI = (Value - 32) * 5 / 9
Case ConvertFromUnit = "ºC"
UnitFromType = 2
ConvertToSI = Value
End Select

Select Case True
Case ConvertToUnit = "psi"
UnitToType = 1
ConvertFromSI = 1 / 6895
Case ConvertToUnit = "bar"
UnitToType = 1
ConvertFromSI = 1 / 100000
Case ConvertToUnit = "psig"
UnitToType = 1
ConvertFromSI = 1 / 6895
Case ConvertToUnit = "barg"
UnitToType = 1
ConvertFromSI = 1 / 100000
Case ConvertToUnit = "N/mm2"
UnitToType = 1
ConvertFromSI = 1 / 1000000
Case ConvertToUnit = "N/m2"
UnitToType = 1
ConvertFromSI = 1
Case ConvertToUnit = "ksi"
UnitToType = 1
ConvertFromSI = 1 / 6.895
Case ConvertToUnit = "ºF"
UnitToType = 2
ConvertFromSI = ConvertToSI * 9 / 5 + 32
Case ConvertToUnit = "ºC"
UnitToType = 2
ConvertFromSI = ConvertToSI
End Select

If UnitFromType - UnitToType = 0 Then
If UnitFromType = 2 Then
ConvertUnit = ConvertFromSI
Else
ConvertUnit = ConvertToSI * ConvertFromSI * Value
End If
Else
ConvertUnit = "Incompatible unit types or undefined units"
End If
End Function

**********************************************************

In this case all input units are converted to SI units before converting back to the output value.

You can easily add any other units if you need them.

Hope this helps

Regards
Mogens




RE: Units in Excel

This is a bit of a tangent question, but does anyone know if its possible to associate a unit with a value and have Excel calculate the final unit resulting from putting these values into an equation?

MathCAD has this function, but I haven't seen it anywhere else. It's extremely useful for spotting errors in formulae. When the answer is in the wrong unit something must be wrong...

RE: Units in Excel

To Peglor

To some extent it is possible by using names.

You can assign a "name" to each worksheet cell. This way all your formulas will look more like the "real thing".

Select from the excel menu:
<insert><name><define>

or read the excel help for using names

This won't however make your visual formulas look any better on the worksheet, only the ones stored in the cells.

Hope this helps
Regards
Mogens




RE: Units in Excel

Instead of using macros for units, you can use lookup tables and drop down menus, this way you dont have to worry about mispelling words at all, and personally I find the data easier to read and check in a table than in a macro.

RE: Units in Excel

What I was wondering is whether it is possible to associate for example the unit of meters with one cell, associate seconds with another so that when I divide the first by the second, Excel will automatically associate m/s with the result.

RE: Units in Excel

That's when you need to start looking at Mathcad, TKSolver, or Matlab.

TTFN

RE: Units in Excel

Peglor,

Although I've not thought about it sufficiently to be sure, I believe that if one were sufficiently motivated, one could use VBA to accomplish what you're talking about.

This would be particularly true if the input cells were limited to an exclusive range of cells.

I've never seen anything like that "off-the-shelf", though.

It's funny that you bring it up because I've got a spreadsheet that I'm considering converting into Mathcad for exactly that reason.

My guess is that Bill will include that functionality in some future release of Excel. It would really enhance the program.

RE: Units in Excel

Don't count on Bill, he only knows conversions to $$$.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Units in Excel

IRStuff:

Does Matlab support units? I haven't seen it used that way, but I'm already using that program, so adding units would be very handy. I've used MathCAD and it's unit handling is brilliant - Unit mismatches have flagged errors I don't think I'd have caught so easily any other way.

RE: Units in Excel

Electricpete had a way of doing it, but now that I look at more closely, it looks like it's mostly embedded as conversion factors:
see bottom of Thread724-61185

Otherwise, only Stateflow explicitly supports units

TTFN

RE: Units in Excel

Peglor,

Just to clarify...Mathcad doesn't always help spotting errors when using the enabling the units.

For example, when calculating the modulus of elasticity of normal weight concrete, which may be taken as 57000*(F'c)^0.5 or 57000 times the square root of F'c (28 day compressive strength of concrete) you won't get the proper units.  Er...rather you do get the proper units!!  Just the proper results.

Anyway, I've found that MathCad is a very good program.  I've also learned that when you don't have MathCad or equal you can make do with Excel - even in programming - it just takes some thought.

RE: Units in Excel

I'm simultaneously posting this on the Mathcad forum, but since you guys are on the topic...

I have Mathcad 5.0+, which works very well for me, but I find I have to ADD units by typing them at the top of any analysis sheet before I can get down to business.  I often deal with speeds expressed in "knots" (nautical miles per hour) so I always start my sheets with "knots:=1.1508*mph".  I to the same thing for KSI:=1000*psi.

I also find that there's no good default set of units, so every time I state a calculated value I have to type "lbf" at the end of the "lb*ft*sec-2" that automatically comes up.

Is there a better way?  Do more recent versions of Mathcad include a "unit definition" that can be configured?

STF

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources