Units in Excel
Units in Excel
(OP)
Are there any add-ins available for Unit conversion in Excel?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
|
RE: Units in Excel
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
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 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
RE: Units in Excel
RE: Units in Excel
TTFN
RE: Units in Excel
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
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Units in Excel
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
see bottom of Thread724-61185
Otherwise, only Stateflow explicitly supports units
TTFN
RE: Units in Excel
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 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