Share via

Distance between latitude and longitude points

Dom 776 Reputation points
2023-09-18T16:46:22.23+00:00

I have a listing of latitude and longitude for all US zip code centroids. I want to find the distance between two zip codes using a simple formula in Excel. I have seen this formula in several forums:

=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371 [this is for km]

This does not come remotely close (tells me that the town 4 miles away is 219 km!)

This formula does seem to work:

=ACOS(COS(RADIANS(90-Lat1)) * COS(RADIANS(90-Lat2)) + SIN(RADIANS(90-Lat1)) * SIN(RADIANS(90-Lat2)) * COS(RADIANS(Long1-Long2))) * 6371

Again, I have found that first formula posted many times. Is there a typo in there somewhere or am I reading something wrong?

Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
4,686 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,808 questions
0 comments
Accepted answer
  1. Vahid Ghafarpour 21,080 Reputation points
    2023-09-18T17:24:00.72+00:00

    Ensure that your latitude and longitude values are in the correct units (degrees for the second formula or radians for the first one) for the expression you are using, and the procedure should work as expected.

    Can you provide sample data that does not work?

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.