# Thread: latitude and longitude (all)

1. ## latitude and longitude (all)

I have latitude and longitude for points A and B. I want to determinte the distance between A and B in excel. Does anyone have the equation?

2. ## Re: latitude and longitude (all)

Chip Pearson has some info

Steve

3. ## Re: latitude and longitude (all)

Great!

I tried it and it does just what I want.

Thanks a lot.

4. ## Re: latitude and longitude (all)

I suspect that the formula presented there, while in common usage, will produce inaccurate results for small distances, largely because of the use of the inverse cosine, which loses all of its significant figures rapidly for small values.

An alternative calculation, which doesn't suffer from this, incorporates the Haversine or half versine function, defined as:
hav(x) =

5. ## Re: latitude and longitude (all)

An alternative formula for use with decimal degrees is:
As with Chip Pearson's formula, you need to use -ve values for the Lat/Long on one side of the equation if you're mixing hemispheres.

Cheers

6. ## Re: latitude and longitude (all)

I hadn't seen that rendition before. I'll give it some study.

There are really two separate issues involved in this. The first is to manipulate the latitudes and longitudes so that their "differences" will actually be a reflection of the smallest arc swept between the two points. This is irrespective of the units used or what hemispheres the points are in, provided their coordinates are initially manipulated appropriately to reflect this "smallest" arc.

The other issue is that using the inverse cos function, while mathematically correct, is susceptible to losing significant figures for small values. A calculation to seven significant figures cannot distinguish the cosines of any distances smaller than about one minute of arc, or 1 (nautical) mile.

A third problem (I now recall) is with Excel's ATAN2 function. This may have been a requirement specific to my own formulation, but I needed a positive radian measure (describing the sweep of the arc) from the ATAN2 function - thus a value between 0 and pi. Excel, however, returns in the range (-pi/2, pi/2). While this is correct (maybe even standard, since it represents a continuous function, unlike my version) it was inappropriate to the needs of my calculations.

Anyway, a few bits & pieces of possible relevance.

Alan

7. ## Re: latitude and longitude (all)

I should have also asked about calculating the _bearing_ between the same 2 points...

8. ## Re: latitude and longitude (all)

You might find something useful at Ed Williams' Aviation page http://williams.best.vwh.net/, where the formulae are set out in his Aviation Formulary and from where you can download excetuables and an Excel spreadsheet that uses UDFs etc he's written for doing this sort of thing.

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•