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 ofnumberto_unit— the unit to convertnumberto
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.
