Sometimes, you will need to work with geographical coordinates in Excel and/or PowerPoint. This article shows you how to use Excel to work with coordinates and calculate the distances between locations for advanced mapping problems - it is pretty easy with only a handful of formulas.
Granted, calculating direct distances ("as the crow flies") between locations in a firm's financial model may seem a bit unusual. But there are typical cases where this could be of interest for the model users:
- Government subsidies; for example, subsidies granted for off-shore wind farms may depend on the location of the wind energy converters (WECs). For example, this is the case in Germany.
- Distance-based time and fuel estimations in the logistics sector.
Before we dive into the mathematics, let us take a peek at how we want the result to look in Excel:
As you can see, we need two types of inputs:
- The earth's radius. Of course, this is rather a constant, but for the sake of being consistent with best practices, we should avoid hard-coding numbers into formulas.
- The coordinates of the locations we want to include in the analysis. "Lat" stands for "latitude" (measured in degrees North/South), while "Lon" stands for "longitude" (measured in degrees East/West). You may be used to map coordinates like 52° 31' N, 13° 24' E (the map coordinates for Berlin, in this example). That needs to be turned into decimal coordinates. The conversion is easy, just remember that there are 60 minutes (') in a degree and 60 seconds ('') in a minute, and North and East coordinates (N and E) are treated positive, while South and West coordinates (S and W) are treated as negative values. The values shown in the table are already decimal.
Decimal coordinates are easier to use in Excel formulas. To mimick proper coordinates, you can treat them as time values (hours:minutes:seconds), which would theoretically lead to consistent results; however, it is a bit tricky, because Excel likes to force the hour values into positive numbers between 0 and 23. In any case, for the ongoing calculations, we need those decimal coordinates to be turned into radians values, as commonly used in spherical geometry:
The haversine formula
The formula we will use here to get the distances between to points on the map is the so-called haversine formula. It calculates the distances based on the locations' latitudes and longitudes and is commonly used for navigation and mapping purposes. You do not necessarily need to concern yourself too much with the mathematics behind this - but for more insight, you can check out the Wikipedia article.
Here's the formula used in the spreadsheet (this one from cell I5):
This is basically the formula shown in the Wikipedia article, transformed into an Excel formula for our purpose. It includes a VLOOKUP function so that it can be copied across the distances table consistently. This is quite complex at first sight, but again, do not worry about the mathematics.