How to calculate the distance between two addresses in Excel
By
SpreadCheaters
London and Bristol, the two major cities of England, have the latitude and longitude given in the image below.
Understanding the formula
– The formula for the distance requires manipulation of the latitude and longitude of the addresses that we are trying to measure the distance between.
– The formula uses the mathematical law known as the law of cosines shown below.
ACOS(SIN(LAT1)*SIN(LAT2) + COS(LAT1)*COS(LAT2)*COS(LON2 – LON1)*R
– In this formula LAT1 and LON1 are the latitude and longitudes of the first address in radians while LAT2 and LON2 are those of the second address.
– R is the radius of earth.
– To implement the formula we have to keep in mind that the data for latitude and longitude in the worksheet is in degrees so every time we are going to write the cell reference for that particular coordinate we are going to multiply it by the constant pi (3.14159) and divide by 180 to convert coordinate measure to radians. This is because the sine and cosine functions in excel require angle inputs in radians.
– In excel the constant pi can be called with the function PI() so every cell reference will be written with the multiplier PI()/180.
– The radius of earth in our example is 6371 kilometers.
While working with addresses one might find the need to calculate the distance between them. If you have the latitude and longitude coordinates available for a given pair of addresses, it can be done very easily. So let’s find out.
Step 1 – Implement the formula
– So select the cell where you want the calculation to go and type in the formula starting with the equal sign as follows,
=ACOS( SIN( B2 * PI() / 180 ) * SIN( B3 * PI() / 180 ) + COS( B2 * PI() / 180) *
COS( B3 * PI() / 180) *COS( B3 * PI() / 180 – B2 * PI() / 180 ) ) * 6371
– The above formula is presented in multiple lines for understanding and may be written without line feeds.
– Once the formula has been typed press enter on the keyboard to view the calculation.
– So the approximate distance between London and Bristol is 170.47 km.
