One of the reasons that programs like MathCad tend to be preferred to Excel for engineering and scientific applications is that they have much better facilities for dealing with different units of measurement.  This isn’t difficult, since the only built-in function provided by Excel has a very limited range of units, and does not deal with compound units at all, even for something as basic as measures of area or acceleration.

Looking for a spreadsheet or add-in that would provide better functionality my requirements are:

• Provision of User Defined Functions (UDF’s) for unit conversion, so that the conversion may be easily applied to tabular data anywhere in any spreadsheet.
• A wide range of output units, including all “customary units” used for engineering applications in the USA.
• Editable unit data tables so that new units may be added or corrected.
• Output of converted data in array form, so that the data may be easily used in other UDF’s.
• Preferably free and open source.

None of the existing unit conversion spreadsheets that I’m aware of meet all these requirements (most don’t even come close), so I decided to write my own.

The resulting spreadsheets may be downloaded from Units4Excel.zip.  The download file includes two spreadsheets (both with full open-source code).  Units4Excel.xlsb is a stand-alone spreadsheet including documentation and examples of each function.  Units4Excel.xlam is an add-in file, allowing the functions to be accessed more easily from any other spreadsheet; see below for installation instructions.

The UDF’s included in the spreadsheets are shown in the screenshot below:

Unit Conversion Functions

The functions ToSI and FromSI are for use when either all of the destination units or all of the source units are recognised SI basic or derived units. When non-SI units are included in both the source and destination list the slower ConvertA function must be used. Each of these functions comes in two versions; if the destination units are listed next to the output range then the basic form of the functions may be used, and it is only necessary to specify the two column range listing the source values and their units. If the list of output units is located anywhere else on the spreadsheet it is necessary to use the second version of the functions, and specify both the source data and the list of output units.

All of these functions may be either used on a single row, two column range of input values, or may be used with a multi-row range, in which case the function must be entered as an array function:

• Enter the function
• Select the entire extent of the output range
• Press F2 (edit)
• Press Ctrl-Shift-Enter

See https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/ for more details.

Examples of the use of these functions are shown in the screenshots below:

ToSI and FromSI Functions

ToSI2, FromSI2, ConvertA and ConvertA2 functions

The functions shown above are for use where the source data is listed in two columns (values and units), and the destination units are listed in a single column. In cases where a tabular output is required, with source data listed horizontally across the top of the table, and destination units listed down the left hand side, the ConvertTab function may be used, as shown below:

ConvertTab Function

For all these functions it is important to use the correct name and spelling of the units, and correct case for the abbreviations. These are documented by the ListSI and ListNonSI functions, which provide a list of unit names and abbreviations, and also definitions and conversion factors for the non-SI units. If the “Unit Type” parameter is omitted the functions will return a complete list of all unit names. If the unit type is specified only units of that type will be returned, as shown below:

ListSI and ListNonSI functions

All the functions listed above will recognise all the units in the NonSI unit list (currently 344 items), and also all the recognised compound SI units, such as area, velocity and acceleration. The function QConvert is a front end for the Excel built-in Convert function, and will only recognise the limited list of units included in the Convert list. It does however allow use as an array function, and provides more informative error messages in the case of unrecognised or incompatible units. The screenshot below shows the output of QConvert compared with the buit-in Convert function and the ToSI UDF:

QConvert function

The spreadsheet Units4Excel.xlsb lists all the units recognised by the UDF’s together with abbreviations, definitions, and conversion values. This table has been copied from Wikipedia at: http://en.wikipedia.org/wiki/Conversion_of_units

Note that in many cases there are significant differences between definitions of the same unit used in different countries or different applications.  The table in the spreadsheet (as shown below) gives a summary of the definitions, but refer to the Wikipedia table for more details and references.

NonSI Unit List

In cases of multiple definitions one default (shortest) abbreviation has been selected for one definition (usually the one with the widest international use).  It is possible to edit the abbreviations, but it must be ensured that each definition with a different conversion factor has its own unique abbreviation.

The Units4Excel.xlsb spreadsheet provides documentation and examples of all the UDF’s, as well as a full list of all units and their definitions; however for use of the functions in other spreadsheets an add-in is more convenient, and for this reason the add-in version Units4Excel.xlam has been provided.

To install the add-in:

• Copy Units4Excel.xlam to any convenient folder
• Open Excel and select: File – Options – Add-ins
• Check that the “Manage” drop-down box displays Excel Add-ins and click “Go”
• Click “Browse” and select the folder containing the add-in
• The add-in will appear on the list of Add-ins available; ensure that the check box is ticked.

All the UDFs described above will now be available for use in other spreadsheets, as illustrated below:

Unit Functions used in RC analysis spreadsheet

Finally note that this is version 0.0 and has only had limited testing.  As always, any results found using this spreadsheet must be independently verified.