Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
How?
Syntax
CONVERT(number,from_unit,to_unit)
Number is the value in from_units to convert.
From_unit is the units for number.
To_unit is the units for the result. CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.
| WEIGHT AND MASS | FROM_UNIT OR TO_UNIT |
|---|
| Gram | "g" |
| Slug | "sg" |
| Pound mass (avoirdupois) | "lbm" |
| U (atomic mass unit) | "u" |
| Ounce mass (avoirdupois) | "ozm" |
| DISTANCE | FROM_UNIT OR TO_UNIT |
|---|
| Meter | "m" |
| Statute mile | "mi" |
| Nautical mile | "Nmi" |
| Inch | "in" |
| Foot | "ft" |
| Yard | "yd" |
| Angstrom | "ang" |
| Pica (1/72 in.) | "Pica" |
| TIME | FROM_UNIT OR TO_UNIT |
|---|
| Year | "yr" |
| Day | "day" |
| Hour | "hr" |
| Minute | "mn" |
| Second | "sec" |
| PRESSURE | FROM_UNIT OR TO_UNIT |
|---|
| Pascal | "Pa" (or "p") |
| Atmosphere | "atm" (or "at") |
| mm of Mercury | "mmHg" |
| FORCE | FROM_UNIT OR TO_UNIT |
|---|
| Newton | "N" |
| Dyne | "dyn" (or "dy") |
| Pound force | "lbf" |
| ENERGY | FROM_UNIT OR TO_UNIT |
|---|
| Joule | "J" |
| Erg | "e" |
| Thermodynamic calorie | "c" |
| IT calorie | "cal" |
| Electron volt | "eV" (or "ev") |
| Horsepower-hour | "HPh" (or "hh") |
| Watt-hour | "Wh" (or "wh") |
| Foot-pound | "flb" |
| BTU | "BTU" (or "btu") |
| POWER | FROM_UNIT OR TO_UNIT |
|---|
| Horsepower | "HP" (or "h") |
| Watt | "W" (or "w") |
| MAGNETISM | FROM_UNIT OR TO_UNIT |
|---|
| Tesla | "T" |
| Gauss | "ga" |
| TEMPERATURE | FROM_UNIT OR TO_UNIT |
|---|
| Degree Celsius | "C" (or "cel") |
| Degree Fahrenheit | "F" (or "fah") |
| Kelvin | "K" (or "kel") |
| LIQUID MEASURE | FROM_UNIT OR TO_UNIT |
|---|
| Teaspoon | "tsp" |
| Tablespoon | "tbs" |
| Fluid ounce | "oz" |
| Cup | "cup" |
| U.S. pint | "pt" (or "us_pt") |
| U.K. pint | "uk_pt" |
| Quart | "qt" |
| Gallon | "gal" |
| Liter | "l" (or "lt") |
The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit.
| PREFIX | MULTIPLIER | ABBREVIATION |
|---|
| exa | 1E+18 | "E" |
| peta | 1E+15 | "P" |
| tera | 1E+12 | "T" |
| giga | 1E+09 | "G" |
| mega | 1E+06 | "M" |
| kilo | 1E+03 | "k" |
| hecto | 1E+02 | "h" |
| dekao | 1E+01 | "e" |
| deci | 1E-01 | "d" |
| centi | 1E-02 | "c" |
| milli | 1E-03 | "m" |
| micro | 1E-06 | "u" |
| nano | 1E-09 | "n" |
| pico | 1E-12 | "p" |
| femto | 1E-15 | "f" |
| atto | 1E-18 | "a" |
Remarks
- If the input data types are incorrect, CONVERT returns the #VALUE! error value.
- If the unit does not exist, CONVERT returns the #N/A error value.
- If the unit does not support an abbreviated unit prefix, CONVERT returns the #N/A error value.
- If the units are in different groups, CONVERT returns the #N/A error value.
- Unit names and prefixes are case-sensitive.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
| | A | B |
|---|
| Formula | Description (Result) | | =CONVERT(1.0, "lbm", "kg") | Converts 1 pound mass to kilograms (0.453592) | | =CONVERT(68, "F", "C") | Converts 68 degrees Fahrenheit to Celsius (20) | | =CONVERT(2.5, "ft", "sec") | Data types are not the same so an error is returned (#N/A) | | =CONVERT(CONVERT(100,"ft","m"),"ft","m") | Converts 100 square feet into square meters (9.290304). |
|
Comments
Post a Comment