Calculate Distance in Excel
How do you calculate distance? In the small town where I grew up, distance was measured in blocks or travel time. For example, my school was about 5 blocks away (much further in the winter!) and my grandparents lived 5 minutes from our house – or 6 minutes during rush hour.
Sometimes you need more precise measurements, and Excel MVP, Jerry Latham, has an Excel user defined function that will help you. It’s designed to calculate accurate measurements, based on the longitude and latitude of your start and end points.
Why a user defined function? Unfortunately, an Excel worksheet formula isn’t accurate enough, if you need precise distances. Jerry used to work in air traffic control, and he explains the problem with “almost” accurate worksheet formulas:
Typically they are short by some number of meters, typically about 20 to 30 feet per statute mile, and after flying just 30 or 40 miles, I wouldn’t care to land several hundred feet short of the approach end of a runway, much less be off by over 7 miles on a trip between Los Angeles and Honolulu.
Excel User Defined Function
On the Excel Latitude and Longitude Calculations page, Jerry outlines the problems with calculating distance in Excel, and describes his challenges in creating a solution.
You can read about Jerry’s journey to the final distance calculation solution, and copy Jerry’s Longitude and Latitude code to your own workbook. Or, download Jerry’s sample file, and work with the code and worksheet examples there.
If you have questions or comments, please let Jerry know. You can post a comment below, or send him an email at HelpFrom@JLathamSite.com