Distance between latitude and longitude points

Dom 941 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 and Office Install, redeem, activate For business Windows
Microsoft 365 and Office Excel For business Windows
0 comments No comments
{count} votes

Accepted answer
  1. Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator
    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

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.