The Haversine Equation in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Use the Haversine equation to calculate the distance between two points on a globe.

The Haversine Equation is one of the basic equations for ocean navigation; it is used to generate the distance between two points on a sphere if you know the latitude and longitude of both points and the diameter of the sphere. It has largely been automated into GPS units. Some people who do geocaching like to have this equation loaded on their local copy of Excel, then they can simply enter their starting point and ending point to determine the distance they must travel.

Advertisement

Setting up the Variables

Video of the Day

The Haversine equation requires four input variables for latitude and longitude. To set this up in Excel, you'll name certain cells in Excel and refer to those cell names in the formula. You can name a cell in Excel by clicking on the cell and then typing the name in the white space to the left of the formula bar. Click on cell "A1" and assign it the name "Latitude_1." Click on cell "A2" and assign it the name "Latitude_2." Click on cell "B1" and assign it the name "Longitude_1." Click on cell "B2" and assign it the name "Longitude_2." It's a good idea to change the colors of these cells so that you can identify them later.

Advertisement

Video of the Day

Setting up the Constants

The other part of the Haversine equation is the radius of the sphere being navigated. Usually, this is the Earth. As with the latitude and longitude numbers above, name the cells that contain these variables. Click on "C1" and name it "RadMiles," click on cell "C2" and name it "RadKilom". In cell C1, enter "3959"; in cell C2, enter "6371." In cell C3, set up Data Validation by clicking on the "Data" part of the ribbon interface and selecting "Data Validation." Select "List" as the type of validation and enter "Miles,Kilometers" in the space provided, without the quotation marks. Cell C3 will become a drop-down list with two entries: Miles and Kilometers.

Advertisement

Entering the Formula

Enter the following formula in cell C4:

Advertisement

=ACOS(COS(RADIANS(90-Latitude_1)) COS(RADIANS(90-Latitude_2)) +SIN(RADIANS(90-Latitude_1)) SIN(RADIANS(90-Latitude_2)) COS(RADIANS(Longitude_1-Longitude2)) IF(C3="Miles",RadMiles,RadKilom)

Advertisement

Using the Haversine Equation

To use the spreadsheet containing the Haversine equation, enter the latitude and longitude of your origination point in cells A1 and B1, in degrees. Then enter the latitude and longitude of your destination point in cells A2 and B2, also in degrees. Select the units in which you want to measure the distance between the points by selecting the drop-down in cell C3 (either miles or kilometers). Cell C4 will show the distance between the two points.

Advertisement

Advertisement

references