The Units4Excel spreadsheet described in the first post of this series provides functions to convert values from one set of units to another, but does not allow for automatic evaluation of functions, taking account of the specified units, as performed by programs such as MathCad and SMath.  I have now rectified this omission with the addition of the User Defined Function (UDF) EvalU().  The revised spreadsheet may be downloaded from  Units4Excel.zip, including full open source code.  At the moment the new function has only been added to the stand-alone spreadsheet (Units4Excel.xlsb).  It will be added to the add-in version at a later date.

The UDF input and output options are described in the screenshot below:

EvalU Function input and output

Typical usage is shown below, with the evaluation of the deflection of a cantilever with a single point load at the end.  Note that the input values are in a mixture of kips and psi for forces, and inches and feet for length.  The first EvalU (Cells I20:J20) function calculates that the function value has units of length, and returns the value in metre units, together with the unit type.  If the optional ReturnUnits value is given (as in I21:J21) the function converts the output to the specified units, or if the units are inconsistent returns an error message.  Cell I22 shows the result of the Eval UDF (also included in the spreadsheet), which evaluates the function ignoring units. 

Evaluation of cantilever deflection with mixed units.

The second example shows the output of one function (cells I29:J29) used in the input to another function, including the unit type. The final example shows that where the input values have consistent units, and the specified return units are also consistent (in^4 in the example shown), then the EvalU and Eval functions give the same result.

Section Properties of a Tapered Channel

Please take note of the warning in large red type. Any comments or suggestions will be gratefully received.