Distances as the crow flies: the solution

Yesterday I posted about joyful Tuesday, a day I spent lost in Excel, data and maps.  And as requested by @lesteph, here’s the solution I developed.  I’ve sanitised it, but the principles still stand.

The spreadsheet takes six places: Edinburgh, Bristol, Cambridge, London, Cardiff and Halifax.  It shows their latitude and longitude and uses the following formula to calculate the distance in kilometres to one decimal place between each pairing.

=ROUNDDOWN(6371.0072*ACOS(COS(RADIANS(90-lat_from))*COS(RADIANS(90-lat_to))+SIN(RADIANS(90-lat_from))*SIN(RADIANS(90-lat_to))*COS(RADIANS(long_from-long_to))),1)

lat_from is the latitude of the “from” location, and you can guess the other three such references.  The references in the spreadsheet itself are cell references to allow easy copy/paste action.

Conditional formatting hides the lower half of the square, and emphasises those places that are closest together.

I made the schoolboy error of hardcoding the earth’s radius (6,371.0072km), meaning that the model does not scale to other planets or moons in our solar system.  Apologies.

July 1, 2010 | Filed Under Numbers and stuff, Spreadsheets 

Comments

Leave a Reply