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:

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:

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:

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:

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:

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.

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:

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.

Hi Doug, nice work! As you state you are particularly interested in the US units, you might find the NIST handbook 44 quite interesting, esp. Appendix C: http://www.nist.gov/pml/wmd/pubs/upload/AppC-12-hb44-final.pdf .

Have you thought about or ever heard of the possibilty of calling a computer algebra system from Excel in order to do some manipulations with the units, e.g., combining kg m s-2 to N ??

LikeLike

Hi Georg – I did (briefly) think about writing my own code to parse SI units but soon discovered it wasn’t a trivial task. What the code does do is recognise units in several different formats, so for instance accelleration can be entered as m.s-2, m/s2, m / s^2, or m . s^-2 (it won’t accept m/s/s though). Also it will take accont of prefixes anywhere in a compound unit, so density in Yg.ym-3 should work OK (just checked, 10000 kg/m3 is 1E-89 Yg.ym-3). I’ll write that up in more detail in a later post.

As for linking to a computer algebra system from Excel, that’s one of the things that’s been on my “nice to do when I have a bit of spare time” list for quite some time 🙂

LikeLike

I was looking for a way to feedback a finding that Excel conversion from “gallon” to “liter” is not exact. Error is small (+0.022%). Exact value 1 Gallon = 231 in³ = 3.785411784 liters. Excel conversion: 1 Gallon = 3.78623545651745… I do not understand why if there is such simple way to calculate the exact value, excel produces an error in the third decimal place and even show more decimals to give sense of precision.

Thanks

LikeLike

Jose – what version of Excel are you using? In 2010 the Excel Convert function returns exactly 3.785411784.

My ConvertA function returns 4.54609 l, which is the correct Imperial conversion.

To get the conversion from US fluid gallons using ConvertA the gallon abbreviation is gal(fl), which also returns exactly 3.785411784.

My ToSI functions returns an error for gallons to litres, because the litre is not on the SI list. ToSI returns exactly 3785.411784 for gal(fl) to cm3.

LikeLike

The first Google hit on “excel convert error gallon liter” (without the “”) takes you to

http://office.microsoft.com/en-us/excel-help/convert-measurements-HP003056127.aspx

which is the MS on-line help for Excel 2003. In the examples it gives 6 gallons converts to 22.71741274 l, which is 3.7862354567 /gallon.

I don’t know where that number came from, but it looks like they just got it wrong and corrected it in 2007 or 2010.

LikeLike

Microsoft bug report (acknowledges the problem for XL 2000, but not 2003):

http://support.microsoft.com/kb/283695

(Their suggested workaround is a bit of a joke as well. They suggest using the Convert function then multiplying that value by a correction factor, rather than just doing your own calculation with the correct conversion factor in the first place. Anyway, it seems they have finally fixed it.)

LikeLike

Pingback: Units for Excel 2 – Unit aware evaluation of functions | Newton Excel Bach, not (just) an Excel Blog