Chapter 10: Miscellaneous Calculations

 Download CD Content

This chapter contains reference information that may be useful to you at some point. Consider it a cheat sheet to help you remember the stuff you may have learned but have long since forgotten.

Unit Conversions

You know the distance from New York to London in miles, but your European office needs the numbers in kilometers. What's the conversion factor?

Excel's CONVERT function can convert between a variety of measurements in the following categories:

• Weight and mass

• Distance

• Time

• Pressure

• Force

• Energy

• Power

• Magnetism

• Temperature

• Liquid measures

New 

In previous versions of Excel, the CONVERT function required the Analysis TookPak add- in. In Excel 2007, this useful function is built in.

The CONVERT function requires three arguments: the value to be converted, 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 kilometers:

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

The second and third arguments are unit abbreviations, which are listed in the Help system. Some of the abbreviations are commonly used, but others aren't. And, of course, you must use the exact abbreviation. Furthermore, 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 multiplier. 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.

In some situations, the CONVERT function requires some creativity. For example, what if you need to convert ten square yards to square feet? Neither of these units are available, but the following formula will do the job:

 =CONVERT(CONVERT(10,"yd","ft"),"yd","ft") 

The nested instance of CONVERT converts ten yards into feet, and this result (30) is used as the first argument of the outer instance of the function. Similarly, to convert ten cubic yards into unit cubic feet, use this formula:

 =CONVERT(CONVERT(CONVERT(10,"yd","ft"),"yd","ft"),"yd","ft") 

Need to Convert Other Units?

The CONVERT function, of course, doesn't handle every possible unit conversion. To calculate other unit conversions, you need to find the appropriate conversion factor. The Internet is a good source for such information. Use any Web search engine and enter search terms that correspond to the units you use. Likely, you'll find the information you need.

Also, you can download a copy of Josh Madison's popular (and free) Convert software. This excellent program can handle just about any conceivable unit conversion you throw at it. The URL is as follows:

http://www.joshmadison.com/software

On the CD 

The companion CD-ROM includes a workbook named  unit conversion tables.xlsx that contains conversion factors for a number of units. This workbook uses hard-coded conversion factors and does not use the CONVERT function.