{"text_format":"1","workbook":"xlsx","n_sheets":16,"sheets":["Flash","Intro","Notes","Contents","Sanity","Installation","Lat,long <-> OSref","Range & bearings","Variations","Misc Functions","GPX","CompactGPX","KML","ZipCodes","VBA code","Example Application"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":1,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Flash","index":0,"n_rows":38,"n_cols":2,"rows_kept":38,"cut":false,"hidden":false,"merged":[]}
[]
[{"$f":"Intro!A2","v":"Phil Brady's Cartographic functions -     Windows & Mac compatible version.  V42/3 May 2025"}]
[]
["This workbook contains examples of using the OSGB addin and includes macro code to install the addin and to edit the formulae in this workbook."]
[]
["Note:  If this flash sheet does not hide then macros are being inhibited by Microsoft and need to be enabled."]
[]
["The author asserts that he has NOT added any malicious code and if you trust that assertion then proceed as follows:"]
[]
["Windows:"]
[]
["To enable macros do the following:"]
[]
["1.  In Excel, select:   File > Options > Trust Centre > Trust Centre settings > Macro Settings > Disable macros with notification > ok > close window (twice)."]
[null,"This is a global setting which will affect all workbooks with macros and will only be needed once."]
[null,"When macros are encountered in a workbook, you will advised that macros are disabled.  Ifyou trust the source then click 'Enable editing'."]
[]
[]
["2.  If 'macros have been disabled' then click 'Enable Editing'"]
[null,"click 'Enable Editing''"]
[]
[]
["3.  Microsoft will also block workbooks which are untrusted because they have been downloaded from the Internet.  "]
[null,"It will report that 'Microsoft has then blocked macros from running because the source is untrusted'"]
[]
[null,"Open file explorer "]
[null,"Locate the file:"]
[null,"Right click on this source file"]
[null,"Properties > general > Unblock > apply>ok"]
[null,"Close this workbook and try again."]
[]
["4.  If this flash sheet still does not hide then click the yellow cell below."]
[null,"click here to continue"]
[]
[]
["MAC"]
[]
["On MAC, please reload and enable macros"]

{"sheet":"Intro","index":1,"n_rows":46,"n_cols":1,"rows_kept":46,"cut":false,"hidden":false,"merged":[]}
[]
["Phil Brady's Cartographic functions -     Windows & Mac compatible version.  V42/3 May 2025"]
[]
["These pages demonstrate use of routines allowing various conversions and calculations involving British Ordnance Survey references, the import, manipulation  "]
["and export of GPX data in both OS reference and WGS84 format, the export of data for displaying on Google Earth and the location of postcodes."]
[]
["Installing Code"]
[]
["The underlying code is supplied separately as an add-in.  This is in a file osgb.xla for Excel 1997-2003 or osgb.xlam for later versions.  It  must also be downloaded (see installation worksheet)."]
["This example workbook includes an installer to guide you through the installation process."]
["If you haven't already done so, or if you wish to install a new version then do so via OSGB Installer > Install addin"]
["Once the add-in has been installed it will be available to all workbooks."]
[]
[]
["Compatibility"]
[]
["The routines have been developed and tested extensively under Excel 2003 and 2016 and have been validated with Excel 2007 and 2010 in Windows 95/XP/10/11 environments."]
["This code has been tested with MacOS Mojave, Catalina and Sonoma  and Excel versions 16.16 (excel 2016) and 16.41 (Excel 2019 and 365)"]
[]
[]
["Disclaimer and Conditions"]
[]
["These Excel (tm) spreadsheet functions are made available on the understanding that:"]
[]
["1.  Phil Brady asserts authorship and retains all intellectual, copyright and patent rights."]
["2.  No liability is accepted for the way in which these routines are used, or the way in which any results are interpreted."]
["3.  They will not be utilised for financial gain, either directly or in modified form."]
["4.  Use is acknowledged in any publications."]
["5.  Reverse engineering or extraction of the underlying code is not attempted."]
[]
["Accuracy"]
[]
["These routines were written for domestic/recreational use though they have been used by professional bodies and in research studies."]
["Accuracy of conversions is to the best of my knowledge to be 'of the order of a few metres'."]
["Calculations of distances and angles are based on cartesian geometry, not spherical.  They will not be accurate over large distances."]
["Note that magnetic variation calculations are 'rough estimates' based on Whitaker's Almanack.  They may require revision from time to time."]
[]
[]
["Acknowledgements"]
[]
["I am very grateful for the encouragement and support so willingly given by Ian Baines.  Without this help, support on MAC would not be possible."]
["I also acknowledge the encouragement and support given by Murray Fullerton in implementing Helmert rather than Molodensky transformations."]
["I also include code published by Ordnance Survey."]
[]
["Author"]
["phil.brady@hotmail.co.uk"]

{"sheet":"Notes","index":2,"n_rows":176,"n_cols":2,"rows_kept":176,"cut":false,"hidden":true,"merged":[]}
[]
["Editing HELP text:"]
["https://www.excelforum.com/excel-programming-vba-macros/1429142-open-sheet-in-an-addin.html"]
[]
["“Go to the Thisworkbook module of your add-in. Change the IsAddin property to False."]
[" The workbook will now be visible. Make corrections to the workbook. Finally, change the IsAddin property to True and from the VBA editor, save the file."]
["Artik”"]
[]
[]
[]
["Change History"]
[]
["Changes to version 2.00 include:"]
["- transformations allow WGS84 datum for lat/long"]
[null,"(spreadsheets using previous versions will need modification)."]
["-  miscellaneous functions for formatting data. "]
["- Eastings and Northings to OS reference added"]
["- improved detection of errors in input data."]
["- improved examples"]
[]
["Changes in version 2.01:"]
["- tidyref now allows specification of number of digits and optional spacing"]
["- distance functions hypotenuse and hypotenusekm introduced."]
[]
["Changes in version 2.02, 31 Jan 2008."]
["- Added bearing, angle and target functions. See Range and Bearings sheet."]
[" - added variations functions."]
["- Corrected email address."]
["- disclaimer changed."]
["- Example application added"]
["- new function for text to decimal degrees."]
[]
["Version 3  2011.  Limited circulation in beta form only."]
[]
["Version 4 published.  April 2012."]
["GPX import & export added."]
["roundref function added."]
["New Whitaker data for 2011"]
["Alternative formats for magnetic variation functions."]
["Export to KML file for input to Google Earth"]
["About subroutine."]
["Help routine added"]
["Errors at extreme edges of OS grid in function MetrestoOSref fixed."]
["Validated with Excel 2007. "]
["Diagnostic code added"]
["Code and examples split"]
["Installer added"]
[]
["Version 4 June 2012"]
["Bug in exportGPX with longitudes < 0.1 fixed. "]
["Zipcodes added,"]
[]
["Version 5 August 2013"]
["target function added to help text."]
["Bug fix in importGPX.  Duplicated points identified as spurious not handled properly.   'spurious points' mechanism removed in favour of new CompactGPX routine"]
["Export GPX and KML routines now tolerate blank rows at end of selection."]
["Trailing spaces after gpx types now ignored instead of generating a puzzling error."]
["CompactGPX  routine developed."]
["Route/track names added to KML point 'bubbles'"]
["Fixed spurious 'save zip data?' on closing addin."]
["Released on beta test. "]
[]
["Version 5.01 20 August 2013"]
["Size limits increased (absolute limits not known but tested with data sets with 57,000 rows with Excel 2003)"]
["Needed major re-jig of ExportKML"]
["Progress indication added to import/export and compact."]
["Bug in installer in examples - did not correctly edit cells."]
["Caching in OS > Lat/long for performance gain.   If latitude is requested, longitude will be probably be next and vice-versa  "]
["Rewrite of compacting algorithm for improved efficiency. "]
["Change format of cells to 'General' before over-writing them - avoid subsequent errors."]
[]
["Version 5.02,   8 October 2013"]
["Allow user to specify destination cells for GPX import and compact"]
["Prettier progress indication"]
[]
["Version 5.03, 11 Nov 2013"]
["fix links routine added; fixes #NAME? errors on imported workbooks.."]
[]
[]
["Version 6.00  2nd Jan 2019"]
["Code changed to allow generation of two versions:"]
[null,"xls /xla version for Excel 2003"]
[null,"xlsm / xlam version with ribbon interface for later versions."]
["Fix links routine changed to cater for 2010/2016"]
["Whitaker Terrestrial Magnetic data for 2019 now default. "]
[]
["Version 6.01 13 feb 2019"]
["GetFileName copied from osgb.xlam (Mac compatible?) but without  postmortem call."]
[]
[]
["Version 6.02  "]
["2020-04-29","Copied getfilename from ianmac06.xlsm.  Removed postmortem; checked whole filename not just extensiuon."]
["2020-04-30","Added show addin location to menu"]
["2020-04-30","List of addins in the collection displayed"]
["2020-05-08","GetAddinDetails changed to detect case of file missing but addin 'installed'"]
[null,"uninstall - remove file even if not installed."]
[null,"Installation notes changed to refect MAC use."]
["2020-05-10","set aiinstalled=true  in myinstall.  Extra diagnostics in GetAddinDetails.  Do not 'uninstall' during install (put in yesterday!) but do kill addin files.   Version MacExamples04"]
["2020-05-11","Removed extra diagnostics in GetAddinDetails.  Remove displaying of details in checkinstall."]
["2020-05-12","Beta test version."]
["2020-05-19","Altered check addin routine to mention /usr/lib file.  Added message reminding mac user of need for the file if absent.   "]
["2020-05-21","Do not mention deprecated file osgb.xla if mac.  MACEXamples06"]
["2020-05-23","New function to give install location.  Started fresh sheet Install issues  MacExamples08"]
["2020-05-24","MacExamples08 will not load.  Recovered from last night's snapshot - seems ok.  Saved as MacExamples09"]
["2020-05-24","Needed trust setting changing.   Ok now.  Revised install sheet.  MacExamples09"]
["2020-05-24","Revised GetAddinDetails  Order changed & no longer check deprecated file if mac (#if not MAC failed)   MacExamples10"]
["2020-05-28","Changed DeleteMenu - don't attempt it if ribbon version.  Correct mag variation notes - Whitaker 2019 data is default, not 2012.   MacExamples11"]
["2020-06-03","#if useribbon then totally exclude createmenu and deletemenu code - suspicion of compile failure with mac.  Macexamples12"]
["2020-07-03","Enhanced install check for mac - osgb.applescript "]
["2020-07-06","Fixed getfilename bugs. Introduced applescripttask and grantaccesstomultiplefiles dummies for testing.."]
["2020-07-09","Reverting to simpler version of GetFileName using GetOpenFileName instead of applescript."]
["2020-07-17","Include applescript generator."]
["2020-07-17","Include functions to show location of MAC files for MACnotes.  MACexamples15"]
["2020-07-19","Further tidy up of install.  Macexamples16"]
["2020-07-29","MACexamples19.   Stage by stage installation.  Big rewrite of getaddindetails"]
["2020-07-30","All instances of kill - precede with setattr filename,vbnormal"]
["2020-07-30","Check whether addin is working with applicatio.run.  Include this in getaddindetails"]
["2020-07-30","Temp location same folder as examples workbook."]
["2020-07-30","Refresh installation sheet even if no installation work needed."]
["2020-07-31","Added VBA helper code."]
["2020-07-31","White background on all sheets to improve readability"]
["2020-08-02","Added copy to applescript so user can click on the file instead of doing the copy manually."]
["2020-08-03","Seek permissions for appletemp in Macspecials.  MacExamples22."]
["2020-08-03","Corrections to above Examples23"]
["2020-08-07","Stage 1 message error fixed  - nor .Librarypath but .UserLibraryPath."]
["2020-08-07","Select installation page during install."]
["2020-08-07","Ruler added to messages.  Remove it by changing const ruler=\"\""]
["2020-08-07","Revised instructions on running applescript"]
["2020-08-09","Added display dialog to applescript - request user saves the workbook."]
["2020-08-09","Whitaker 2019 data in examples sheet."]
["2020-08-17","GetAddindetails:  clarify registry status:  known but not installed.  "]
["2020-08-18","GetEasting - allow to access even if not installed"]
["2020-08-18","Cater for addin loaded but not installed.  GetAddinDetails and myinstall changed."]
["2020-08-20","GetAddindetails simplified.  Extra installation stage - remove old installation added."]
["2020-08-20","Don't allow installation if routines available but not installed - the file copy fails otherwise.  MACEXamples25"]
["2020-08-24","Noted that application.run fails compilation rather than run time on MAC.  We can no longer rely on it to indicate that an addin is working.  Removed references to it. "]
["2020-08-26","Simplified notes on calling osgb from own vba.   Trap failure to copy addin in install - assume osgb is already running.     Macexamples 26"]
["2020-09-23","Added mkdir -p <applescriptpath> to applescript.  Reason: folder missing on Matt's system.  MACExamples 27"]
["2020-09-25","Changed above to use ~/ rather than /user/<username>/  from Application.userlibrarypath  MACEXamples 27"]
["2020-10-10","Change code to inhibit complilation failures if mac<16.  Permissionsok, AppleScriptNeeded.  Examples29"]
["2020-10-12","Added sysinfo(\"supported\") - check version>=11 windows, >=16 MAC."]
["2020-10-12","Bug in EditAllCells.  Use IsError to indicate that cell needs rewriting, not mismatch between formuls and edited formula.  Examples30"]
["2020-10-20","Revise:  Look specifically for error xlErrName"]
["2020-10-23","Revise again.  Edit cell if A) formula refers to osgb or B) cell has a NAME error"]
["2020-10-23","mkdir and the copy in applescript includes full file/folder name rather than  ~.  "]
["2020-10-23","on osgbopen event added to applescript.    MacExamples32"]
["2020-10-27","Corrected \"/\" not \"\\\" in Sysinfo"]
["2020-10-29","osgbopen missing!!  ??   Re-added."]
["2020-11-01","Added startuppath to sysinfo"]
["2020-11-01","Mark destination file not source file as installed."]
["2020-11-05","Display dialog added to applescript osgbopen   Examples36"]
["2020-11-10","Applescript osgbopen becomes tell application \"Finder\" to open f as POSIX file"]
["2020-11-13","Revised notes;  Don't switch to install sheet if action needed;    clarify message: Please install osgb - added 'addin'.     MACExamples38"]
["2020-11-28","Cosmetic change:  Tidier 'installed' in msgbox  checking addin status.   MacExamples39"]
["2024-10-05","Fix in roundref to allow 8 digits.  Included Ord Survey Helmert transformation as default, transformation function to switch back to Molodensky, added getelement."]
["2024-10-05","Included new routines in help; added to examples."]
["2024-10-12","Round easting and northing down in LLtoCOORDS - but removed because it gave inferior results compared with Ord Surv batch results."]
["2024-11-12","Strategy for Helmert/Molodensky, OS vs Maling, rounding included but default zero."]
["2024-11-12","Changed stage 5 of MAC install.   /usr/lib/libc.dylib not always needed."]
["2024-11-20","Cells not changed during install - change to scan ThisWorkbook not Activeworkbook in EditAllCells"]
["2024-11-20","Only ask user to save if cells have changed. Bool  Saveneeded"]
["2024-11-20","Allow mac type imported formulae starting =@"]
["2024-11-22","AppActivate added to addin startup code - try to unhide examples after license agreed.    Listname code added to get name of this  book"]
["2024-11-22","Install page - changed applescipt needed text if already installed."]
["2024-11-25","Validated with Mac os Sonoma "]
["2024-02-02","Blocked macros note added to this sheet."]
["2024-02-04","Flash page added to installer."]
["2024-12-10","Changes instances of Application.ActiveWorkbook.Sheets  to thisworkbook.sheets()  Removed ambiguity."]
["2024-12-11","Added instructions for setting trust centre to flash page."]
["2024-12-21","height set at -50m in myhelmert in addin - gives closer match in just a few points. "]
["2024-12-21","Flash screen text changed and yellow 'if all else fails' button added."]
["2024-12-21","Notes on separate page and hidden."]
["2024-12-31","Workbook_open() code modified to trap Run-time error '1004' when Protected View [Enable Editing] is clicked"]
["2024-12-31","Workbook_open - hides notes as well as flash screen"]
["2024-12-31","flash >  Worksheet_SelectionChange simplified - no need to check whether current screen is flash."]
["2025-05-03","Fix crashes with Import GPX or compact GPX when different sheet selected; also on undo.   Subs WriteArraytoSheet and InportGPXUndo. "]

{"sheet":"Contents","index":3,"n_rows":87,"n_cols":4,"rows_kept":87,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Phil Brady's Excel OSGB routine examples."]
[]
["The OSGB routines perform conversions and calculations with the ordnance survey grid of Great Britain, import and export gpx data, export kml data and locates postcodes.."]
["The routines are available in an addin osgb.xlam"]
[]
["This workbook has install and uninstall routines for the osgb add-in code and descriptions and examples of the use of the routines.:"]
[]
["Contents"]
[null,"This sheet!"]
[]
["Installation Notes",null,"   < < < ","Start Here!"]
[]
[null,"Installing the OSGB addin and transfer of workbooks between systems."]
[]
[]
["Lat/long <-> OS"]
[]
[null,"Description and examples of conversion between "]
[null,null,"Ordnance Survey reference,"]
[null,null,"latitude and longitude (OS 1936 datum),"]
[null,null,"latitude and longitude (WGS84 datum) and"]
[null,null,"northings and eastings."]
[]
["Variations"]
[null,"Compute variations of:"]
[null,null,"True north from Grid"]
[null,null,"Grid north from True"]
[null,null,"Magnetic north from Grid"]
[null,null,"Magnetic north from True"]
[]
["Range & Bearings"]
[]
[null,"Distances between points in metres or km."]
[null,"Compute bearings"]
[null,"OS Reference of target from here on a given range and bearing."]
[]
["Misc functions"]
[]
[null,"Extracting decimal degrees from various formats."]
[null,"Display latitude or longitude in degrees, minutes & seconds"]
[null,"Tidying up or rounding an OS reference"]
[null,"Angle normalisation (ie to between +-180 or 0 ~ 360)"]
[]
["GPX routines"]
[]
[null,"Import or export *.gpx files.  These are typically used by mapping software and handheld devices such as GPS receivers."]
[null,"Coordinates can be in latitude/longitude or OS references."]
[]
["Compact GPX"]
[]
[null,"If you have a track log or route which has been generated automatically and which has thousands of points then you will not be able to"]
[null,"see the wood for the trees.  This is the routine for you !"]
[null,"It will compact the data down to a fraction of the original size without losing much in the way of accuracy."]
[]
["KML Routine"]
[null,"The ExportKML routine will output data to a file suitable for input to Googe Earth ™. "]
[null,"Coordinates can be in latitude/longitude or OS references."]
[]
["Zipcodes"]
[null,"Gets longitude, latitude and OS reference for postcodes from web based mapping systems."]
[null,"Examples given for GB and US."]
[]
["VBA","How to access these OSGB routines from your own vba module."]
[]
[]
["Help"]
[null,"Via OSGB > Help"]
[]
[]
["Example Application"]
[]
[null,"This worksheet holds an application which:"]
[null,null,"Computes distances and bearings between two points."]
[null,null,"Computes second point from first, given bearing and distance"]
[null,null,"Allows input in OSreference, WGS84 or OS1936 format."]
[null,null,"Allows preset points to be included (TV transmitters in NW Wales as example)."]
[null,null,"Allows bearings to be grid, true or magnetic.  Distances in various units."]
[null,"Note green cells hold partial calculation results and are hidden, yellow cells are for input."]
[]
["About"]
[null,"Via OSGB > about"]
[null,"Shows the location from which the addin was loaded."]
[]
["Author"]
[null,"phil.brady@hotmail.co.uk"]

{"sheet":"Sanity","index":4,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":true,"merged":[]}
["host",{"$f":"sysinfo(A1)","v":"Excel 16.0 running on Windows (32-bit) NT 10.00"}]
["supported",{"$f":"sysinfo(A2)","v":"supported"}]
["appletemp","Not relevant for Windows"]
["applefinal","Not relevant for Windows"]
["applefinalpath","Not relevant for Windows"]
["MAC",false]
["addinpath","C:\\Users\\philb\\AppData\\Roaming\\Microsoft\\AddIns\\"]
["addin","C:\\Users\\philb\\AppData\\Roaming\\Microsoft\\AddIns\\osgb.xlam"]

{"sheet":"Installation","index":5,"n_rows":120,"n_cols":3,"rows_kept":120,"cut":false,"hidden":false,"merged":[]}
[]
["OSGB Installation"]
[]
["Installation Notes"]
[]
["To install OSGB you need to download two components:"]
[]
["1.  An add-in which provides the functionality of OSGB and which  needs to be installed. "]
["2.  This examples workbook with worked examples and an installer."]
[]
["After installation, the OSGB routines will be available to all workbooks."]
[]
["Compatibility check:"]
[]
["This system has:",{"$f":"sysinfo(\"host\")","v":"Excel 16.0 running on Windows (32-bit) NT 10.00"}]
["This system is:",{"$f":"sysinfo(\"supported\")","v":"supported"}]
[]
[]
["For Windows Excel 2007 (version 11) or later and for MAC Excel 2016 (version 16) you need this OSGBExamples.xlsm and the add-in osgb.xlam."]
[null,"MAC Excel 2016 will need updating with help > Update"]
[]
["For Excel 1997-2003 with Windows  you need to download the deprecated and no longer supported OSGBExamples.xls and addin osgb.xla"]
[]
["MAC excel prior to 2016 is not supported."]
[]
[]
["Installation Process"]
[]
["If OSGB needs to be installed or re-installed, please choose 'OSGB Installer > Install addin' from the menu above.  This will lead you through the necessary steps as follows:"]
[]
["Stage 1","Remove any previous installation"]
[]
[]
["Stage 2.","You will be asked to locate the addin osgb.xlam which you have downloaded.  It will then be copied to the addin library.:"]
[null,"On this system it will be placed here:",{"$f":"sysinfo(\"addin\")","v":"C:\\Users\\philb\\AppData\\Roaming\\Microsoft\\AddIns\\osgb.xlam"}]
[]
[]
["Stage 3","The addin will be marked as 'installed' in the registry (or pseudo registry in the case of MAC)."]
[null,"You will also be asked to agree the license conditions."]
[]
[]
["Stage 4.","The formulae in this examples workbook may still point at the addin on the author's development system so show a #NAME? errors."]
[null,"Stage 4 edits these so they point at the addin you have just loaded."]
[]
[]
["Stage 5 (Mac only).",{"$f":"IF(sysinfo(\"MAC\"),\"\",\"Not relevant for Windows\")","v":"Not relevant for Windows"}]
[null,"For the zip codes / postcodes to work we need to have access to a mac utility for older versions of MAC."]
[null,"Permissions may be  requested."]
[null,"The file is: /usr/lib/lib.dylib"]
[]
[]
["Stage 6 (Mac only).",{"$f":"sysinfo(\"Applescript\")","v":"Not relevant for Windows"}]
[null,"The functions for asking a user to select a file have serious limitations with MAC and require a little helper script written in Applescript for stability. "]
[null,"Even with this in place, permission will still be requested by OSX when folders or files outside the sandbox are accessed for the first time."]
[null,"Stage 6 generates this file in a temporary location and you may be asked to allow permissions for this."]
[]
[null,"The location on your system is:",{"$f":"sysinfo(\"appletemp\")","v":"Not relevant for Windows"}]
[]
[]
["Stage 7 (Mac only).",{"$f":"B52","v":"Not relevant for Windows"}]
[null,"The Applescript temporary file now needs to be placed in the relevant library so Excel can access it."]
[null,"Unfortunately, we could not find a way to do this directly with VBA or Excel so it will need manual intervention:"]
[]
[null,"Please use Finder to open and run the temporary file: ",{"$f":"sysinfo(\"appletemp\")","v":"Not relevant for Windows"}]
[null,"This will copy itself to:",{"$f":"sysinfo(\"applefinal\")","v":"Not relevant for Windows"}]
[]
[]
[null,"Please copy the temporary file created in step 5  from here:   ",{"$f":"sysinfo(\"appletemp\")","v":"Not relevant for Windows"}]
[null,"to here:   ",{"$f":"sysinfo(\"applefinal\")","v":"Not relevant for Windows"}]
[]
[null,"Note that the ~/Library folder is hidden but you can reveal or rehide it with Command Shift Dot."]
[]
["Final stage","Please save and close this examples workbook so the edited cells will be preserved."]
[null,"The OSGB functions should now be available to all opened workbooks, including this one."]
[]
[]
[]
["Check Installation"]
[]
["The option OSGB Installer > Check  addin install shows installation status and recommends any action needed.."]
[]
[null,"Is deprecated osgb.xla present?  This was the addin for Excel 1997~2003"]
[null,"Is recommended osgb.xlam present?"]
[null,"Is it marked as 'installed' in the registry? "]
[null,"if MAC, is the applescript helper file in place."]
[null,"Are there any cells which need 'fixing'?"]
[]
["Uninstalling"]
[]
["1.  Ensure no other workbooks are running"]
["2.  Open OSGBExamples.xlsm"]
["3.  Select OSGB Installer  > uninstall add-in"]
["4.  Close Excel."]
["5.  For MAC, remove the temporary and final applescript helper files manually.  They will be found here:"]
[null,{"$f":"sysinfo(\"appletemp\")","v":"Not relevant for Windows"}]
[null,{"$f":"sysinfo(\"applefinal\")","v":"Not relevant for Windows"}]
[]
[]
[]
["Moving Workbooks and sharing data  (#NAME? errors)."]
[]
["If you move or copy a workbook from one system to another you will end up with broken links whereby functions in the cells still refer to the original system."]
["The functions in the add-in are thus not found and give a #NAME? error."]
["You may have seen examples in this workbook. "]
[]
["Excel 2016 gives a message which suggests that it will fix this problem but it is ineffective so code is supplied to do this."]
[]
["All cells in this examples workbook will be corrected during installation."]
["A routine to scan all workbook cells and 'correct' cells is available via OSGB > Fix links to cater for the case of other data transferred between systems."]
[]
[]
["Re-Installation Failure ?"]
[]
["You may encounter a situation where a reinstall of the OSGB add-in appears to go through successfully but after the install, the OSGB add-in does not appear. "]
[]
["In this situation the Excel Add-in registry needs to be cleared. Perform an uninstall as normal, then go to Developer>Excel Add-ins from within Excel. "]
["The OSGB Add-in checkbox should be unticked. "]
["Try to tick it, at which point Excel will say the Add-in isn’t installed, so would you like to remove the option"]
["Agree to this then restart  Excel. "]
["Perform the install again, which should now succeed."]

{"sheet":"Lat,long <-> OSref","index":6,"n_rows":97,"n_cols":7,"rows_kept":97,"cut":false,"hidden":false,"merged":[]}
[]
["Phil Brady's OS conversion functions"]
[]
["Conversion of OS reference to latitude and longitude:"]
[null,"=latitude(datum,osreference,strategy)"]
[null,"=longitude(datum,osreference, strategy)"]
[null,"where datum is that required for the latitude/longitude.   Use 36 for OS1936, 84 for WGS84"]
[null,"Strategy is optional and controls the algorithm used."]
[]
["Conversion of latitude and Longitude to  OS reference"]
[null,"=osref(datum, latitude,longitude,strategy)"]
[null,"where datum is that of the given latitude and longitude.  Specify 36 for OS 1936,  84 for WGS84"]
[null,"note that longitude is negative for west"]
[null,"Strategy is optional and controls the algorithm used."]
[]
["OS reference to Eastings and Northings:"]
[null,"=easting(osreference)"]
[null,"=northing(osreference)"]
[null,"Note:   Easting and northing are in metres from false origin."]
[]
["Eastings and Northings to OS reference:"]
[null,"=MetresToOSref(easting,northing)"]
[null,"Note:   Easting and northing are in metres from false origin."]
[]
["WGS84 latitude/longitude to OS 1936 datum:"]
[null,"=newlatitude(36,latitude,longitude,strategy)"]
[null,"=newlongitude(36,latitude,longitud,strategye)"]
[]
["OS 1936 datum latitude/longitude to WGS84 datum:"]
[null,"=newlatitude(84,latitude,longitude)"]
[null,"=newlongitude(84,latitude,longitude)"]
[]
[]
["Strategy"]
[]
["Strategy is an optional parameter.  It is bit significant.  +4 = use Molodensky rather than Helmert, 2= use OS LL to easting/northing rather than Derek Maling, 1= round easting/nothing DOWN rather than to nearest metre."]
["You can get a reminder of the meaning of a strategy with:"]
[null,"=strategydesc(strategy)    "]
[]
["The default strategy of zero is advised."]
[]
[]
[]
[]
["Examples:"]
[]
["Raw Data"]
["Place","Aberystwyth","Bangor","M53/M56 junc","Scwd yr Eira  **","Kent drain gulley","Test - Origin"]
["OS reference","SN5881","SH5772","SJ 423 728","SN 928 098","TQ 34700 69798","xe00"]
[]
[]
["OSreference to/from OS1936 datum:"]
["OSreference",{"$f":"B49","v":"SN5881"},"SH5772","SJ 423 728","SN 928 098",{"$f":"F49","v":"TQ 34700 69798"},"xe00"]
["OS1936 latitude",{"$f":"[1]!latitude(36,B53)","v":52.408172607421875},{"$f":"[1]!latitude(36,C53)","v":53.225490570068359},{"$f":"[1]!latitude(36,D53)","v":53.248794555664063},{"$f":"[1]!latitude(36,E53)","v":51.776138305664063},{"$f":"[1]!latitude(36,F53)","v":51.410694122314453},{"$f":"[1]!latitude(36,G53)","v":49.000003814697266}]
["OS1936 longitude",{"$f":"[1]!longitude(36,B53)","v":-4.0877976417541504},{"$f":"[1]!longitude(36,C53)","v":-4.1423392295837402},{"$f":"[1]!longitude(36,D53)","v":-2.8648450374603271},{"$f":"[1]!longitude(36,E53)","v":-3.5539810657501221},{"$f":"[1]!longitude(36,F53)","v":-6.2954656779766083E-2},{"$f":"[1]!longitude(36,G53)","v":-1.9999971389770508}]
["OSreference",{"$f":"[1]!OSref(36,B54,B55)","v":"SN 58000 80999"},{"$f":"[1]!OSref(36,C54,C55)","v":"SH 57000 72000"},{"$f":"[1]!OSref(36,D54,D55)","v":"SJ 42300 72800"},{"$f":"[1]!OSref(36,E54,E55)","v":"SN 92800 09800"},{"$f":"[1]!OSref(36,F54,F55)","v":"TQ 34700 69798"},{"$f":"[1]!OSref(36,G54,G55)","v":"XE 00000 00000"}]
[]
[]
["OSreference to/from WGS84 datum"]
["OSreference",{"$f":"B49","v":"SN5881"},"SH5772","SJ 423 728","SN 928 098","TQ 34700 69798","xe00"]
["WGS84 latitude",{"$f":"[1]!latitude(84,B60)","v":52.408523559570313},{"$f":"[1]!latitude(84,C60)","v":53.225742340087891},{"$f":"[1]!latitude(84,D60)","v":53.249061584472656},{"$f":"[1]!latitude(84,E60)","v":51.776569366455078},{"$f":"[1]!latitude(84,F60)","v":51.411216735839844},{"$f":"[1]!latitude(84,G60)","v":49.000774383544922}]
["WGS84 longitude",{"$f":"[1]!longitude(84,B60)","v":-4.0889778137207031},{"$f":"[1]!longitude(84,C60)","v":-4.1435403823852539},{"$f":"[1]!longitude(84,D60)","v":-2.8662004470825195},{"$f":"[1]!longitude(84,E60)","v":-3.5552031993865967},{"$f":"[1]!longitude(84,F60)","v":-6.4564421772956848E-2},{"$f":"[1]!longitude(84,G60)","v":-2.0013046264648438}]
["OSreference",{"$f":"[1]!OSref(84,B61,B62)","v":"SN 58000 80999"},{"$f":"[1]!OSref(84,C61,C62)","v":"SH 57000 72000"},{"$f":"[1]!OSref(84,D61,D62)","v":"SJ 42300 72800"},{"$f":"[1]!OSref(84,E61,E62)","v":"SN 92800 09800"},{"$f":"[1]!OSref(84,F61,F62)","v":"TQ 34700 69798"},{"$f":"[1]!OSref(84,G61,G62)","v":"XE 00000 00000"}]
[]
[]
["Northing and Eastings"]
["OSreference",{"$f":"B49","v":"SN5881"},"SH5772","SJ 423 728","SN 928 098","TQ 34700 69798","xe00"]
["Easting",{"$f":"[1]!easting(B67)","v":258000},{"$f":"[1]!easting(C67)","v":257000},{"$f":"[1]!easting(D67)","v":342300},{"$f":"[1]!easting(E67)","v":292800},{"$f":"[1]!easting(F67)","v":534700},{"$f":"[1]!easting(G67)","v":400000}]
["Northing",{"$f":"[1]!northing(B67)","v":281000},{"$f":"[1]!northing(C67)","v":372000},{"$f":"[1]!northing(D67)","v":372800},{"$f":"[1]!northing(E67)","v":209800},{"$f":"[1]!northing(F67)","v":169798},{"$f":"[1]!northing(G67)","v":-100000}]
["OSreference",{"$f":"[1]!MetresToOSref(B68,B69)","v":"SN 58000 81000"},{"$f":"[1]!MetresToOSref(C68,C69)","v":"SH 57000 72000"},{"$f":"[1]!MetresToOSref(D68,D69)","v":"SJ 42300 72800"},{"$f":"[1]!MetresToOSref(E68,E69)","v":"SN 92800 09800"},{"$f":"[1]!MetresToOSref(F68,F69)","v":"TQ 34700 69798"},{"$f":"[1]!MetresToOSref(G68,G69)","v":"XE 00000 00000"}]
[]
[]
["OS1936 latitude/longitude to WGS84 datum"]
["OS1936 latitude",{"$f":"B54","v":52.408172607421875},53.225490570068359,53.248794555664063,51.776138305664063,51.410694122314453,49.000003814697266]
["OS1936 longitude",-4.0877976417541504,-4.1423392295837402,-2.8648450374603271,-3.5539810657501221,-6.2954656779766083E-2,-1.9999971389770508]
["WGS84 latitude",{"$f":"[1]!newlatitude(84,B74,B75)","v":52.408523559570313},{"$f":"[1]!newlatitude(84,C74,C75)","v":53.225742340087891},{"$f":"[1]!newlatitude(84,D74,D75)","v":53.249061584472656},{"$f":"[1]!newlatitude(84,E74,E75)","v":51.776569366455078},{"$f":"[1]!newlatitude(84,F74,F75)","v":51.411216735839844},{"$f":"[1]!newlatitude(84,G74,G75)","v":49.000774383544922}]
["WGS84 longitude",{"$f":"[1]!newlongitude(84,B74,B75)","v":-4.0889778137207031},{"$f":"[1]!newlongitude(84,C74,C75)","v":-4.1435403823852539},{"$f":"[1]!newlongitude(84,D74,D75)","v":-2.8662004470825195},{"$f":"[1]!newlongitude(84,E74,E75)","v":-3.5552031993865967},{"$f":"[1]!newlongitude(84,F74,F75)","v":-6.4564421772956848E-2},{"$f":"[1]!newlongitude(84,G74,G75)","v":-2.0013046264648438}]
[]
[]
["WGS84 latitude/longitude to OS1936 datum"]
["WGS84 latitude",{"$f":"B76","v":52.408523559570313},53.225742340087891,53.249061584472656,51.776569366455078,51.411216735839844,49.000774383544922]
["WGS84 longitude",-4.0889778137207031,-4.1435403823852539,-2.8662004470825195,-3.5552031993865967,-6.4564421772956848E-2,-2.0013046264648438]
["OS1936 latitude",{"$f":"[1]!newlatitude(36,B81,B82)","v":52.408172607421875},{"$f":"[1]!newlatitude(36,C81,C82)","v":53.225490570068359},{"$f":"[1]!newlatitude(36,D81,D82)","v":53.248794555664063},{"$f":"[1]!newlatitude(36,E81,E82)","v":51.776138305664063},{"$f":"[1]!newlatitude(36,F81,F82)","v":51.410694122314453},{"$f":"[1]!newlatitude(36,G81,G82)","v":49.000003814697266}]
["OS1936 longitude",{"$f":"[1]!newlongitude(36,B76,B77)","v":-4.0877976417541504},{"$f":"[1]!newlongitude(36,C76,C77)","v":-4.1423392295837402},{"$f":"[1]!newlongitude(36,D76,D77)","v":-2.8648450374603271},{"$f":"[1]!newlongitude(36,E76,E77)","v":-3.5539810657501221},{"$f":"[1]!newlongitude(36,F76,F77)","v":-6.2954694032669067E-2},{"$f":"[1]!newlongitude(36,G76,G77)","v":-1.9999971389770508}]
[]
[]
["**  A super waterfall - you can walk behind it!!"]
[]
["Strategydesc examples:"]
[0,"=",{"$f":"[1]!strategydesc(A90)","v":"Helmert,Maling L/L to E/N,round to nearest metre"},null,null,"Zero is assumed by default"]
[1,"=",{"$f":"[1]!strategydesc(A91)","v":"Helmert,Maling L/L to E/N,round down"}]
[2,"=",{"$f":"[1]!strategydesc(A92)","v":"Helmert,OS L/L to E/N, round to nearest metre"}]
[3,"=",{"$f":"[1]!strategydesc(A93)","v":"Helmert,OS L/L to E/N, round down"}]
[4,"=",{"$f":"[1]!strategydesc(A94)","v":"Molodensky,Maling L/L to E/N,round to nearest metre"}]
[5,"=",{"$f":"[1]!strategydesc(A95)","v":"Molodensky,Maling L/L to E/N,round down"}]
[6,"=",{"$f":"[1]!strategydesc(A96)","v":"Molodensky,OS L/L to E/N, round to nearest metre"}]
[7,"=",{"$f":"[1]!strategydesc(A97)","v":"Molodensky,OS L/L to E/N, round down"}]

{"sheet":"Range & bearings","index":7,"n_rows":165,"n_cols":7,"rows_kept":165,"cut":false,"hidden":false,"merged":[]}
[]
["Range and Bearings"]
[]
["The bearing and target functions were written in February 2008."]
[]
["These routines are simple geometric calculations based on 'square' map geometry.  They are not 'Great Circle' calculations."]
["The simple algorithms break down for large distances."]
["Rules of thumb:  If you can walk it in a week, or spread arms across a pink map and touch the points then you are okay."]
[]
[]
["To Compute Bearings:"]
[]
["=bearing(here,there,offset)",null,null,"Offset is provided to allow for the north you wish to consider:"]
[]
["=bearing(OSHere,OSThere,0)",null,null,"Grid bearing from here to there"]
["=bearing(OSHere,OSThere,Truevar(OShere))",null,null,"True bearing from here to there"]
["=bearing(OSHere,OSThere,MagGrid(OShere ))",null,null,"Magnetic bearing from here to there"]
[]
[null,"Result is an angle between 0 and 360."]
[]
[]
["Alternatives supported:"]
[]
["=GridBearing(OSHere,OSThere)",null,null,"Gives grid bearing from OS reference here to OS reference there."]
[]
["=TrueBearing(OSHere,OSThere)",null,null,"Gives true bearing from OS reference here to OS reference there."]
[]
[]
[]
["Distance between two OS references"]
[]
["=hypotenuse(ref1,ref2)"]
[null,"Gives the distance between two OS references in metres."]
[]
["=hypotenusekm(ref1,ref2)"]
[null,"Gives the distance in kilometres."]
[]
[null,"Note these are calculation based on the hypotenuse distance on the rectangular grid  -"]
[null,"      This is equivalent to measuring from the map with a ruler."]
[null,"      They do not compute great circle distances."]
[]
[]
["Reference of a point at a known distance on a bearing "]
[]
["=Target(OSReference here, range in metres, bearing,offset)"]
[null,"Bearing in degrees.  Not limited to values between 0 and 360."]
[null,"Offset as per the bearing function."]
[null,"ie","for grid bearing use:",null,0]
[null,null,"For true bearing use:",null,"Truevar(here)"]
[null,null,"For magnetic bearing use:",null,"MagGrid(here, …all the Whitaker stuff...  )"]
[]
[]
["Examples of computing bearings, variation and targets."]
[]
[null,"From:","Here",null,"SH500500"]
[null,"To:","There",null,"SH600600"]
[]
[]
[null,"Variations:","True Varies from grid at SH500500 by:",null,{"$f":"[1]!TrueVar(E55)","v":1.78717041015625},"   ie true north is east of grid north in Snowdonia"]
[null,null,"Mag from grid variation at SH500500",null,{"$f":"[1]!MagGrid(E55)","v":0.92978644371032715},"   If negative implies magnetic is west of grid."]
[]
[]
[null,"Distances","From here to there"]
[null,null,"Distance in Km",null,{"$f":"[1]!hypotenusekm(E55,E56)","v":14.142135620117188}]
[null,null,"Distance in metres",null,{"$f":"[1]!hypotenuse(E55,E56)","v":14142.1357421875}]
[]
[]
[null,"Bearings:","From here to there:"]
[null,null,"Grid",null,{"$f":"[1]!bearing(E55,E56,0)","v":45}]
[null,null,"True",null,{"$f":"[1]!bearing(E55,E56,E59)","v":43.21282958984375}]
[null,null,"Magnetic",null,{"$f":"[1]!bearing(E55,E56,E60)","v":44.070213317871094}]
[]
[]
[null,"Targets:","If we are right,  these should all get to  SH600600."]
[]
[null,null,null,null,"Target Reference","Tidied up:"]
[null,null,"On a grid bearing  from SH500500:",null,{"$f":"[1]!Target($E$55,$E$65,E69,0)","v":"SH 60000 60000"},{"$f":"[1]!tidyref([1]!Target($E$55,$E$65,E69,0),6,1)","v":"SH 600 600"}]
[null,null,"On a true bearing  from SH500500:",null,{"$f":"[1]!Target($E$55,$E$65,$E70,E59)","v":"SH 60000 60000"},{"$f":"[1]!tidyref([1]!Target($E$55,$E$65,$E70,E59),6,1)","v":"SH 600 600"}]
[null,null,"On a magnetic bearing  from SH500500:",null,{"$f":"[1]!Target($E$55,$E$65,$E71,$E60)","v":"SH 60000 60000"},{"$f":"[1]!tidyref([1]!Target($E$55,$E$65,$E71,$E60),6,1)","v":"SH 600 600"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"OSreference1","SN5881","SH5772","SJ 423 728","SN 928 098","TQ 34700 69798"]
[null,"OSreference2","SH5772","SJ 423 728","SN 928 098","TQ 34700 69798","TQ 34723 69798"]
[null,"Distance in m",{"$f":"[1]!hypotenuse(C122,C123)","v":91005.4921875},{"$f":"[1]!hypotenuse(D122,D123)","v":85303.75},{"$f":"[1]!hypotenuse(E122,E123)","v":170350.375},{"$f":"[1]!hypotenuse(F122,F123)","v":245185.171875},{"$f":"[1]!hypotenuse(G122,G123)","v":23}]
[null,"Distance in Km",{"$f":"[1]!hypotenusekm(C122,C123)","v":91.0054931640625},{"$f":"[1]!hypotenusekm(D122,D123)","v":85.303749084472656},{"$f":"[1]!hypotenusekm(E122,E123)","v":170.35037231445313},{"$f":"[1]!hypotenusekm(F122,F123)","v":245.18516540527344},{"$f":"[1]!hypotenusekm(G122,G123)","v":2.3000000044703484E-2}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Here","Range (metres)","Grid bearing","Far point"]
[null,"TQ 500 500",1000,45,{"$f":"[1]!Target(B138,C138,D138,0)","v":"TQ 50707 50707"}]
[null,"TQ 500 500",1000,90,{"$f":"[1]!Target(B139,C139,D139,0)","v":"TQ 51000 50000"}]
[null,"TQ 500 500",1000,180,{"$f":"[1]!Target(B140,C140,D140,0)","v":"TQ 50000 49000"}]
[null,"TQ 500 500",1000,270,{"$f":"[1]!Target(B141,C141,D141,0)","v":"TQ 49000 50000"}]
[null,"TQ 500 500",1000,360,{"$f":"[1]!Target(B142,C142,D142,0)","v":"TQ 50000 51000"}]
[]
[null,"Again, not for great distances when you need to use great circle geometry."]
[]
[]
[]
[]
[]
[]
["Angle normalising"]
[]
["=Normalised(angle,base)"]
[null,"Angle is the angle which is to be normalised"]
[null,"Produces a result between base and base+360."]
[]
[null,"This routine is used internally to ensure that bearings are between 0 and 360, and that TrueVar is between -180 and 180."]
[]
[null,"Angle","Offset","Result","Base","Result"]
[null,1,0,{"$f":"[1]!Normalised(B160,C160)","v":1},-180,{"$f":"[1]!Normalised(B160,E160)","v":1}]
[null,361,0,{"$f":"[1]!Normalised(B161,C161)","v":1},-180,{"$f":"[1]!Normalised(B161,E161)","v":1}]
[null,721,0,{"$f":"[1]!Normalised(B162,C162)","v":1},-180,{"$f":"[1]!Normalised(B162,E162)","v":1}]
[null,-1,0,{"$f":"[1]!Normalised(B163,C163)","v":359},-180,{"$f":"[1]!Normalised(B163,E163)","v":-1}]
[null,359,0,{"$f":"[1]!Normalised(B164,C164)","v":359},-180,{"$f":"[1]!Normalised(B164,E164)","v":-1}]
[null,721,0,{"$f":"[1]!Normalised(B165,C165)","v":1},-180,{"$f":"[1]!Normalised(B165,E165)","v":1}]

{"sheet":"Variations","index":8,"n_rows":87,"n_cols":9,"rows_kept":87,"cut":false,"hidden":false,"merged":[[54,6,54,7]]}
[]
["Which North?"]
[]
["These functions were first published February 2008.   MagGrid and MagTrue undertook major revision in April 2011 and Whitaker parameters were revised in 2012 and again in 2019."]
[]
["There are three different interpretations of 'North'."]
[]
[null,"True North is based on the spin axis of the earth.  You would use it in aligning an astronomical telescope."]
[null,"Grid North is that shown by the north/south grid lines on OS maps."]
[null,"Magnetic North is where your magnetic compass will point."]
[]
["All three are slightly different, and the differences between them are called 'variations' (in the UK at least!)."]
[]
["The following functions can be used to compute the variations."]
[]
["Variation of Grid North from True North:"]
[]
["=TrueVar(OSHere)",null,"Gives bearing of true north relative to grid north at the given reference."]
[null,null,"eg -2 says true is west of grid."]
[]
["=GridVar(OSHere)",null,"Gives bearing of grid north relative to true.  Eg -2 implies grid is west of true."]
[]
["Magnetic variation"]
[]
[null,"Unfortunately, the magnetic field drifts from year to year, is different from place to place and its value is determined by measurements"]
[null," taken at magnetic laboratories around the world; three of which are in Britain."]
[]
[null,"These two functions estimate magnetic variation using information from Whitakers Almanac. "]
[]
[null,"They need parameters which can be supplied manually, be taken from the tables below or be omitted in which case"]
[null,"the 2019 Whitaker values hard wired into the code will be used."]
[]
[null,"Whitaker warns that there might be distortions due to local geology, and that solar activity can produce rapid disturbances"]
[null," (eg a 5 degree change over 6 minutes in 2003) and that the values are 'rough'."]
[]
[null,"Please refer to Whitakers Almanac (Terrestrial magnetism in index) for more information."]
[null,"Having stressed the limitations, the two functions are MagTrue and MagGrid as follows:"]
[]
["=MagTrue((OSRef, SurveyYear, SurveyLat, SurveyLong, SurveyVariation, LatVar, LongVar, AnnualVar)"]
["=MagTrue(OSRef,column/row of Whitaker data)"]
["=MagTrue(OSRef)"]
[]
[null,"MagTrue returns an estimate of magnetic variation, in degrees,  from true north at an OS reference in the current year."]
[null,"The  7 Whitaker parameters can be supplied individually,as a column (eg H31:H37), as a row (eg A1:A7) or omitted entirely, "]
[null," in which case 2019 data will be assumed."]
[]
["=MagGrid(OSRef, SurveyYear, SurveyLat, SurveyLong, SurveyVariation, LatVar, LongVar, AnnualVar)"]
["=MagGrid(OSRef,column of Whitaker data)"]
["=MagGrid(OSRef)"]
[]
[null,"MagGrid returns an estimate of magnetic variation, in degrees,  from grid north at an OS reference in the current year."]
[null,"Whitaker parameters are as MagTrue"]
[]
[null,"The parameters are:"]
[null,null,null,null,null,null,"Whitaker data"]
[null,"OSRef","Ordnance survey reference of point at which variation is required."]
[null,"SurveyYear","The year of the almanack which has been used.",null,null,null,2012,2019]
[null,"BaseLatitude","latitude of the survey point for the estimation",null,null,null,50,50]
[null,"BaseLongitude","Longitude ditto",null,null,null,0,0]
[null,"BaseVariation","variation from true at the survey point in minutes.",null,null,null,-49,20,"    negative west"]
[null,"LatVar","Add minutes per degree north.",null,null,null,-11,-11]
[null,"LongVar","Add minutes per degree east.",null,null,null,26,26]
[null,"Annual variation.","Add minutes per year",null,null,null,11,12]
[]
[]
[null,"Example published in Whitaker 2012:"]
[null,"Latitude",53]
[null,"Longitude",-5]
[null,"OS Ref",{"$f":"[1]!OSref(84,C67,C68)","v":"SG 98785 48953"}]
[null,"Variation from true north:",{"$f":"[1]!magtrue(C69,G57:G63)","v":-1.1494816541671753},null,"it's not that accurate!"]
[null,"In dms:",{"$f":"[1]!dms(C70)","v":"  1º08'58\"W"},null,"Note - will not match in future years!"]
[null,"Published value in 2012:","about 3 deg 32min west in 2012"]
[]
[]
[]
["Examples (Whitaker 2019 data):"]
[]
[null,"Place","Reference","Grid from true","DMS format","Magnetic from True","DMS format","Magnetic from Grid","DMS format"]
[null,"At 2 degrees west","SK000000",{"$f":"[1]!GridVar(C79)","v":0},{"$f":"[1]!dms(D79)","v":"  0º00'00\"E"},{"$f":"[1]!magtrue(C79,$H$57:$H$63)","v":0.19046987593173981},{"$f":"[1]!dms(F79)","v":"  0º11'26\"E"},{"$f":"[1]!MagGrid(C79,$H$57:$H$63)","v":0.19046987593173981},{"$f":"[1]!dms(H79)","v":"  0º11'26\"E"}]
[null,"Off Pembroke","SM650410",{"$f":"[1]!GridVar(C80)","v":-2.7013397216796875},{"$f":"[1]!dms(D80)","v":"  2º42'05\"W"},{"$f":"[1]!magtrue(C80,$H$57:$H$63)","v":-1.1874264478683472},{"$f":"[1]!dms(F80)","v":"  1º11'15\"W"},{"$f":"[1]!MagGrid(C80,$H$57:$H$63)","v":1.5139132738113403},{"$f":"[1]!dms(H80)","v":"  1º30'50\"E"}]
[null,"Solent SE","SZ690760",{"$f":"[1]!GridVar(C81)","v":0.7523193359375},{"$f":"[1]!dms(D81)","v":"  0º45'08\"E"},{"$f":"[1]!magtrue(C81,$H$57:$H$63)","v":0.98284846544265747},{"$f":"[1]!dms(F81)","v":"  0º58'58\"E"},{"$f":"[1]!MagGrid(C81,$H$57:$H$63)","v":0.23052914440631866},{"$f":"[1]!dms(H81)","v":"  0º13'50\"E"}]
[null,"Llanberis","SH600600",{"$f":"[1]!GridVar(C82)","v":-1.673858642578125},{"$f":"[1]!dms(D82)","v":"  1º40'26\"W"},{"$f":"[1]!magtrue(C82,$H$57:$H$63)","v":-0.8116687536239624},{"$f":"[1]!dms(F82)","v":"  0º48'42\"W"},{"$f":"[1]!MagGrid(C82,$H$57:$H$63)","v":0.8621898889541626},{"$f":"[1]!dms(H82)","v":"  0º51'44\"E"}]
[null,"Near Northallerton","NZ460000",{"$f":"[1]!GridVar(C83)","v":0.5767822265625},{"$f":"[1]!dms(D83)","v":"  0º34'36\"E"},{"$f":"[1]!magtrue(C83,$H$57:$H$63)","v":0.16826425492763519},{"$f":"[1]!dms(F83)","v":"  0º10'06\"E"},{"$f":"[1]!MagGrid(C83,$H$57:$H$63)","v":-0.40851795673370361},{"$f":"[1]!dms(H83)","v":"  0º24'31\"W"}]
[null,"Used in range/bearings sheet","SH500500",{"$f":"[1]!GridVar(C84)","v":-1.78717041015625},{"$f":"[1]!dms(D84)","v":"  1º47'14\"W"},{"$f":"[1]!magtrue(C84,$H$57:$H$63)","v":-0.85738396644592285},{"$f":"[1]!dms(F84)","v":"  0º51'27\"W"},{"$f":"[1]!MagGrid(C84,$H$57:$H$63)","v":0.92978644371032715},{"$f":"[1]!dms(H84)","v":"  0º55'47\"E"}]
[]
[]
[null,"Note that if you print off this sheet then compare it in later years then magnetic variations may have changed."]

{"sheet":"Misc Functions","index":9,"n_rows":133,"n_cols":7,"rows_kept":133,"cut":false,"hidden":false,"merged":[]}
[]
["Miscellaneous Functions"]
[]
[]
["String to decimal degrees"]
[]
["=decdegrees(string)"]
[null,"decdegrees expects a string with degrees, minutes and seconds "]
[null,"Decdegrees expects degrees, minutes and seconds digits to be present, separated by non numeric characters which can include:"]
[null,"/  , \" ' - º d m or s. "]
[null,"W and w are assumed for west."]
[null,"Now obsolete - see next"]
[]
["=TextDegreesToDec(string)"]
[null,"Improved version of above, though the original is preserved for backward compatibility."]
[null,"Handles incomplete cases without 3 sets of numbers (eg 1.5 or 1 30)."]
[null,"Note that the cell used as parameter should have a format set to 'text'"]
[null,"W or w or minus sign for west."]
[]
["Extract decimal degrees from fixed character position format (eg from logging equipment)"]
[]
["ExtractDegrees will perform this and has parameters:"]
[null,"- String to extract from"]
[null,"- Position of degrees, no of characters"]
[null,"- Position of minutes, no of characters (position zero if none)"]
[null,"- Position of seconds, no of characters (position zero if none)"]
[null,"- Position of West indicator, character expected"]
[]
[]
["Degrees of latitude/longitude to degrees, minutes and seconds"]
[]
["=dms(degrees)"]
[null,"This converts from decimal degrees to a readable degrees, minutes, seconds string."]
[null,"It assumes North if >30, West if negative, otherwise east."]
[]
[]
["To 'tidy up' an OS reference"]
[]
["=tidyref(untidy reference, no. of digits,spaced)"]
[null,"Number of digits must be 2,4,6,8 or 10."]
[null,"If parameter 'spaced' is non zero then spaces are inserted between letters, eastings and northings."]
[]
["Note that 'Tidyref' truncates.  If you wish to round such that (say) SS999999 becomes SO 00 00 then use 'roundref."]
[]
["=roundref(untidy reference, no. of digits,spaced)"]
[]
["Examples of Decdegrees and dms:"]
[]
[null,"Input","Decdegrees","DMS"]
[null,"2º30' 0\"E",{"$f":"[1]!textdegreestodec(B50)","v":2.5},{"$f":"[1]!dms(C50)","v":"  2º30'00\"E"}]
[null,"1 0 0",{"$f":"[1]!textdegreestodec(B51)","v":1},{"$f":"[1]!dms(C51)","v":"  1º00'00\"E"}]
[null,"6/6/8",{"$f":"[1]!textdegreestodec(B52)","v":6.1022224426269531},{"$f":"[1]!dms(C52)","v":"  6º06'08\"E"},"  Beware of date confusion - Excel might interpret this as 6th June 2008."]
[null,"1º 0' 0\"W",{"$f":"[1]!textdegreestodec(B53)","v":-1},{"$f":"[1]!dms(C53)","v":"  1º00'00\"W"},null,"  Prefix with single quote ' or set format to 'text' to fix this"]
[null,"2,0,0w",{"$f":"[1]!textdegreestodec(B54)","v":-2},{"$f":"[1]!dms(C54)","v":"  2º00'00\"W"},"  Include W or w if west "]
[null,"3/0/0W",{"$f":"[1]!textdegreestodec(B55)","v":-3},{"$f":"[1]!dms(C55)","v":"  3º00'00\"W"}]
[null,"49-0-0",{"$f":"[1]!textdegreestodec(B56)","v":-49},{"$f":"[1]!dms(C56)","v":" 49º00'00\"W"}]
[null,"51'15' 0N",{"$f":"[1]!textdegreestodec(B57)","v":51.25},{"$f":"[1]!dms(C57)","v":" 51º15'00\"N"}]
[null,"53,0,0",{"$f":"[1]!textdegreestodec(B58)","v":53},{"$f":"[1]!dms(C58)","v":" 53º00'00\"N"}]
[null,"54d30m0s",{"$f":"[1]!textdegreestodec(B59)","v":54.5},{"$f":"[1]!dms(C59)","v":" 54º30'00\"N"}]
[null,"55D 30M 0S",{"$f":"[1]!textdegreestodec(B60)","v":55.5},{"$f":"[1]!dms(C60)","v":" 55º30'00\"N"}]
[null,"56 30 29N",{"$f":"[1]!textdegreestodec(B61)","v":56.508056640625},{"$f":"[1]!dms(C61)","v":" 56º30'29\"N"}]
[null,"52.5",{"$f":"[1]!textdegreestodec(B62)","v":52.5},{"$f":"[1]!dms(C62)","v":" 52º30'00\"N"}]
[null,1.5,{"$f":"[1]!textdegreestodec(B63)","v":1.5},{"$f":"[1]!dms(C63)","v":"  1º30'00\"E"}]
[null,"1 30",{"$f":"[1]!textdegreestodec(B64)","v":1.5},{"$f":"[1]!dms(C64)","v":"  1º30'00\"E"}]
[]
[]
[]
["Examples of Extractdegrees"]
[]
[null,"Input","Extracted Degrees","DMS"]
[null,"5124.4398N",{"$f":"[1]!ExtractDegrees(B71,1,2,3,7,0,0,10,\"W\")","v":51.407330000000002},{"$f":"[1]!dms(C71)","v":" 51º24'26\"N"}]
[null,"040301W",{"$f":"[1]!ExtractDegrees(B72,1,2,3,7,0,0,10,\"W\")","v":9.0166666666666675},{"$f":"[1]!dms(C72)","v":"  9º01'00\"E"}]
[null,"00002.8800E",{"$f":"[1]!ExtractDegrees(B73,1,2,3,7,0,0,10,\"W\")","v":4.8000000000000001E-2},{"$f":"[1]!dms(C73)","v":"  0º02'53\"E"},"ie 2.88 minutes"]
[null,"515253N",{"$f":"[1]!ExtractDegrees(B74,1,2,3,7,0,0,10,\"W\")","v":138.55000000000001},{"$f":"[1]!dms(C74)","v":"138º33'00\"N"}]
[null,"04.123W",{"$f":"[1]!ExtractDegrees(B75,1,2,3,7,0,0,10,\"W\")","v":4.0020499999999997},{"$f":"[1]!dms(C75)","v":"  4º00'07\"E"}]
[null,"040723-",{"$f":"[1]!ExtractDegrees(B76,1,2,3,7,0,0,10,\"W\")","v":16.05},{"$f":"[1]!dms(C76)","v":" 16º03'00\"E"}]
[]
[]
[]
["Examples of tidying up an OS reference"]
[]
[null,"untidy reference","sn60","SH5772","SJ 423 728","SN 928 098","TQ 34723 69798"]
["tidyref","tidyref unspaced",{"$f":"[1]!tidyref(C$82,2,0)","v":"SN60"},{"$f":"[1]!tidyref(D$82,2,0)","v":"SH57"},{"$f":"[1]!tidyref(E$82,2,0)","v":"SJ47"},{"$f":"[1]!tidyref(F$82,2,0)","v":"SN90"},{"$f":"[1]!tidyref(G$82,2,0)","v":"TQ36"}]
[null,null,{"$f":"[1]!tidyref(C$82,4,0)","v":"SN6000"},{"$f":"[1]!tidyref(D$82,4,0)","v":"SH5772"},{"$f":"[1]!tidyref(E$82,4,0)","v":"SJ4272"},{"$f":"[1]!tidyref(F$82,4,0)","v":"SN9209"},{"$f":"[1]!tidyref(G$82,4,0)","v":"TQ3469"}]
[null,"tidyref spaced",{"$f":"[1]!tidyref(C$82,6,1)","v":"SN 600 000"},{"$f":"[1]!tidyref(D$82,6,1)","v":"SH 570 720"},{"$f":"[1]!tidyref(E$82,6,1)","v":"SJ 423 728"},{"$f":"[1]!tidyref(F$82,6,1)","v":"SN 928 098"},{"$f":"[1]!tidyref(G$82,6,1)","v":"TQ 347 697"}]
[null,null,{"$f":"[1]!tidyref(C$82,8,1)","v":"SN 6000 0000"},{"$f":"[1]!tidyref(D$82,8,1)","v":"SH 5700 7200"},{"$f":"[1]!tidyref(E$82,8,1)","v":"SJ 4230 7280"},{"$f":"[1]!tidyref(F$82,8,1)","v":"SN 9280 0980"},{"$f":"[1]!tidyref(G$82,8,1)","v":"TQ 3472 6979"}]
[null,null,{"$f":"[1]!tidyref(C$82,10,1)","v":"SN 60000 00000"},{"$f":"[1]!tidyref(D$82,10,1)","v":"SH 57000 72000"},{"$f":"[1]!tidyref(E$82,10,1)","v":"SJ 42300 72800"},{"$f":"[1]!tidyref(F$82,10,1)","v":"SN 92800 09800"},{"$f":"[1]!tidyref(G$82,10,1)","v":"TQ 34723 69798"}]
[]
["Roundref","roundref","SN 9999 9999","SN 9900 9990","SN 0005 0005"]
[null,null,{"$f":"[1]!roundref(C$89,2,1)","v":"SJ 0 0"},{"$f":"[1]!roundref(D$89,2,1)","v":"SJ 0 0"},{"$f":"[1]!roundref(E$89,2,1)","v":"SN 0 0"}]
[null,null,{"$f":"[1]!roundref(C$89,4,1)","v":"SJ 00 00"},{"$f":"[1]!roundref(D$89,4,1)","v":"SH 99 00"},{"$f":"[1]!roundref(E$89,4,1)","v":"SN 00 00"}]
[null,null,{"$f":"[1]!roundref(C$89,6,1)","v":"SJ 000 000"},{"$f":"[1]!roundref(D$89,6,1)","v":"SN 990 999"},{"$f":"[1]!roundref(E$89,6,1)","v":"SN 001 001"}]
[null,null,{"$f":"[1]!roundref(C$89,8,1)","v":"SN 9999 9999"},{"$f":"[1]!roundref(D$89,8,1)","v":"SN 9900 9990"},{"$f":"[1]!roundref(E$89,8,1)","v":"SN 0005 0005"}]
[]
[]
["Tidy up an angle:"]
[]
[null,"=normalised(angle, base)"]
[]
[null,"This function adds or subtracts 360˚ to place an angle between base and base + 360."]
[]
[null,"If base is zero, it turns angle into one between 0 and 360."]
[null,"If base is -180 it returns one between -180 and +180."]
[]
[null,"Examples:"]
[]
[null,"Angle","Base = 0","base = -180"]
[null,-170,{"$f":"[1]!Normalised(B108,0)","v":190},{"$f":"[1]!Normalised(B108,-180)","v":-170}]
[null,-10,{"$f":"[1]!Normalised(B109,0)","v":350},{"$f":"[1]!Normalised(B109,-180)","v":-10}]
[null,0,{"$f":"[1]!Normalised(B110,0)","v":0},{"$f":"[1]!Normalised(B110,-180)","v":0}]
[null,10,{"$f":"[1]!Normalised(B111,0)","v":10},{"$f":"[1]!Normalised(B111,-180)","v":10}]
[null,170,{"$f":"[1]!Normalised(B112,0)","v":170},{"$f":"[1]!Normalised(B112,-180)","v":170}]
[null,270,{"$f":"[1]!Normalised(B113,0)","v":270},{"$f":"[1]!Normalised(B113,-180)","v":-90}]
[null,350,{"$f":"[1]!Normalised(B114,0)","v":350},{"$f":"[1]!Normalised(B114,-180)","v":-10}]
[null,370,{"$f":"[1]!Normalised(B115,0)","v":10},{"$f":"[1]!Normalised(B115,-180)","v":10}]
[]
[]
["getelement","Split and extract an element from a srring"]
[null,"=getelement(string,separator,element)"]
[]
["eg","String","52.1,W,2.4"]
[null,"First element",{"$f":"[1]!getelement(C121,\",\",0)","v":"52.1"},"    =getelement(C121,\",\",0)"]
[null,"second",{"$f":"[1]!getelement(C121,\",\",1)","v":"W"},"    =getelement(C121,\",\",1)"]
[null,"third",{"$f":"[1]!getelement(C121,\",\",2)","v":"2.4"},"    =getelement(C121,\",\",2)"]
[]
[]
["OSGB > About","Gives version number of the code"]
[]
["OSGB > Help","For help text"]
[]
["Installation > Install Addin",null,"in examples workbook to install or reinstall addin"]
[]
["Installation > UnInstall Addin",null,"In examples workbook to uninstall and remove it from default addin location."]

{"sheet":"GPX","index":10,"n_rows":87,"n_cols":10,"rows_kept":87,"cut":false,"hidden":false,"merged":[]}
[]
["GPX functions. "]
[]
["gpx files hold mapping data."]
[]
["They are supported by:"]
[null,"Many GPS devices, "]
[null,"GPS import/export software (eg EasyGPS)"]
[null,"Mapping software (eg Anquet maps, Garmin's Basecamp, Trailzilla, MemoryMap)"]
[null,"Google earth."]
[]
[]
["Importing GPX files"]
[]
["The importGPX routine will import waypoints, routes and tracks from a *.gpx file."]
[]
["To import, select a cell top left of the area to hold the imported data, then:"]
[null,null,null,"OSGB > ImportGPX"]
[]
["Data of the following form will be imported:"]
[]
[]
["GPXtype","name","latitude","longitude","number","ele","time","sym","type","label_text"]
["wpt","CHAGF",50.672546349999998,-3.84116546,null,null,"2011-05-29T17:36:16.687Z","Waypoint","Summit","CHAGF"]
["wpt","WP402",50.675434840000001,-3.84953865,null,153.93600000000001,"2011-05-29T17:36:16.687Z","Waypoint","Summit","WP402"]
["wpt","WP403",50.676770410000003,-3.8503873199999998,null,151.05199999999999,"2011-05-29T17:36:16.687Z","Waypoint","Summit","WP403"]
["rte","DAY04",null,null,1,null,null,null,"GPS Route","DAY04"]
["rtept","CHAGF",50.672546349999998,-3.84116546,null,null,"2011-05-29T17:36:16.687Z","Waypoint","Summit","CHAGF"]
["rtept","WP402",50.675434840000001,-3.84953865,null,153.93600000000001,"2011-05-29T17:36:16.687Z","Waypoint","Summit","WP402"]
["rtept","WP403",50.676770410000003,-3.8503873199999998,null,151.05199999999999,"2011-05-29T17:36:16.687Z","Waypoint","Summit","WP403"]
["trk","ACTIVE LOG",null,null,null,null,null,null,"GPS Tracklog","ACTIVE LOG"]
["trkpt",null,50.673000819999999,-3.84098768,null,177.98099999999999,"2011-05-19T09:50:04Z"]
["trkpt",null,50.673923500000001,-3.8436269799999998,null,169.81,"2011-05-19T09:57:47Z"]
["trkpt",null,50.674481399999998,-3.8464593900000001,null,152.02600000000001,"2011-05-19T10:00:50Z"]
["trkpt",null,50.675468449999997,-3.84965658,null,135.203,"2011-05-19T10:04:40Z"]
["trkpt",null,50.676648620000002,-3.8503646900000001,null,139.52799999999999,"2011-05-19T10:08:57Z"]
[]
[]
["The user will be given the option of creating a table with a singe column holding OS references instead of latitude and longitude columns. "]
[]
["Supported columns are:"]
[]
[null,"Those specific to this code:",null,null,null,null,"GPXtype, latitude, longitude and Osref.  "]
[]
[null,"Schema http://www.topografix.com/GPX/1/1/ specific:",null,null,null,null,"ele, time, name, magvar, geoidheight, sym, fix, sat, hdpo, vdop, pdop"]
[null,null,null,null,null,null,"ageofdgpsdata,gpsid, cmt, desc, src, link, type, number"]
[]
[null,"Garmin specific for a decription (desc or cmt would have been a better choice!)",null,null,null,null,"label_text"]
[]
["The schema parameters are described in:",null,null,"http://www.topografix.com/GPX/1/1/"]
[]
["GPXType is as follows:"]
[]
[null,"wpt","Specifies a waypoint. "]
[null,"rte","Start of a route, giving name and route number.  This will be followed by one or more rtept rows."]
[null,"rtept","Routepoints on the  route"]
[null,"trk","Start of a track, giving name and number.  This will be followed by one or more trkpt rows"]
[null,"trkpt","Trackpoint on the track."]
[]
["If you wish to 'undo' a GPXImport then you can do so with control-z"]
[]
[]
["Exporting GPX files"]
[]
["Select a set of data in the above format.  A single column with Osref will be accepted instead of latitude and longitude columns."]
["Then select OSGB > ExportGPX"]
[]
["Note that the order of columns is unimportant but the order of the rows and GPXtype is important:"]
[]
[null,"Header row first - if missing or if not matching those above then it will fail."]
[null,"waypoints (also optional)"]
[null,"      then"]
[null,"routes each followed by one or more route points"]
[null,"     and/ or"]
[null,"tracks each followed by one or more track points."]
[]
["ExportGPX will reject any other order."]
[]
["The order of the columns is not important - the headers will be examined to determine contents."]
[]
["If any headers other than those listed above are encountered, they will be output as 'extension' data which it is assumed will "]
[" be understood be the target software or device."]
[]
["However, the spreadsheet routine cannot determine which schema describes these extensions so the GPX file will possibly fail strict validation."]
[]
["Many of the exotic columns are not required to generate working routes or tracks in a GPS device."]
["The GPXtype and location information are required but probably little else. "]

{"sheet":"CompactGPX","index":11,"n_rows":63,"n_cols":12,"rows_kept":63,"cut":false,"hidden":false,"merged":[]}
[" "]
["Compact GPX. "]
[]
["The Compact GPX routine will accept GPX data in a worksheet in either OSGB or latitude and longitude format and reduce the number of route and track points in it."]
[]
["The algorithm uses an allowable tolerance, given in metres.  The final path will not diverge from any point on the original path by more than this tolerance.  "]
[]
["Routes which have been hand generated will generally give little reduction, though automatically generated logs can give huge reductions"]
["Taking a long track log of 11000 points as an example: "]
[null,"a zero tolerance (eliminate duplicated points and those in a straight line) gave a 26% reduction, "]
[null,"a tolerance of 2 metres gave 88% reduction,"]
[null,"a 10 metre tolerance gave a 98% reduction (ie 50:1)."]
[]
["If the data is subsequently plotted (eg with Google Earth) then the reduction in plotting times and the improved responsiveness can be dramatic."]
[]
["As an example:"]
[]
["On the left is a small section of the Thames footpath with points as logged by a GPS receiver.  It has 31 points."]
["On the right, the same section has been compressed with a tolerance of 5 metres.  This has 6 points. "]
[]
["This code as been written for walking routes and uses simple rather than great circle distance calculations.  Use for aviation or shipping is not recommended!! "]
[]
["To invoke the compact routine, select the data then choose:",null,null,null,null,null,"OSGB > Compact Selected GPX Points"]
[]
["The routine will tame data which could otherwise be quite indigestible to your system with a mapping application.  "]
[]
[]
[]
[null,"Original track log",null,null,null,null,null,"Compacted version with 5m tolerance."]
[]
[null,"GPXtype","name","latitude","longitude","ele",null,"GPXtype","name","latitude","longitude","ele"]
[null,"trk","THAMESD",null,null,null,null,"trk","THAMESD"]
[null,"trkpt",null,51.681185178500002,-1.6671217326000001,78.97,null,"trkpt",null,51.681185178500002,-1.6671217326000001,78.97]
[null,"trkpt",null,51.681198170400002,-1.6670307051,78.97,null,"trkpt",null,51.6811757069,-1.666035438,78.48]
[null,"trkpt",null,51.681200517299999,-1.6669013723999999,78,null,"trkpt",null,51.6812575143,-1.6658664588000001,78]
[null,"trkpt",null,51.681196242600002,-1.6666503344000001,78.48,null,"trkpt",null,51.681865202300003,-1.6660324205000001,77.52]
[null,"trkpt",null,51.681176545100001,-1.6664401162,78.97,null,"trkpt",null,51.681976346299997,-1.6651353054,78]
[null,"trkpt",null,51.681162714999999,-1.6663448139999999,78.48,null,"trkpt",null,51.681798482300003,-1.6646178066999999,78]
[null,"trkpt",null,51.6811709292,-1.6662582289000001,78.48]
[null,"trkpt",null,51.681165481000001,-1.6661145631000001,78.97]
[null,"trkpt",null,51.6811757069,-1.666035438,78.48]
[null,"trkpt",null,51.681192470699997,-1.6659951209999999,78.48]
[null,"trkpt",null,51.6812575143,-1.6658664588000001,78]
[null,"trkpt",null,51.681432528400002,-1.6659226174999999,77.52]
[null,"trkpt",null,51.681498745500001,-1.6659560613,78]
[null,"trkpt",null,51.681555993899998,-1.6659599169999999,78]
[null,"trkpt",null,51.681686583900003,-1.6660094540999999,79.930000000000007]
[null,"trkpt",null,51.681693708499999,-1.6660262179000001,80.41]
[null,"trkpt",null,51.681693708499999,-1.6660262179000001,79.45]
[null,"trkpt",null,51.681702006599998,-1.6660223622000001,79.45]
[null,"trkpt",null,51.681846762100001,-1.6660514474000001,78]
[null,"trkpt",null,51.681865202300003,-1.6660324205000001,77.52]
[null,"trkpt",null,51.681895879999999,-1.6658424027000001,77.040000000000006]
[null,"trkpt",null,51.6819284018,-1.6656451765,77.52]
[null,"trkpt",null,51.681933933899998,-1.6655444261000001,78]
[null,"trkpt",null,51.681962851400002,-1.6654142551,78]
[null,"trkpt",null,51.681976346299997,-1.6651353054,78]
[null,"trkpt",null,51.681966539500003,-1.6650838404999999,77.040000000000006]
[null,"trkpt",null,51.681887665799998,-1.6648724489,77.52]
[null,"trkpt",null,51.6818622686,-1.6648250072999999,77.040000000000006]
[null,"trkpt",null,51.681850450100001,-1.6647735424000001,78]
[null,"trkpt",null,51.6818376258,-1.6647427807999999,78]
[null,"trkpt",null,51.681798482300003,-1.6646178066999999,78]

{"sheet":"KML","index":12,"n_rows":59,"n_cols":8,"rows_kept":59,"cut":false,"hidden":false,"merged":[]}
[]
["Export to KML file."]
[]
["This routine was extensively changed in August 2013 to improve performance with large data sets."]
[]
["The ExportKML routine will output data to a file suitable for input to Googe Earth ™. "]
[]
["It will mark points with 'pins' on the globe, place lines joining a series of points or place both pins and lines."]
["The 'pins' on the map can optionally have a name alongside them, and if the pin is 'clicked' a bubble will be shown with extra data displayed."]
["The routine will also handle GPX imported data."]
[]
["Required Input"]
[]
["The input data should be in columns, the first row of which must be a header describing the data.  A few examples you might like to experiment with:"]
[]
["Either of these sets of data will plot two points in the Lake District and place pins there or join them with a line (or both)."]
[]
[null,"Osref",null,"Latitude","Longitude"]
[null,"NY 39629 08732","or",{"$f":"[1]!latitude(84,B19)","v":54.470352172851563},{"$f":"[1]!longitude(84,B19)","v":-2.9330248832702637}]
[null,"NY 34686 05138",null,{"$f":"[1]!latitude(84,B20)","v":54.437442779541016},{"$f":"[1]!longitude(84,B20)","v":-3.0084929466247559}]
[]
["Just select the data (including header) then select from the main menu bar:      OSGB > ExportKML"]
[]
[]
["If you wish to place a name against each pin, include a name column."]
[]
[null,"Osref","Name",null,"Latitude","Longitude","Name"]
[null,"NY 39629 08732","Red Screes","or",54.470367431640625,-2.9330439567565918,"Red Screes"]
[null,"NY 34686 05138","Loughrigg Fell",null,54.437454223632813,-3.0085139274597168,"Loughrigg Fell"]
[]
[]
["In this example we add extra data showing the the height, the day we went there, the weather and what we had for lunch."]
["If you choose to plot 'pins' then a bubble holding the data is shown if you click the pin."]
[]
[]
[null,"Name","Osref","Height","Day","Weather","Lunch"]
[null,"Red Screes","NY 39629 08732",758,"Friday","Brilliant sunshine","Flapjack & tea"]
[null,"Loughrigg Fell","NY 34686 05138",305,"Monday","Windy","Fruitcake & coffee"]
[]
[]
["or","Name","Latitude","Longitude","Height","Day","Weather","Lunch"]
[null,"Red Screes",54.470367431640625,-2.9330439567565918,758,"Friday","Brilliant sunshine","Flapjack & tea"]
[null,"Loughrigg Fell",54.437454223632813,-3.0085139274597168,305,"Monday","Windy","Fruitcake & coffee"]
[]
["The user is invited to choose their own more interesting data categories such as temperature, butterfly wingspan, traffic densities, mast height  etc. "]
[]
[]
["GPX Imported data"]
[]
["If the data includes a heading of GPXType then the routine will assume you are presenting imported GPX data. "]
["In this case, it will give you the option of plotting waypoints, routepoints, routes, trackpoints or tracks."]
["Waypoints will be shown in yellow, routes in red and tracks in green."]
[]
["Notes:"]
[]
["The headings 'GPXType', 'Osref', 'Longitude', 'Latitude' and 'Name' are special but any other headings can be used. "]
["The columns can be in any order."]
[]
["If latitude and longitude are specified they must be with reference to WGS84 datum."]

{"sheet":"ZipCodes","index":13,"n_rows":79,"n_cols":6,"rows_kept":79,"cut":false,"hidden":false,"merged":[]}
[]
["Postcodes and Zipcodes"]
[]
["Zipcode translation is provided on an experimental basis (June 2012), but there are limitations which the user should be aware of."]
[]
["The custom worksheet functions:"]
[]
[null,"=ZipLatitude(postcode)"]
[]
[null,"=ziplongitude(postcode)"]
[]
[null,"=zipOSRef(postcode)"]
[]
["return latitude, longitude and OS reference respectively for the given postcode."]
[]
["eg"]
[null,"Name","Postcode","Osref","Latitude","Longitude"]
[null,"Buckingham Palace press office","SW1A1AA",{"$f":"[1]!ziposref(C18)","v":"TQ 29091 79639"},{"$f":"[1]!ziplatitude(C18)","v":51.500957489013672},{"$f":"[1]!ziplongitude(C18)","v":-0.14156852662563324}]
[]
[]
["However, an understanding of how they function is necessary to their successful use."]
[]
["      The data is extracted by a 'program' which acesses a web site.  In the case of Britain, the web site is www.streetmap.co.uk"]
["      Since the format of the web sites may change, the 'program' needs to be soft and changeable by the user."]
["      Also, to reduce web traffic, the extracted data is cached."]
[]
["The 'program' and the cache are both held in the OSGB addin but can be extracted with "]
["      OSGB > Extract zip data"]
[]
["It can be re-inserted with"]
["    OSGB > Insert new zip data."]
[]
["The zip data has 4 columns; the program, the postcode, latitude and longitude thus:"]
[]
[null,"Program","Postcode","Latitude","Longitude"]
[null,"open","SA28PP",51.609577178955078,-3.9806892871856689]
[null,"https://streetmap.co.uk/postcode/#"]
[null,"#"]
[null,"find"]
[null,"_SM_locationLX ="]
[null,"Eastings"]
[null,"find"]
[null,"_SM_locationLY ="]
[null,"Northings"]
[null,"end"]
[]
[]
["This program opens the streetmap url after replacing the # by the postcode,"]
["It finds the text preceding the eastings figure, then extracts the eastings following the text, "]
["does likewise for northings, before ending."]
[]
["Other instructions are latitude, longitude, and a skip function to skip N characters in the input (may be negative)."]
[]
["I hope that users will find their own web sites and develop their own simple programs.  Simply extract the data, edit it then insert it."]
["If changing country, remove the cached data but not the headings."]
["Note that the cached zip/postcodes contain only alpha numeric characters and that they are limed to 10 characters.."]
[]
["This worked with the very limited testing I've tried with USA."]
[]
[null,"Program","Postcode","Latitude","Longitude"]
[null,"open","337014313",27.769443511962891,-82.636085510253906]
[null,"http://www.melissadata.com/lookups/GeoCoder.asp?InData=#&submit=Search"]
[null,"#"]
[null,"find"]
[null,">Latitude"]
[null,"find"]
[null,"class='padd'>"]
[null,"latitude"]
[null,"find"]
[null,">Longitude"]
[null,"find"]
[null,"class='padd'>"]
[null,"longitude"]
[null,"end"]
[]
["If you find that your program does not work as expected, then you can use the diagnostic trace facility which will 'single step' the program."]
["Select a cell with a test postcode and click OSGB > Trace Zip lookup."]
[]
["If you put postcodes/zipcodes in manually, you may need to start them with the single quote character '"]

{"sheet":"VBA code","index":14,"n_rows":12,"n_cols":2,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
["VBA code"]
[]
["If you wish to access the osgb routines from your own VBA module then the Application.Run directive will achieve this."]
[]
["Example:"]
[]
[null,"Dim easting As Long"]
[null,"Dim osref As String"]
[null,"easting = Application.Run(\"easting\", \"sh123456\")"]
[null,"osref = Application.Run(\"osref\", 84, 51, -2)"]
[null,"MsgBox “easting=” & CStr(easting) & \"  osref= \" & osref"]

{"sheet":"Example Application","index":15,"n_rows":118,"n_cols":26,"rows_kept":118,"cut":false,"hidden":false,"merged":[[34,16,34,17],[1,14,1,15],[34,10,34,12],[5,10,5,12],[103,6,103,8],[103,10,103,12],[98,10,98,12],[67,10,67,12],[65,17,65,19],[1,3,1,8],[39,6,39,8],[39,10,39,12],[1,11,1,12],[1,16,1,17]]}
[]
[null,"help",null,"OS Reference, Distance and Bearing Calculator",null,null,null,null,null,null,"Operator:",null,null,null,"Time:",null,{"$f":"IF(M114=\"\",\"\",NOW())","v":"2025-05-03T16:08:47.600"}]
[]
[]
[null,"Point 1"]
[null,null,null,null,"Format or fixed point",null,{"$f":"INDEX(G10:G34,I7)","v":"OS Reference"},null,{"$f":"INDEX(I10:I34,I7)","v":"Ignore this box"},null,"Description"]
[null,null,null,null,null,"Index calc:",{"$f":"MATCH(E35,E11:E34,0)","v":22},null,{"$f":"IF(ISNA(G7),1,G7+1)","v":23}]
[]
[null,null,null,null,null,null,"Long header",null,"Lat header",null,"Osref calculation",null,{"$f":"INDEX($K$10:$K$34,$I$7)","v":"TQ123456"}]
[null,null,null,null,null,null,"Please set format !",null,"Please set format !",null,{"$f":"E35","v":"Osreference"}]
[null,null,null,null,{"$f":"IF(ISBLANK(R99),\".\",R99)","v":"Llandonna"},null,"ignore this box",null,"ignore this box",null,{"$f":"T99","v":"SH571797"}]
[null,null,null,null,"Great Orme",null,"ignore this box",null,"ignore this box",null,"SH767834"]
[null,null,null,null,"Nebo",null,"ignore this box",null,"ignore this box",null,"SH476494"]
[null,null,null,null,"Crystal Palace",null,"ignore this box",null,"ignore this box",null,"TQ337707"]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0,null,null,null,null,null,"Index ->",{"$f":"ISNUMBER(R37)+ISNUMBER(R38)*2+1","v":1}]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0,null,null,null,null,null,"Used:","Grid values:"]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0,null,null,null,null,null,"Auto",{"$f":"R39","v":-0.11092961579561234}]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0,null,null,null,null,null,"Manual/true",{"$f":"R37+[1]!TrueVar(M9)","v":-1.2545166015625}]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0,null,null,null,null,null,"Manual/grid",{"$f":"R38","v":0}]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0,null,null,null,null,null,"Manual/grid",{"$f":"R38","v":0}]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,".",null,"ignore this box",null,"ignore this box",null,0]
[null,null,null,null,"Osreference",null,"OS Reference",null,"Ignore this box",null,{"$f":"IF(ISBLANK(G35),\"\",G35)","v":"TQ123456"}]
[null,null,null,null,"OS1936",null,"longitude",null,"Latitude",null,{"$f":"[1]!OSref(36,[1]!textdegreestodec(I35),[1]!textdegreestodec(G35))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"WGS84",null,"Longitude",null,"Latitude",null,{"$f":"[1]!OSref(84,[1]!textdegreestodec(I35),[1]!textdegreestodec(G35))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Osreference",null,"TQ123456",null,null,null,null,null,null,null,null,null,"Magnetic Variation"]
[]
[null,null,null,null,null,null,"OS Digits required",null,"Value",null,null,null,null,null,null,null,"Manual/True"]
[null,null,null,null,"Osreference   ->",null,6,null,{"$f":"[1]!tidyref(M9,G38,1)","v":"TQ 123 456"},null,null,null,null,null,null,null,"Manual/grid"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Auto",{"$f":"[1]!MagGrid(M9)","v":-0.11092961579561234}]
[null,null,null,null,null,null,"Longitude",null,null,null,"Latitude"]
[null,null,null,null,"OS 1936    ->",null,{"$f":"[1]!longitude(36,M9)","v":-0.39254665374755859},"=",{"$f":"[1]!dms(G41)","v":"  0º23'33\"W"},null,{"$f":"[1]!latitude(36,M9)","v":51.198051452636719},"=",{"$f":"[1]!dms(K41)","v":" 51º11'53\"N"},null,null,null,"Used:",{"$f":"INDEX(Q22:Q25,R19)","v":"Auto"}]
[]
[null,null,null,null,"WGS84     ->",null,{"$f":"[1]!longitude(84,M9)","v":-0.39411050081253052},"=",{"$f":"[1]!dms(G43)","v":"  0º23'39\"W"},null,{"$f":"[1]!latitude(84,M9)","v":51.198593139648438},"=",{"$f":"[1]!dms(K43)","v":" 51º11'55\"N"}]
[]
[]
[]
[null,"Projection"]
[null,null,null,null,"Projection: Yes/No?",null,{"$f":"INDEX(G50:G52,I49)","v":"Bearing˚"},null,{"$f":"INDEX(I50:I52,I49)","v":"Which north?"},null,{"$f":"INDEX(K50:K52,I49)","v":"Distance"},null,{"$f":"INDEX(M50:M52,I49)","v":"Enter units"}]
[null,null,null,null,"Index ->",null,{"$f":"MATCH(E63,E61:E62,0)","v":1},null,{"$f":"IF(ISNA(G49),1,G49+1)","v":2}]
[null,null,null,null,"Projection yes/no?",null,"<- enter yes/no",null,"<- enter yes/no",null,"<- enter yes/no",null,"<- enter yes/no"]
[null,null,null,null,"ignore",null,"Bearing˚",null,"Which north?",null,"Distance",null,"Enter units"]
[null,null,null,null,"Format or location",null,"ignore",null,"ignore",null,"ignore",null,"ignore"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Whitaker",{"$f":"MATCH(I63,I60:I62,0)","v":2},null,null,{"$f":"MATCH(M63,M54:M62,0)","v":8}]
[null,null,null,null,null,null,null,null,null,"Units and factors here->",null,null,null,null,null,null,"Metres/unit","Distance",null,null,null,2011,{"$f":"IF(ISNA(W54),1,W54+1)","v":3}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Metres",null,null,null,1,{"$f":"K63*INDEX(Q54:Q62,Z54)","v":32186.880000000001},null,null,null,50,null,null,"Vars"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Nautical mile",null,null,null,1852,null,null,null,null,0,null,null,"#error"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Yards",null,null,null,0.91439999999999999,null,null,null,null,-60,null,"Grid",0]
[null,null,null,null,null,null,null,null,null,null,null,null,"kilometers",null,null,null,1000,null,null,null,null,-11,null,"true",{"$f":"[1]!TrueVar(M9)","v":-1.2545166015625}]
[null,null,null,null,null,null,null,null,"Grid north",null,null,null,"km",null,null,null,1000,null,null,null,null,26,null,"Mag",{"$f":"INDEX(R22:R25,R19)","v":-0.11092961579561234}]
[null,null,null,null,"Yes",null,null,null,"True north",null,null,null,"statute miles",null,null,null,1609.3440000000001,null,null,null,null,11]
[null,null,null,null,"No",null,null,null,"Magnetic north",null,null,null,"miles",null,null,null,1609.3440000000001]
[null,null,null,null,"yes",null,2,null,"True north",null,20,null,"statute miles"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Insert your fixed points below"]
[null,"Point 2"]
[null,null,null,null,{"$f":"IF(E63=\"Yes\",\"Ignore this\",\"Format or fixed point\")","v":"Ignore this"},null,{"$f":"INDEX(G72:G98,K69)","v":"Ignore"},null,{"$f":"INDEX(I72:I98,K69)","v":"Ignore"},null,"Description",null,null,null,null,null,null,"Name",null,"OS reference"]
[null,null,null,null,null,"Index calc:",{"$f":"3+MATCH(E99,E75:E98,0)","v":4},"->",{"$f":"IF(ISNA(G69),3,G69)","v":4},"->",{"$f":"IF((I49<3),I49,I69)","v":2}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Index with yes/no"]
[null,null,null,null,null,null,"Longitude header",null,"Latitude header",null,"Osref calculation",null,{"$f":"INDEX(K72:K98,K69)","v":"TQ 12719 77784"},null,null,null,null,"Bearing"]
[null,null,null,null,null,1,"Projection yes/no?",null,"Projection yes/no?",null,null,"<-","yes/no unset",null,null,null,null,{"$error":"#N/A"}]
[null,null,null,null,null,2,"Ignore",null,"Ignore",null,{"$f":"[1]!Target(M9,R56,G63,INDEX(Y57:Y60,W55))","v":"TQ 12719 77784"},"<-","projecting bearing",null,null,null,null,{"$f":"[1]!Normalised(G63+INDEX(Y57:Y60,W55),0)","v":0.7454833984375}]
[null,null,null,null,null,3,"Please set format !",null,"Please set format !",null,null,"<-","format/location unset",null,null,null,null,{"$f":"[1]!bearing(M9,M71,0)","v":0.74588537216186523}]
[null,null,null,1,{"$f":"IF(ISBLANK(R99),\".\",R99)","v":"Llandonna"},4,"ignore this box",null,"ignore this box",null,{"$f":"T99","v":"SH571797"}]
[null,null,null,2,{"$f":"IF(ISBLANK(R100),\".\",R100)","v":"Great Orme"},5,"ignore this box",null,"ignore this box",null,"SH767834",null,"Copy of fixed points"]
[null,null,null,3,{"$f":"IF(ISBLANK(R101),\".\",R101)","v":"Nebo"},6,"ignore this box",null,"ignore this box",null,"SH476494"]
[null,null,null,4,{"$f":"IF(ISBLANK(R102),\".\",R102)","v":"Crystal Palace"},7,"ignore this box",null,"ignore this box",null,"TQ337707"]
[null,null,null,5,{"$f":"IF(ISBLANK(R103),\".\",R103)","v":"."},8,"ignore this box",null,"ignore this box",null,0]
[null,null,null,6,{"$f":"IF(ISBLANK(R104),\".\",R104)","v":"."},9,"ignore this box",null,"ignore this box",null,0]
[null,null,null,7,".",10,"ignore this box",null,"ignore this box",null,0]
[null,null,null,8,".",11,"ignore this box",null,"ignore this box",null,0]
[null,null,null,9,".",12,"ignore this box",null,"ignore this box",null,0]
[null,null,null,10,".",13,"ignore this box",null,"ignore this box",null,0]
[null,null,null,11,".",14,"ignore this box",null,"ignore this box",null,0]
[null,null,null,12,".",15,"ignore this box",null,"ignore this box",null,0]
[null,null,null,13,".",16,"ignore this box",null,"ignore this box",null,0]
[null,null,null,14,{"$f":"IF(ISBLANK(R112),\".\",R112)","v":"."},17,"ignore this box",null,"ignore this box",null,0]
[null,null,null,15,".",18,"ignore this box",null,"ignore this box",null,0]
[null,null,null,16,".",19,"ignore this box",null,"ignore this box",null,0]
[null,null,null,17,".",20,"ignore this box",null,"ignore this box",null,0]
[null,null,null,18,".",21,"ignore this box",null,"ignore this box",null,0]
[null,null,null,19,".",22,"ignore this box",null,"ignore this box",null,0]
[null,null,null,20,".",23,"ignore this box",null,"ignore this box",null,0]
[null,null,null,21,".",24,"ignore this box",null,"ignore this box",null,0]
[null,null,null,22,"Osreference",25,"OS Reference",null,"Ignore this box",null,{"$f":"IF(ISBLANK(G99),\"\",G99)","v":""}]
[null,null,null,23,"OS1936",26,"longitude",null,"Latitude",null,{"$f":"[1]!OSref(36,[1]!textdegreestodec(I99),[1]!textdegreestodec(G99))","v":{"$error":"#VALUE!"}}]
[null,null,null,24,"WGS84",27,"Longitude",null,"Latitude",null,{"$f":"[1]!OSref(84,[1]!textdegreestodec(I99),[1]!textdegreestodec(G99))","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Llandonna",null,null,null,null,null,null,null,null,null,null,null,null,"Llandonna",null,"SH571797"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Great Orme",null,"SH767834"]
[null,null,null,null,null,null,"OS Digits required",null,"Value",null,null,null,null,null,null,null,null,"Nebo",null,"SH476494"]
[null,null,null,null,"Osreference   ->",null,10,null,{"$f":"[1]!tidyref(M71,G102,1)","v":"TQ 12719 77784"},null,null,null,null,null,null,null,null,"Crystal Palace",null,"TQ337707"]
[]
[null,null,null,null,null,null,"Longitude",null,null,null,"Latitude"]
[null,null,null,null,"OS 1936    ->",null,{"$f":"[1]!longitude(36,M71)","v":-0.37635725736618042},"=",{"$f":"[1]!dms(G105)","v":"  0º22'35\"W"},null,{"$f":"[1]!latitude(36,M71)","v":51.487274169921875},"=",{"$f":"[1]!dms(K105)","v":" 51º29'14\"N"}]
[]
[null,null,null,null,"WGS84     ->",null,{"$f":"[1]!longitude(84,M71)","v":-0.37793448567390442},"=",{"$f":"[1]!dms(G107)","v":"  0º22'41\"W"},null,{"$f":"[1]!latitude(84,M71)","v":51.487781524658203},"=",{"$f":"[1]!dms(K107)","v":" 51º29'16\"N"}]
[]
[]
[]
[null,"Bearings"]
[null,"    &",null,null,"Point 1 -> Point 2"]
[null,"Distances",null,null,null,null,"Grid bearing˚",null,"True bearing˚",null,"Magnetic bearing˚",null,"Distance"]
[null,null,null,null,"Bearing",null,{"$f":"INDEX(R72:R74,I49,0)","v":0.7454833984375},null,{"$f":"[1]!Normalised(G114-Y59,0)","v":2},null,{"$f":"[1]!Normalised(G114-Y60,0)","v":0.85641300678253174},null,{"$f":"[1]!hypotenuse(M9,M71)","v":32186.7265625},null,"Metres"]
[null,null,null,null,"Back bearing",null,{"$f":"[1]!Normalised(180+G114,0)","v":180.7454833984375},null,{"$f":"[1]!Normalised(180+I114,0)","v":182},null,{"$f":"[1]!Normalised(180+K114,0)","v":180.85641479492188},null,{"$f":"M114/1000","v":32.186726562499999},null,"Km"]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"M114/Q62","v":19.999904658357689},null,"Miles"]
[null,null,null,null,null,null,{"$f":"IF(YEAR(TODAY())>2022,\"Warning !!!    Using 2019 magnetic variation data - consider updating it!\",\"\")","v":"Warning !!!    Using 2019 magnetic variation data - consider updating it!"},null,null,null,null,null,{"$f":"M114/Q58","v":35199.832198709533},null,"Yards"]
[null,null,null,null,null,null,{"$f":"IF(M115>100,\"Warning!!  Calculations are unreliable for distances over 100km\",\"\")","v":""},null,null,null,null,null,{"$f":"M114/Q57","v":17.37944198839093},null,"N Miles"]