CONVERT Function
Whilst most of the world has adopted the metric measurement system, three countries officially prefer imperial units: USA, Liberia, and Myanmar. The UK, however, is caught between the two — and an imperial ‘hangover’ still exists in other Commonwealth nations such as Canada, India, South Africa, and Australia.
If you have a dataset containing imperial (or metric) data — out of necessity or convenience — you might want to convert it. Luckily, Excel has a versatile function for this purpose.
CONVERT converts a number from one measurement system to another.
Syntax
= CONVERT ( number , from_unit , to_unit )
number
— the value to convertfrom_unit
— the current unit ofnumber
to_unit
— the unit to convertnumber
to
Both from_unit
and to_unit
require a measurement code enclosed in quotation marks.
Example
A table named tblPlayers holds baseball player data. The heights and weights are in imperial units but have been converted outside the table.
For height, the Height (in) column is referenced in CONVERT’s number
argument. The from_unit
is “in” for inches and the to_unit
is “cm” for centimetres.
Similarly, for weight, Weight (lbs) is the referenced column, the from_unit
is “lbm” for pound mass, and the to_unit
is “kg” for kilograms.
CONVERT is one of several functions that aren’t fully compatible with dynamic arrays. That’s why the double hyphen (–) preceding the table reference is necessary to spill the results. If omitted, a #VALUE! error occurs.
To guarantee whole numbers are returned, nest the CONVERT statement inside ROUND and round to zero decimal places.