Converting Latitude/longitude to OSGB grid ref

[Greenspan]

Hi,

I'm very new to using Excel for programming etc, so there may well be an easy fix for my problem.

I'm using excel to try and sort by distance a list of wind farm locations from a location i put in.

This should be relitavely straight forward, however there are a few elements that make it more complicated.

I am getting a list of wind farm locations from the internet, the BWEA site. The latitude and longitude data copied over is in text. I'm using Excel '07 but it still requires me to butcher the numbers out of it with several columns of REPLACE formulas, it isn't elegant and may well be wrong, so a better way of doing this would help a lot.

The second and harder problem is that I have to first convert the latitude and lonitude values into OSGB grid references. So far i have found the OS maps excel converter, but it only converts one value at a time. I need something that will convert all of them at once (i don't want to go through and individually type in each distance).

After that it is possible to use basic trigonometry to find the distance between them and any grid reference i put in.

Cheers for any help you can give

Tom

[Greenspan]

I have spent this afternoon producing a reduced version of the spreadsheet that hopefully doesn't need a macro - However it means the formulaes involved are exceptionally long and complicated.

What is more annoying is that it still doesn't come put with the right answer!! -And I don't know why, only that there is an error before the column named 'v' (column z). It may be a simple error (equivalent to looking for my glasses when they're on my head) But i can't seem to spot it.

The helpful guide i am using is from the OS website - the Pdf. Appendix C is what i am trying to do!

Again any help is much appreciated.

Cheers

T

[shg]

I'll look at this later today if no one else solves it in the meanwhile.

The goal is just to convert WGS-84 lat/long coordinates (received as d m s in a space-delimited string) to OSGB coordinates?

[Greenspan]

Hi,

Yup basically that's it! - however, it's a lot harder than it first appears (or so i've found!)

Thanks a lot for havinga look at it.

T

[shg]

It's the first section in App C, correct?

Does the worked example use the same phi0 and lambda0 (49 and -2 respectively) that you show in the spreadsheet?

[shg]

For the worked example, the function returns the Easting exactly in accordance with the worked example, and the Northing with a 0.001% error. I don't see a mistake in the formulas, but did not look carefully.

The UDF requires a two-cell wide array formula. Usage is

=WGS84ToOSGB(lat, lon) (arguments in radians, north and east positive)

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

[vlam]

This is what I am looking for also. I am a newbie in excel formulas and all looks really difficult to me. I will try to use the formula shg provided and see if it works on mine lat/long lists I have got. Many thanks in advance.

[Allymcnab]

Hi Guys
Very new to excel, but here goes.
Is there a way of batch converting Lat/Long to OS grids with alpha numerical,10 digit (55.920860,-4.402973 is NS49857 72357)

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread