Spreadsheet: 120371d1315243153-latitude-and-longitude-to-eastings-and-northings-projectionandtransformationcalculations
Sheet: UD Funcs Tranformation Example

This sheet gives an example of how the user defined functions stored in this workbook can be used.
To see the Visual Basic code of the functions, menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1".
A WGS84 lat, long and height (e.g. from a handheld GPS receiver) is converted and transformed in stages to an OSGB36 easting, northing and approx ODN height.
The reverse transformation from OSGB36 to WGS84 is then performed.
The results of each stage are all produced by user defined functions in single cells (i.e. there are no intermediate steps done in other cells).
The outputs of the previous stage are passed as inputs to the next stage.  Other parameters used are on the right of each stage.
Note : be aware when changing the layout of this sheet that all cell references are ABSOLUTE.  This is so that function inputs can be easily identified.
WGS 84 to OSGB36
[NAME], longitude and Ellipsoidal height to WGS84 [NAME]
 |  | ° | ' | " | [NAME] |  | User inputs DMS and Height. Decimal degrees and
latitude | N | 52 | 39 | 28.723003 | 52.65797861194444 | Input | height are the input to the user defined functions.
longitude | E | 1 | 42 | 57.787253 | 1.716052014722222 |  | The DMS values are converted to decimal using
Ellipsoidal height | 69.391 | m |  |  |  |  | standard Excel functions
X | 3875311.464604478 | m |  |  |  | Output | Ellipsoid parameters (WGS84)
Y | 116103.2338431737 | m |  |  |  |  | a = | 6378137
Z | 5047602.2912816 | m |  |  |  |  | b = | 6356752.3142
Helmert Datum Transformation (WGS84 to OSGB36)
 |  |  |  |  |  |  | Transformation (WGS84 to OSGB36)
X | 3875311.464604478 | m | Input |  |  |  | translation parallel to X = | -446.448
Y | 116103.2338431737 | m |  | See the Transformation Notes on the |  |  | translation parallel to Y = | 125.157
Z | 5047602.2912816 | m |  | "Constants" page for information on the |  |  | translation parallel to Z = | -542.06
X | 3874938.848963902 | m | Output | limitations and accuracy of this |  |  | scale change = | 20.4894
Y | 116218.6239344141 | m |  | transformation. |  |  | rotation about X = | -0.1502
Z | 5047168.209724677 | m |  |  |  |  | rotation about Y = | -0.247
 |  |  |  |  |  |  | rotation about Z = | -0.8421
OSGB36 [NAME] to OSGB36 Latitude, longitude and approx ODN height
 |  |  |  |  |  |  | The decimal degrees and height are the output of
X | 3874938.848963902 | m |  |  |  | Input | the user defined functions.
Y | 116218.6239344141 | m |  |  |  |  | The decimal values are converted to DMS using
Z | 5047168.209724677 | m |  |  |  |  | standard Excel functions
 |  | ° | ' | " | [NAME] |  | Ellipsoid parameters (OSGB36)
latitude | N | 52 | 39 | 27.25313465541694 | 52.65757031518206 | Output | a = | 6377563.396
longitude | E | 1 | 43 | 4.517709896204906 | 1.717921586082279 |  | b = | 6356256.91
~ODN height | 24.70085338316858 | m
OSGB36 Latitude and longitude to OSGB36 easting and northing
 |  | ° | ' | " | [NAME] |  | The decimal degrees are the input to the
latitude | N | 52 | 39 | 27.25313465541694 | 52.65757031518206 | Input | user defined functions.
longitude | E | 1 | 43 | 4.517709896204906 | 1.717921586082279 |  | The decimal values are converted to DMS using
Easting | 651409.9030687253 | m |  |  |  | Output | standard Excel functions
Northing | 313177.2714227744 | m |  |  |  |  | Ellipsoid and projection parameters (OSGB36)
 |  |  |  |  |  |  | a = | 6377563.396
 |  |  |  |  |  |  | b = | 6356256.91
 |  |  |  |  |  |  | f0 = | 0.9996012717
 |  |  |  |  |  |  | e0 = | 400000
 |  |  |  |  |  |  | n0 = | -100000
 |  |  |  |  |  |  | j0 = | 49
 |  |  |  |  |  |  | [NAME] | -2
OSGB36 to WGS84
OSGB36 easting and northing to OSGB36 latitude and longitude
 |  |  |  |  |  |  | The eastings, northings and approx ODN height are
easting | 651409.9031284967 | m |  |  |  | Input | input by the user. User defined functions convert
northing | 313177.2702654498 | m |  |  |  |  | them to dec degrees. The decimal degrees are
 |  | ° | ' | " | [NAME] |  | converted to DMS using standard Excel functions
latitude | N | 52 | 39 | 27.25309488052153 | 52.65757030413348 | Output | Ellipsoid and projection parameters (OSGB36)
longitude | E | 1 | 43 | 4.517699167315186 | 1.717921583102032 |  | a = | 6377563.396
~ODN height | 24.70009583048522 | m |  |  |  |  | b = | 6356256.91
 |  |  |  |  |  |  | f0 = | 0.9996012717
 |  |  |  |  |  |  | e0 = | 400000
 |  |  |  |  |  |  | n0 = | -100000
 |  |  |  |  |  |  | j0 = | 49
 |  |  |  |  |  |  | [NAME] | -2
OSGB36 latitude, longitude and approx ODN height to OSGB36 cartesian XYZ
 |  | ° | ' | " | [NAME] |  | Decimal degrees and height are the input to the
latitude | N | 52 | 39 | 27.25309488052153 | 52.65757030413348 | Input | user defined functions.
longitude | E | 1 | 43 | 4.517699167315186 | 1.717921583102032 |  | The DMS values are converted to decimal using
~ODN height | 24.70009583048522 | m |  |  |  |  | standard Excel functions
X | 3874938.849487588 | m |  |  |  | Output | Ellipsoid parameters (OSGB36)
Y | 116218.6237483839 | m |  |  |  |  | a = | 6377563.396
Z | 5047168.208377489 | m |  |  |  |  | b = | 6356256.91
Helmert Datum Transformation (OSGB36 to WGS84)
 |  |  |  |  |  |  | Transformation (OSGB36 to WGS84)
X | 3874938.849487588 | m | Input |  |  |  | translation parallel to X = | 446.448
Y | 116218.6237483839 | m |  | See the Transformation Notes on the |  |  | translation parallel to Y = | -125.157
Z | 5047168.208377489 | m |  | "Constants" page for information on the |  |  | translation parallel to Z = | 542.06
X | 3875311.471771921 | m | Output | limitations and accuracy of this |  |  | scale change = | -20.4894
Y | 116103.230087724 | m |  | transformation. |  |  | rotation about X = | 0.1502
Z | 5047602.299358739 | m |  |  |  |  | rotation about Y = | 0.247
 |  |  |  |  |  |  | rotation about Z = | 0.8421
WGS84 [NAME] to WGS84 Latitude, longitude and Ellipsoidal height
 |  |  |  |  |  |  | The decimal degrees and height are the output of
X | 3875311.471771921 | m |  |  |  | Input | the user defined functions.
Y | 116103.230087724 | m |  |  |  |  | The decimal values are converted to DMS using
Z | 5047602.299358739 | m |  |  |  |  | standard Excel functions
 |  | ° | ' | " | [NAME] |  | Ellipsoid parameters (WGS84)
latitude | N | 52 | 39 | 28.72298017289722 | 52.65797860560358 | Output | a = | 6378137
longitude | E | 1 | 42 | 57.78704187506264 | 1.716051956076406 |  | b = | 6356752.3142
Ellipsoidal height | 69.40170076303184 | m