Converting measurements from one system to another can be done by Excel using the CONVERT function … here’s how it works and then we’ll tell you what CONVERT can’t do or should do better.
As usual, we decided to write about an Office feature thinking it will be straight-forward only to find curious anomalies and problems even in such a supposedly mature product like Excel.
CONVERT() lets you change a measurement in one scale to another scale using in-built conversion factors. Most commonly this is used for imperial/US <> metric conversions but can also be used to convert between scales of the same type (feet to miles or seconds to hours).
The basic format is this:
CONVERT(Number, From_unit, To_unit)
Number is the incoming value – probably a reference to another cell.
From_unit is the measurement scale that applies to the number. It’s a text value that can be typed into the formula or referenced from another cell.
To_unit is the measurement you want converted to. It’s a text value that can be typed into the formula or referenced from another cell.
Sounds imposing but here are some simple and common examples.
=CONVERT(100,"mi","km") converts 100 miles into kilometers
=CONVERT(25,"C","F") converts 25° Celsius into Fahrenheit .
=CONVERT(100,"oz","l") converts 100 fluid ounces (oz) into liters.
Of course any of these are reversible for example:
=CONVERT(100, "km", "mi") converts 100 kilometres into miles
CONVERT has a long list of measurement units to choose from. We’ve listed them in a separate table.
In addition to the basic units, there are also multiplier options which apply mostly to the metric measurements. For example ‘m’ is the abbreviation for metre and ‘k’ is the multiplier prefix for ‘kilo’ or x 1000 – put the two together to get ‘km’ the CONVERT abbreviation for kilometre (also the standard abbreviation, the metric system is wonderfully consistent).
Even with the multipliers, the convert function list of measurements is by no means complete as we’ll see later.
Such a powerful but flexible function means there’s plenty of scope for formula mistakes.
A common one is forgetting the quote marks around the “to” and “from” units.
For example =CONVERT(51234,sec,hr) looks like it will convert 51,234 seconds into hours but it won’t – the double-quotes are missing. =CONVERT(51234,"sec","hr") is correct.
Another mistake is to try converting mismatching measurements.
=CONVERT(100,"tsp","c") will get a #N/A result because it’s trying to convert a 100 teaspoons of liquid measure into a thermodynamic calorie (quite a different animal). Mostly likely it should be =CONVERT(100,"tsp","cup").
Some of the abbreviations aren’t what you expect. ‘oz’ isn’t ounces it is fluid ounces. For ounces in weight/mass use ‘ozm’.
All measurement ‘to’ and ‘from’ units are case-sensitive.
To avoid many of these errors, Autocomplete can help you type in the right matching conversion factor – it can limit the to_unit suggestions to the ones applicable for the from_unit but not always.
If you type in “mi” as the ‘from’ unit, Autocomplete will show the applicable distance measures for the ‘to’ unit:
However it doesn’t suggest a common measurement unit like ‘km’ because that’s not an inbuilt measurement, only a construct made using the multiplier ‘k’ prefix. That’s a pity since ‘km’ for kilometre is commonly used.
Autocomplete is strangely inconsistent, at least in our Excel 2010 tests. Entering “C” for Celsius should give you a small list of temperature scales for the ‘to’ unit but instead shows the entire list of measurements – most of which can’t possibly be appropriate and will reward you with a cell error.
You have to scroll down to find the temperature measurement units.
Article posted: Monday, 28 November 2011
there's more ...
If you liked this article you'll LOVE our new ebooks.
Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8
A focused and unvarnished look at Windows 8, especially written for
the many people who use Microsoft Office Get it today
- click here.
ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox
100+ pages of practical tips and help to streamline,
automate and search your Inbox. Get more
than you ever thought possible from Outlook. Read it today
- click here.
More from Office Watch: