Microsoft Office Tutorials and References

In Depth Information

• Weight and mass

The CONVERT function requires three arguments: the value that you want to convert, the from-unit, and the to-

unit. For example, if cell A1 contains a distance expressed in miles, use this formula to convert miles to kilo-

meters:

=CONVERT(A1,”mi”,”km”)

The second and third arguments are unit abbreviations, which are listed in the Excel Help system. Some of the

abbreviations are commonly used, but others aren't. And, of course, you must use the
exact
abbreviation. Fur-

thermore, the unit abbreviations are case sensitive, so the following formula returns an error:

=CONVERT(A1,”Mi”,”km”)

The CONVERT function is even more versatile than it seems. When using metric units, you can apply a multi-

plier. In fact, the first example I presented uses a multiplier. The actual unit abbreviation for the third argument

is
m
for meters. I added the kilo-multipler —
k
— to express the result in kilometers.

Sometimes, you need to use a bit of creativity. For example, if you need to convert 100 km/hour into miles/sec,

the formula requires two uses of the CONVERT function:

=CONVERT(100,”km”,”mi”)/CONVERT(1,”hr”,”sec”)

The CONVERT function has been significantly enhanced in Excel 2013 and includes

dozens of new units.

Figure 10-1 shows a conversion table for area measurements. The CONVERT argument codes are in column B

and duplicated in row 3. Cell A1 contains a value. The formula in cell C4, which was copied down and across is

=CONVERT($A$1,$B4,C$3)

The table shows, for example, that 1 square foot is equal to 144 square inches. It also shows that one square

light year can hold a
lot
of acres.

By the way, this formula is also a good example of when to use an absolute reference and mixed references in a

formula.