Units in Excel

[Gearhead]

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

 

[mgp]

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

 

[peglor]

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

 

[mgp]

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

 

[Jkaen]

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.

 

[peglor]

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.

 

[IRstuff]

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

TTFN

 

[Binary]

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 &quot;off-the-shelf&quot;, 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.

 

[joerd]

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.

 

[peglor]

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.

 

[IRstuff]

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

 

[Qshake]

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.

 

[Sparweb]

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 &quot;knots&quot; (nautical miles per hour) so I always start my sheets with &quot;knots:=1.1508*mph&quot;. 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 &quot;lbf&quot; at the end of the &quot;lb*ft*sec^-2&quot; that automatically comes up.

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



STF