Microsoft’s spreadsheet software EXCEL is used to manipulate and analyze all types of data. However, the product is oriented to business so most of the built-in functions are specific to business and include only basic math/science functions. Fortunately, Microsoft developed EXCEL to be extendible through the Visual Basic for Applications (VBA) language. This capability allows the user to develop their own functions and subroutines and to incorporate them easily into EXCEL. In response to my own needs with the analysis of marine mammal survey data and other types of data, I have written various EXCEL functions that perform trigonometric calculations for plane and spherical geometry and a number of other related calculations. I have compiled these functions into a single EXCEL add-in file - after downloading, double-click on the Geofunc.exe icon to extract geofunc.xla file! (Download self extracting zip file here). Functions within EXCEL such as Solver and wizards are add-in files. You can use this add-in file by simply copying the file to the appropriate XLSTART sub-directory or in the AddIns sub-directory and use Add-in under the Tools menu item. Or you download the text file and paste them into the VB editor. By examining the text file you can see the code used to make the computations and more complete documentation. The text file should also work for a Mac but I've not tested it. For complete instructions consult EXCEL help.

Each time EXCEL loads, it will load the Geofunc.xla file and all of the functions it contains will be available. The functions can be used like any other EXCEL built-in function by typing them into a formula with the appropriate arguments. If you use functions through the fx icon, the functions in Geofunc will be listed in alphabetical order under the User-defined category.

The Visual Basic code for each function is listed below with comments that describe what the function does and the assumed input and output measurement units. The functions are organized alphabetically as follows:

Spherical (Earth) Geometry: Angle & Distance Measurements

The earth is approximately spherical, so trigonometric relationships between positions on the earth’s surface can be approximated with spherical trigonometry. The appropriate formulas were used from pages 176-177 in the Standard Mathematical Tables 24th edition, CRC Press. All units for latitude and longitude and bearing are in decimal degrees [e.g., 100 degrees, 30 minutes and 50 seconds corresponds to 100.5139 = 100 + (30 + 50/60)/60 in decimal degrees]. Distance units are nautical miles (1 nautical mile = 1852 meters). Northern latitudes and eastern longitudes are specified as positive values and their counterparts are negative.

1. Bearing(Lat1, Lon1, Lat2, Lon2)NewPosLat(Lat1, Lon1, Bearing, Distance)NewPosLon(Lat1, Lon1, Bearing, Distance)

2. Posdist(Lat1, Lon1, Lat2, Lon2)

Geographic Position Unit Conversions

Geographic positions are expressed in (1) decimal degrees (degrees.ddd), (2) degrees and minutes (mm) and decimal minutes (tt) (degrees.mmtt), and (3) degrees, minutes (mm) and seconds (ss) (degrees.mmss). All of the spherical functions above assume positions are expressed in decimal degrees. To convert between the various units, the following simple conversion functions were included. The argument format must match the specified format. The routines can be easily modified to match other input and output formats. Most GPS positions typically default to the degrees and decimal minutes format (e.g. 45 minutes and 30 seconds is 45.5 minutes).DegToMinSec(DecDeg)

1. DegToMinSec(DecDeg)DegToMinTen(DecDeg)MinSecToDeg(MinSec)

2. MinTenToDeg(MinTen)

Survey Distance Measurements

In aerial, shipboard and land-based surveys, the distance is often measured between the observer and the observation (e.g., animal). These distances are measured as a vertical angle from the horizon with reticle binoculars in shipboard or shore-based surveys or from horizontal with an inclinometer in aerial surveys.

From shipboard and shore-based surveys, distance is measured with binoculars that contain reticles which measure an angle from the horizon. The appropriate formulas are described by Lerczak and Hobbs (1998) [Calculating sighting distance from angular readings during shipboard, aerial and shore-based marine mammal surveys, Marine Mammal Science 14(3):590-599; see also Errata in vol 4, pg 903]. Note that the formula used in RetDist is a slightly different parameterization of the Lerczak and Hobbs (1998) formula and it returns the line of sight distance (denoted D0). To obtain the distance along the ground (D) as in Lerczak and Hobbs (1998) use the approximation: D~sqrt(D02 - h2), where h is the height of the observer’s eye measured in the same units as D0. For most observation platforms D and D0 are very close when used with reticle binoculars because the closest distance within the field of view is much larger than the observation height. RetDist7x50 uses RetDist with the radians per reticle specified as 0.00497 which is the empirical value we derived for Fujinon 7x50. Likewise, RetDistBE is for 25x binoculars that the National Marine Fisheries Service uses. The function DistRet provides an approximate inverse function (i.e., computes the reticle from the distance). For the Reticle-Distance functions, distance units are nautical miles (1 nautical mile = 1852 meters) and platform heights units are meters.

In aerial surveys, distance can be measured with a known altitude and a vertical angle (0 when horizontal and 90 when directly below) often measured with an inclinometer. For most survey altitudes, distance can be accurately computed with a simple tangent formula which is computed in ClinoDist. For ClinoDist, any units can be used because the output distance units are the same as the input units for Altitude. If distances are large or survey altitudes are very high (> 300 m), a more accurate formula is given by the ClinoArcDist function. This function uses the arc distance formula (see reference to Lerczak and Hobbs 1998 listed below) rather than the simpler tangent formula in ClinoDist. Technically, ClinoArcDist is more accurate for inclinometer measurements at small angles for aerial surveys, but for practical survey altitudes less than 300 m, the difference in the results are less than 1%, unless the angle is less than 3 degrees (distance > 3 nm). With the unaided eye, it will only be possible to see fairly large objects at that distance. For most aerial survey work, the differences in these formulas are inconsequential. For ClinoArcDist, output distance units are nautical miles and altitude must be specified in meters. You can compare the two functions by comparing ClinoArcDist(Altitude,Angle) to ClinoDist(altitude,angle)/1852 for a specific altitude in meters and for a range of angles. The answers should be very close except for small angles and large altitudes.

1. ClinoArcDist(Altitude, Angle)ClinoDist(Altitude, Angle)DistRet(Height, RadPerReticle, Distance)RetDist(Height, RadPerReticle, Reticles)RetDistBE(Height, Reticles)

2. RetDist7x50(Height, Reticles)

Plane Geometry - Oblique Triangle Formulas

It is relatively easy to remember and to include a formula for right triangles, but how many of you remember your geometry well enough to recall oblique triangle formulas. I surely didn’t when I was confronted with some oblique geometry while in the field. Luckily, a colleague had a Rite-in-the-rain field notebook which contained the formulas on the cover. With the formulas built into EXCEL they’ll always be available. Input and output angle measurements are in decimal degrees. Output distance measurements will be the same as the input distance measurements. Make sure all input distances are based on the same units.

1. ASAb(b, a, c)ASAc(b, a, c)SASa(b, a, c)SASB(b, a, c)SASC(b, a, c)SSAB(a, c, AngleA)SSAC(a, c, AngleA)SSASb(a, c, AngleA)SSSA(a, b, c)SSSB(a, b, c)

2. SSSC(a, b, c)

Utility and Miscellaneous Functions
(some of these are already defined within EXCEL)

1. Arccos(x)Arcsin(x)ClosestDistance(x1, y1, x2, y2, xp, yp)Radians(x)

2. Sign(x)

NOTE: When referring to any of these computations, use a reference to the formula rather than a reference to this web page.

Update Notice September 21, 2001: The XLA file was updated 9/21/01. If you obtained a prior copy, you should update with the new version.

Rounding in the routine MinTenToDeg() was giving inconsistent results for the conversion to decimal degrees when the decimal portion of the minutes was 0 (e.g., 19.00 minutes). This rounding was correctly handled in MinSecToDeg() but unfortunately not in MinTenToDeg(). This new version fixes the error.

Update Notice March 22, 2000: Update Notice: Two minor changes listed below were made to the routines on 22 March 2000. If you obtained a prior copy, you should update with the new version.

1) The Bearing and NewPosLat functions were incorrectly handling degrees of 90 and 270. Both were mistakenly being used as a constant latitude which only holds at the equator. The effect of this on previous calculations should have been minor except for large distances (>100 nm).

2) The ClinoArcDist function has been added. This function uses the arc distance formula (see reference to Lerczak and Hobbs 1998 listed below) rather than the simpler tangent formula in ClinoDist. Technically, ClinoArcDist is more accurate for inclinometer measurements at small angles for aerial surveys, but for practical survey altitudes less than 300 m, the difference in the results are less than 1% unless the angle is less than 3 degrees (distance > 3 nm at 300 m altitude). With the unaided eye, it will only be possible to see fairly large objects at that distance. For most aerial survey work, the differences in these formulas are inconsequential.

Disclaimer: The add-in file is provided as a courtesy to others that may find it useful. I have checked these functions reasonably well but I make no claims about their accuracy. As with any computer software, check to make sure the answers you get make sense and are accurate. That will ensure that you understand their use. Please pay particular attention to the measurement units. If you find any errors, please notify me via email (Jeff.Laake@Noaa.Gov).