One of the most common stumbling blocks for people beginning to work with weather data from around the internet and around the world is the myriad of units that you encounter. Often times different data sources use different units for the same weather metric, and you will need to homogenize them if you want to do calculation, comparisons, or even make useful presentations. If you are working with Excel, you are in luck. Excel is the master calculator and will gladly convert units for you as long as you give it the right formulas. In this post, we’ll discuss various conversions commonly required for weather data analysis and how to accomplish them efficiently in Excel.
Temperature
Temperature is a very common conversion that you will encounter when utilizing nearly any type of weather data. Typically, raw weather data sources will give temperature data in degrees Celsius or Kelvin. However, occasionally you will also encounter raw Fahrenheit data as well.
When you encounter Kelvin, you will almost always want to convert to a unit that feels more comfortable both for your own analysis and to other people who may want to view your results. Afterall, who naturally thinks about a nice day being around 300 degrees? Even when you encounter Celsius you will generally find that most US audiences find it easier to think about and understand data in Fahrenheit. Luckily, Excel’s built-in conversion will deftly convert between all three of these options for us. Assuming that you have Kelvin temperature data in cell A1, the conversion function to Fahrenheit is
=CONVERT(A1,"K","F")
The conversion to and from other units can be done with a simple tweak to the formula above. To change the ‘to’ and ‘from’ units, change the K and F in quotes above. The first parameter to the CONVERT function is the input cell or value. The second is the input unit and the third is the desired output unit. You use “K” for Kelvin, “C” for Celsius, and “F” for Fahrenheit.
Precipitation
Precipitation is typically found in the unit of millimeters or inches over the given time period. Often that time period is an hour although sometimes it could be an entire day or even a specific number of hours. So, check your weather source’s documentation carefully. Since millimeters per hour (mm/hr) and inches per hour (in/hr) are the most common options, we’ll discuss those here.
The first thing to notice is that the time component (in this case hours) is the same for both the input and output unit. So, we only need to convert the distance unit. To do this we can again can use Excel’s built-in converter, this time converting between inches and millimeters. To change from mm/hr to in/hr, we can simply apply the formula
=CONVERT(A1,"mm","in")
Since hours is the common unit in both input and output, this will give us our desired in/hr.
Similarly, if we have data in inches an international audience would prefer to see mm/hr, so we just need to invert the conversion above. That would look like this
=CONVERT(A1,"in","mm")
The output will be mm/hr. Although the time unit is typically the same for precipitation, that is not the case for wind speed.
Wind Speed
Windspeed can appear in a variety of units. One of the most common units is meters per second (m/s). However, you will also encounter feet per second (ft/s), miles per hour (mph), kilometers per hour (kph), and knots. With all of these possibilities, conversions can be a bit tricky. In order to make this more simple, we’ll group these into categories.
Feet and meters per second
Since both units have the same time component, we can use our trick from above and simply convert from meters to feet and vice versa. The formula
=CONVERT(A1,"m","ft")
will convert data from m/s to ft/s. And the inverse
=CONVERT(A1,"ft","m")
will take us the other way from ft/s to m/s.
Miles and kilometers per hour
The same is true for miles and kilometers per hour. Since the time unit is the same, a simple unit conversion will work nicely. Hence,
=CONVERT(A1,"km","mi")
will make our US audience happy by taking kph in cell A1 and converting it to mph. And the inverse
=CONVERT(A1,"mi","km")
will make our international viewers more easily understand our data.
Knots
A knot is a unit of speed that that is derived from the way that old-time sailors used to mark their speed by counting physical knots in a rope that is spooled out from a moving ship. The knots were placed just over 47 feet apart, and the timing was performed by an hourglass (sand-glass). The faster the knots moved through a sailor’s fingers, the faster the ship was moving. Amazingly, this system was accurate enough for useful dead-reckoning navigation for hundreds of years.
The modern version of the knot unit has a more-tightly conscribed definition, but the basic unit remains approximately the same and is still tied to the nautical mile. For our purposes, Excel provides a convenient conversion from knots to both mph and kph. To convert from knots to mph, you can use the formula
=CONVERT(A1,"kn","mph")
Likewise, if you would rather see kph, simply use
=CONVERT(A1,"kn","km/h")
If you instead need an output of m/s, you can use
=CONVERT(A1,"kn","m/s")
Oddly, Microsoft has apparently decided not to provide a direct version between knots and ft/s. However, we can easily do the work in a two-step formula. First, we can convert knots to m/s as above and then use the meters to feet conversion since to unit of time is now the same. The nested formula looks like this
=CONVERT(CONVERT(A3,"kn","m/s"), "m", "ft")
Although rare, if you find yourself needing to convert from one of the “per second” or “per hour” units to knots, you can simply reverse the formulas above to achieve your goal.
Summary
In this short entry we’ve gone through the common conversions that one typically sees when working with weather data. Luckily Excel itself can handle many of these, although a bit of cleverness is needed in some cases. If you want more technical details on all of the built-in Excel conversions, you can visit Microsoft’s documentation here.
Another point to consider is that a good weather tool will typically handle these conversions for you automatically. Visual Crossing Weather add-in for Microsoft Excel is such a product. It has the ability to read weather data from sources around the world, understand the native formats and units, and properly convert the data for intelligent comparison and display. This can be a significant time saver as well as helping to avoid accidental mistakes that may result from manual formula entry. Of course, although it is great to have smart software that can save you time and effort, it is always good to understand what is going on “under the hood” and be able to double check the output yourself.