About nautical miles, gigabytes and centimeters
- Jul. 18, 2019
 | Example files with this article: |
Introduction
Who of knows the Excel formula =CONVERT( ) ? This function allows us, effortlessly, to convert different units to other units. For example:
- the band Men at Work is singing in their (plagiarized) song "Down Under" about a man in Brussels who was "six-foot-four and full of muscles". How tall is he actually in centimer ?
- 1 terabyte consists of how many kilobytes ?
- how many miles did I run if I ran 6.4 km ?
- what's the speed in km per hour for the world record holder when it comes to sprinting 100 meters ?
- 0 degrees Fahrenheit is how many degrees Kelvin ?
- what do you have to pay in the supermarket for a can of coke containing 330 ml when the price equals 2$ per liter ?
- …
Discussion
The function =CONVERT( ) requires 3 arguments, in the following sequence:
- the value to be converted
- the unit from which you want to convert
- the unit to which you want to convert
Grams are denoted with the symbol g, meter is m, mile is mi, nautical miles is Nmi. There are lots of other units that can be used. I could call them the base units, all because there are still a lot more possibilities. We can combine the base units with powers of 10. Then "cm" stands for centimer, "kg" stands for kilogram, etc. Possibilities (large to small):
| Prefix | Multiplier | Abbreviation |
| yotta | 1E+24 | "Y" |
| zetta | 1E+21 | "Z" |
| exa | 1E+18 | "E" |
| peta | 1E+15 | "P" |
| tera | 1E+12 | "T" |
| giga | 1000000000 | "G" |
| mega | 1000000 | "M" |
| kilo | 1000 | "k" |
| hecto | 100 | "h" |
| dekao | 10 | "da" of "e" |
| deci | 0,1 | "d" |
| centi | 0,01 | "c" |
| milli | 0,001 | "m" |
| micro | 0,000001 | "u" |
| nano | 0,000000001 | "n" |
| pico | 1E-12 | "p" |
| femto | 1E-15 | "f" |
| atto | 1E-18 | "a" |
| zepto | 1E-21 | "z" |
| yocto | 1E-24 | "y" |
Yotta denotes a factor 10^24, yocto is the extreme value on the other end with a factor 10^-24. Scientists will love these symbols to avoid computations with lots of 0s.
We can see giga in the table (symbol G). byte is also another unit with which we can make conversions. Consequently, we can compute the number of bytes in 1 gigabyte as follows:
=CONVERT( 1, "Gbyte", "byte" )
No, not entirely true. This gives us a result of 1 000 000 000, while we know for sure that we do not have a factor of 1000 but rather 1024 between 1 byte and 1 kilobyte, 1 kilobyte and 1 megabyte, 1 megabyte and 1 gigabyte, etc. Is it still possible to use the =CONVERT function, or shall we revert to using powers ? No, it is possible. Powers of 1024 have been made available too (again large to small):
| Binary prefix | Prefix value | Abbreviation | Derived from |
| yobi | 2^80 | "Yi" | yotta |
| zebi | 2^70 | "Zi" | zetta |
| exbi | 2^60 | "Ei" | exa |
| pebi | 2^50 | "Pi" | peta |
| tebi | 2^40 | "Ti" | tera |
| gibi | 2^30 | "Gi" | giga |
| mebi | 2^20 | "Mi" | mega |
| kibi | 2^10 | "ki" | kilo |
"gibi" (with symbol "Gi") is used for gigabytes, "mebi" (with symbol "Mi") is used for megabyte, etc.
=CONVERT( 1, "Mibyte", "byte" ) gives 1 073 741 824 (= 1024^3)
Please note that the units are case sensitive. To convert 1 nautical mile to 1 mile, you do:
=CONVERT( 1, "Nmi", "mi" )
and the next formula gives an error message because "nautical miles" and "mebi" are incompatible:
=CONVERT( 1, "Nmi", "Mi" )
A very swift flashback to Usain Bolt who set the world record in 2009 for the 100 meter sprint. He only needed 9.58 seconds. What was his average speed measured in kilometer per hour ? 100 meter for 9.58 seconds = 10.44 meter/sec.
=CONVERT( 10.44, "m/sec", "km/hr" ) immediately gives us: 37.58 km per hour !
Did you know that, out of the 10 fastest men and 10 fastest women on earth on the 100 meter sprint, 18 originate from either the US, either Jamaica ? Impressive !
Oh yes, our man in Brussels in the Men At Work song is 1.95 meter tall or 195.07 cm ;-)
=CONVERT( 6.4, "ft", "cm" )
The workbook above also contains my conversions for prices. As the formulas are rather generic, please have a detailed look at them. You can easily change $ to €, $/l can also become £/ozm (pond per ons/ounce), etc. If you respect the measurement units within a certain system (weight and mass should not be combined with volume or time for example), a lot is possible !
