Excel: How to calculate distances between longitude / latitude co-ordinates

Here's a blast from excel past, it was actually one of the more interesting projects I worked on. We were designing food waste recycling plants for the food manufacture and retail industries. The task was to calculate the most efficient solution ofhow many recycling plants to build and where to build them based on the waste arising of each shop and factory vs the cost to transport between the sites.

The finished algorythm wasn't that complicated, and I ran it for one, two, three four and five plant solutions. One of the bits of information that the work was predicated on was the sitings of each shop/factory and although I had the postcode my presumption was that converting them to x and y co-ordinates would be easy. Well, to cut a long story short it wasn't - I quite quickly found a lookup list of postcodes to latitude and longitude and after that it would just be a simple bit of triganometry and pythag right? Wrong!

Turns out converting lat/long to east/north is a complete PITA! Thankfully the guys at Ordnance Survey have the answer in their everything you need to know guide:

A guide to coordinate calculations (mirror)

Did that clear it up for you? Me neither,, the formula is a whopper:

Fortunately they make our lives a little easier by providing an Excel spreadsheet showing us how to calculate it and a whole lot of other stuff, yeah!

Coordinate calculation sheet(XLS) (mirror)

It's a little detailed so if you open it and head over to the [lat,long to E,N] tab you see the custom function helpfully spitting out the answer.

=Lat_Long_to_East(DecimalLat,DecimalLong,a,b,E0,F0,DecimalPHI0,DecimalLAM0)
=Lat_Long_to_North(DecimalLat,DecimalLong,a,b,E0,N0,F0,DecimalPHI0,DecimalLAM0)

Fortunately it is not all that complicated, most of the arguments are constant so we are just left with:

=Lat_Long_to_East(DecimalLat,DecimalLong,6377563.396,6356256.909,400000,0.9996012717,49,-2)
=Lat_Long_to_North(DecimalLat,DecimalLong,6377563.396,6356256.909,400000,-100000,0.9996012717,49,-2)

Simples, now all you need to do is copy the macro module over to your spreadsheet and you are good to go.