ChangeUnits

NOTE: All of the examples are programmed in the Excel file ChangeUnits_Examples.xls on the first worksheet (Examples). This file is included with the ChangeUnits add-in.

The simplest use of ChangeUnits is to change a value from one unit to another using the ChangeUnits function.  Figure 1 below shows an Excel worksheet in which 10 inches has been changed to different units using the ChangeUnits function.  Column D has the call to the function: in cell D2, the formula is “= ChangeUnits(A2, B2, E2)”.  In cell D3, the formula is “= ChangeUnits(A3, B3, E3)”, etc.

In cell D2, 10 “inch” changes to 10 “in”, since both “inch” and “in” are interpreted or parsed as inches.  In cell D3, 10 “inch” is equal to 0.254 “m”.  In cell D4, we have tried to change “inch” into “dollars”.  Since “dollars” is unknown to ChangeUnits, the error message returned is “Unknown unit ‘dollars’”.  Finally, in cell D5, we have tried to change 10 “inch” into “kg”.  Since “inch” and “kg” are not equivalent units, ChangeUnits returns the error message “Inconsistent units”.

Figure 1.  The ChangeUnits function.

In Figure 2 below, we show a number of alternative but equivalent methods for expressing the same units that are recognized by ChangeUnits (m / s^2 in this example).

Figure 2.  Alternative Syntaxes Recognized by ChangeUnits

 Figure 3 below uses the ToMetric() and FromMetric() functions to calculate the area of a rectangle.

 

Figure 3.  Calculating the area of a rectangle in typical units.

The cells have been color coded so that the user inputs are yellow, the final answer is white, and the formula is light blue.  The pale green cells show the equivalent MKS units.

The width of the rectangle, b, is given in cell C4 and its units are given in cell D4.  In this example, b = 1 m.  The height of the rectangle, h, is shown in cell C5 and its units are shown in cell D5.  In this case, h = 2 m. 

Although the input units are already in MKS units, this may not always be the case. We therefore explicitly change the input units into MKS values in column E.  Cell E4 is the function “= ToMetric(C4, D4)”, which gives a value of 1, and cell E5 is “= ToMetric(C5, D5)”, which gives a value of 2.

The formula for the area of a rectangle, A = b * h, is programmed in cell E6 using the values in the MKS column E, namely, E6 = E4 * E5.  For this problem, the area is 2 m^2.

As output, we list the units we want in cell D6, in this case m^2.  The answer to the problem is given in C6 using the formula C6 “= FromMetric(E6, D6)”.

We show the MKS units used in column F.  For example, cell F4 “= ShowMetric(D4)”, etc.

Now suppose that we want to calculate the area of a rectangle with different inputs. Suppose b = 1.0e4 inches, h = 2 mm, and we want the answer in mile^2.  We simply change the yellow cells C4, D4, D5, and D6, and obtain Figure 4.

Figure 4.  Area of a rectangle in some unexpected units.

The formula itself (cell E6) did not need to be changed, only the input units and values and the output units.  Once a formula has been correctly programmed, the only errors that occur are typographical errors in the yellow input boxes.

The disadvantage of doing calculations this way is that the addin does not know the correct output units of the formula that has been programmed, so it cannot catch when the user asks for inconsistent output units.  For example, in Figure 4, if one puts “lbm” into cell D6, then cell F6 correctly updates to kg, but the value in cell C6 is 1.119948292, the equivalent of 0.508 kg even though the formula intends that cell E6 be in length-squared units rather than mass.

Syntax

ChangeUnits uses the following syntax rules and conventions:

 •     All units are case and spelling sensitive.

•     All units are singular (inch rather than inches)

•     The only characters that matter are the units, the first division sign (“/”)   encountered, the exponentiation character (“^”), and numerical exponents (which must be positive).  Everything else is ignored (i.e., treated as white space).  So, for example, the following are all interpreted as equivalent:
              m / s / s = m / s – s = m / s s = m / s s = m / s^2
Parentheses are always ignored.

•     Negative exponents are not allowed.

•     Exponents can be expressed as positive integers, simple decimals (no scientific notation), or simple fractions (one number divided by another).  For example, psi^1/2 = psi^0.5 are valid (and useful for Hertz contact stress).  Note that since parentheses are ignored, (lbf / in)^1/2 is interpreted as lbf / in^1/2, not psi^1/2.  You can check your units using the ShowMetric function.

•     A one “1” can be used as a placeholder as in “1 / m” or to indicated dimensionless units.

•     The units text to parse can have at most 256 characters.

 

Units

The list of pre-programmed base units only has lbf, lbm, ozf, and ozm to distinguish English mass and force.  The units lb and oz are not recognized.

Derived units with special names are included in the list of pre-programmed base units. Combinations of base units are not.  For example, velocity can be represented by any length per time combination.  However, “mph” is a common unit that cannot be parsed from basic units.  Hence, “mph” is included in the list of base units while “m / s” is not.  “m / s” is simply recognized by ChangeUnits.

The list of pre-programmed base units includes multiple spellings of the same units to allow the user to use both “inch” and “in” and to compensate for Excel’s automatic spelling correction.  For example, Excel frequently changes “MPa” to the “Mpa”.  In other cases, such as “Torr” and “torr”, both spellings are encountered and both are included. 

Many of the SI prefixes have been included in the list of pre-programmed base units.  For example, in addition to m, metric lengths include km, cm, and mm. 

The number one (“1”) can be used as a dimensionless place holder in any formula.  For example, the units for a dimensionless parameter can be listed as “1”.  Or, inverse units can denoted be prefacing the unit with either “1 / “ or even “ / “.  For example, inverse length units can be listed as “1 / m” or “/ m”.

Temperature

Temperature is a special unit because in some cases temperature is absolute and in other cases, it is relative (a difference in temperature, not an absolute temperature).  For example, to change from degrees Fahrenheit to degrees Celsius, the formula is: 

            

However, to change the units for thermal expansion from (in / in / degF) to (m / m / degC), all that is required is to divide by 5/9. Thirty-two is not subtracted off because thermal expansion is measured in changes of temperature, not absolute temperature.

To handle this, ChangeUnits always treats the case where temperature is a single unit in the numerator as absolute conversion and all other cases as relative conversion.

For example, in Figure 5 below we change absolute temperature using the ChangeUnits function.  Because all the units listed in columns B and E show only degC, degF, degK, or degR as a single base unit in the numerator, all of these interpret temperature as absolute. 

Figure 5.  Changing absolute temperature.

 In Figure 6 below, we change relative temperature by putting the temperature unit in the denominator.

Figure 6.  Changing relative temperature.

In these examples the base units degC, degF, etc. all appear in the denominator, so they are treated as relative temperatures (e.g., differences in temperature), not absolute temperatures.  Whenever temperature units are embedded in other units (such as, for example, “J / degC”) it is always interpreted as a relative temperature.

Sometimes, one wants to input a temperature difference as an input quantity, for example a change of 3 degrees Fahrenheit.  This is not directly possible with the syntax convention listed above.  A temperature difference of 3 degrees Fahrenheit, if input as “degF”, would be converted as an absolute temperature (to -16 degrees C) rather than a relative temperature (i.e., convert an increase of 3 degrees Fahrenheit to an equivalent increase of  1.67 degrees C).  In order to input temperature differences directly in ChangeUnits, the four relative temperature units degCdiff, degKdiff, degFdiff, and degRdiff have been provided.

Figure 7 below shows how to input temperature differences directly in ChangeUnits. (As an alternative, one could also input two different input temperatures, convert each as absolute temperatures, and then subtract them to get the converted temperature difference.  However, this is cumbersome.)

Figure 7.  Relative temperature units degCdiff, degKdiff, degFdiff, and degRdiff.

Sometimes, one wishes to use combinations of absolute temperature.  For example, in radiant heat, the formula involves absolute temperature to the fourth power.  Because of the exponentiation, ChangeUnits will interpret degC^4 as a relative temperature.  ChangeUnits does not provide a method for combined, absolute temperature conversion.  Instead, the absolute temperature should be input in degC as a distinct input, and then the combination (raising to the fourth power in this case) should be done as a separate Excel formula calculation.

In Figure 8 the results of the Grav( ) and GasConst( ) functions are shown. 

Figure 8.  The Grav and GasConst functions.

 Tips on Using Excel

The Excel Function Help Tool   If you forget the name of a function or the arguments it takes, you can have Excel help you.  Simply click on the "fx" button (or, in some older versions of Excel, the “=” to the left of the formula window).  Excel then asks you which function you need help with.  You can select from just the ChangeUnits functions by selecting that addin category.  Excel then gives a pop up window of the function and describes each parameter as you enter it.  This feature is recommended since it eliminates the need to remember the order of the parameters and allows you to preview the answer.

The Excel Solver.  The Excel Solver (installed as an addin named Solver Add-in from the original Excel software) can be used to find roots and minimize or maximize functions. The enables you to solve for an input value if you are given the output value desired without reprogramming a formula already set up using ChangeUnits.

Cell AutoCompletion.  Excel often tries to automatically complete the typing of your input to cells assuming that you wish to repeat an input that you already have on your worksheet.  This is useful when Excel’s guess is correct.  When constructing unit-based formulas, however, Excel is often incorrect, and it is very easy not to notice the mistakes Excel makes for you.  For example, if you have previously entered a unit like  “m / s” and then enter just “m” below it, Excel will often change that to “m / s” even if you typed only “m” and return. The AutoComplete option can be turned off in Excel by deselecting (unchecking) the “Enable AutoComplete Cell Values” box under Tools|Options under the Edit tab (Windows) or under Excel|Preferences in the Edit pane (Mac).

Automatic Un-Capitalization.  The default spell correction in Excel changes a word of three or more letters that begins with two capital letters and then small letters so that the second letter is not capitalized.  This makes units such as MPa change to Mpa, which then have to be edited to get the correct spelling.  A number of these common occurrences have been included in both spellings in the list of base units.  For example, MPa and Mpa are both included as the  same units.  This Excel autocorrection can be turned off by deselecting (unchecking) the  “Correct TWo INitial CApitals” box under Tools|Options under the Spelling tab and the “AutoCorrect Options …” button (Windows) or from the Tools|AutoCorrect menu (Mac).

Array Formulas.  The ChangeUnits functions all work as specified above.  However, in some cases, it is desirable to use them as Excel “Array Formulas” so that their output consists of two cells rather than a single cell.  For example, for the Grav () function, it is sometimes convenient to output not just the value 9.806652 but also the underlying units “m/s^2”. 

In fact, this is exactly what ChangeUnits does. The function Grav( ) is an array function which returns 9.806652 in the first cell and  “m / s^2” in the second cell.  If only one cell is selected for the output, only the first output, 9.806652, is outputted.

The functions ToMetric( ), FromMetric( ), Grav( ), and GasConst( ) are all array formulas, returning the value in the first cell and the units in the second cell.  Illustrations of how to use array functions for Examples 3 and 7 above are given in ChangeUnits_Examples.xls at the bottom of the “Examples” worksheet, with the array formulas highlighted in orange.

 

For users unfamiliar with array formulas, their use can require getting used to.  Here are the basics:

1.  To input the function, highlight two adjoining cells.

2.  Type the formula desired.

3.  Hit “Ctrl-Shift-Enter”.

4.  The formula you typed in will now be listed in both cells with curly brackets around it.  The two output values will be displayed in the two cells.

5.  Once you have an array function, you cannot delete only one cell’s value – you must delete both at once by highlighting both.

6.  To update the formula, you must use “Ctrl-Shift-Enter” to complete the change.  Hitting only “Enter” will give an error.

7.  When editing (and sometimes inputting) array functions, it is sometimes necessary to make sure the cursor is located in the formula bar on the top of the Excel window, not in the cell itself, which is where it is when you select a cell and start typing.  If you are having difficulty getting Excel to accept a correction to an array formula, make sure that the cursor is in the formula bar, not the cell.

You can get Microsoft help by searching “array formula”.