Working with implied units

The Units4Excel spreadsheet provides a number of functions to convert between different units and evaluate functions entered as text on the spreadsheet, taking account of the units of both the inputs and the results.  The latest version can be downloaded from:

I have now added some examples comparing reinforced shear capacity to the ACI 318 design code, using the “customary US units” and SI units versions of the code:

The examples shown below use the user defined functions (UDFs) Eval and EvalU.  Eval returns the value of any function entered as text on the spreadsheet, substituting given values for any parameters.  EvalU is a “unit aware” version, returning the result in base SI units, or in any specified units.  EvalU returns the calculated units if they are not specified, or an error message if the specified output units are not consistent with those calculated from the input units.

Evaluating formula 11-4 as given in the code produces strange results, even if consistent input units are used.  If the input units are ignored (using the Eval function) the formula returns the correct results, provided that the input has consistent units, but using EvalU with no specified return unit finds a unit with non-integer exponents, and including m, rather than just N.  If the return units are specified, as a force unit, this results in an error message, since the specified return units are different to those calculated from the input units:

The problem is that the code formula contains implied units, since Nu/(2000*Ag) has units of stress (rather than no units), and fc^0.5 has units of stress^0.5, rather than stress.  The units can be made consistent by converting the 2000 (or 14 for the SI version) to an input parameter, Kc1, with stress units, and adding a new parameter, Kc2, also with stress units, as shown below:

EvalU now returns calculated units of N, as expected, and both versions of the formula return very similar results in N.  Also note that when the output units are consistent with the input units (either lbf or N), EvalU and Eval return exactly the same value, as would be expected.

It is also possible to incorporate the initial constants (2 or 0.17) in the Kc1 and KC2 factors, and to use inconsistent input units.  In the first example below Kc1 and Kc2 have been changed to ksi units, and Kc2 is factored by 2^2, so the initial 2 factor can be removed.  In the SI example the units of Kc1 and Kc2 are unchanged, but Kc2 is factored by 0.17^2:

With the revised formulas the EvalA results are correct, but the Eval results, ignoring the input units, are now incorrect, because the input units are inconsistent (stresses are in ksi or MPa, but forces are in lbf or kN).

Formula 11-6 has no factors with implied units, so can be evaluated with no additional parameters, but as before, the input and output units must be consistent if Eval is used.  EvalU will return correct results with any recognised moment output units:

The results of 11-6 can now be used to evaluate 11-5 for sections with combined axial load and shear:

Finally the upper limit to the shear force for combined axial load and shear is found with formula 11-7.  Note that in this case the final expressions in the formula have a different form in the two versions of the code (1 + Nu/(500*Ag) for the US units version and 1 + 0.29*Nu/Ag for the SI version).  Both have been converted to the form 1 + Nu/(Kc2*Ag), so Kc2 can have units of stress, so for the SI version the value of Kc2 becomes 1/0.29:

Two additional points to note when using EvalU:

  • The units are case sensitive, so if Excel helpfully insists on correcting MPa to Mpa, the units will not be recognised.  To avoid this problem go to File-Options-Proofing, click AutoCorrect Options, and either unselect “Correct TWo Initial CApitals, or click Exceptions and add MPa to the exceptions list on the Initial CAps tab.  For a quick one-off you can also enter a space after MPa, then press Ctrl-Z.
  • Both input units and output units (if used) must be recognised units with the correct abbreviation.  The ListNonSI UDF will list all recognised units for any specified unit type, for example “area” as shown in the screen-shot below.  To display all the results enter the function with the unit type, then press Ctrl-Shift-S to resize the output array.

Update 30 Sep 2017:
Following comments at Eng-Tips I have added two examples with different procedures for evaluating ACI equations for the flexural tensile strength of concrete:

The screenshot below shows equations from the US units and SI units versions of ACI 318, evaluated using the procedures described above, i.e. the concrete compressive strength has been factored by one, with units appropriate to the code.  Note that the SI results are about 12% higher than the US units results, because the Kc1 factor is rounded up in the SI code:

An alternative approach is to divide the fc value by the units specified for the equation.  The initial factor should than be given units of stress, or alternatively multiplied by the alternative stress units.  Unlike Mathcad (which recognises units within a formula), EvalU requires the units to be specified as a parameter, with a value of 1, as shown below:
The third example shows the US units formula evaluated with fc in MPa, and return units specified as psi, showing exact agreement with the US units version.

This entry was posted in Concrete, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to Working with implied units

  1. Pingback: #Excel Super Links #153 | Excel For You

  2. Pingback: Working with implied units – update | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.