A to Z of Excel Functions: The CONVERT Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the CONVERT function.

The CONVERT function

Are you a CONVERT?  This function converts a number from one measurement system to another.  For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.

The CONVERT function employs the following syntax to operate:

The CONVERT function has the following arguments:

• number: this is the value in from_units to convert
• from_unit: this represents the units for number
• to_unit: this is the units for the result.  CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.

We often encounter financial models and spreadsheets falling over due to the significant prevalence of hard code.  One common reason is due to analysts converting data from one measure or unit to another (e.g. from kilograms to parsecs, or something like that!).

Some modellers will go the extra mile (pun intended) by having a reference section where conversion factors are stored and referenced, e.g.

The problem with this approach is that figures may be transposed, typed over or stated the wrong way round (e.g. a common conversion error we found was tonnes in a ton).  These numbers are constant – and there is another way.

If you are not familiar with this function already, let us introduce you to the CONVERT function:

Since Excel 2007 and the advent of Formula AutoComplete, this function has really come into its own. For example, to generate / confirm the first conversion factor, simply use 1,000 for the number and locate “meters” (sic)

Then, after selecting the appropriate ‘from’ unit, the list for the third argument will automatically filter for meaningful conversion units (e.g. “kg” will not appear):

Simple!

The only criticism we have is that the units do not appear to be in any logical order (e.g. alphabetical), but, unlike U2, you can usually find what you’re looking for.

Here’s the full list of “disciples” (i.e. “converts” – get it?):

It should be further noted that:

• if the input data types are incorrect, CONVERT returns the #VALUE! error value
• if the unit does not exist, CONVERT returns the #N/A error value
• if the unit does not support a binary prefix, CONVERT returns the #N/A error value
• if the units are in different groups, CONVERT returns the #N/A error value
• unit names and prefixes are case-sensitive.

Please see my example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.