The Haversine Equation in Excel
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.
Setting up the Variables
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.
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.
Entering the Formula
Enter the following formula in cell C4:=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)
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.