Calculating an Expanding Square

by Allen Wyatt
(last updated January 28, 2016)

6

Fernando works with a search and rescue team. When they search for a person in an area, they typically do so using what is called an "expanding square." Given a starting location, they move east a given distance, turn and go north the same distance, go west twice the distance, go south twice the distance, etc., in an ever-expanding square. Fernando would like to provide the beginning coordinates (latitude and longitude) and an initial distance and have Excel calculate the latitude and longitude of each turning point in the expanding square.

Before a solution can be offered, a few decisions need to be made. First, you need to figure out if you are going to specify your GPS coordinates as degrees/minutes/seconds or as decimal degrees. For the sake of this particular solution, the decision was made (for no particular reason) to use decimal degrees when specifying latitude and longitude.

You also need to figure out if you are going to specify the legs of your square in miles or feet. Since Fernando works with a search and rescue team that is probably covering the terrain by foot (rather than by air), the decision was made to allow specifying the distances in feet.

Given these decisions, you can approximate how many degrees latitude or longitude is changed for each foot moved directly east, west, north, or south. This is given in the following formula:

=ATAN((1/5280)/3958.82)*180/PI()

In this formula you convert the feet to miles (1/5280) and then divide it by the mean radius of the Earth (3958.82). The solution is that you change 0.00000274109 degrees for each foot you move. If you move some other distance, you can simply change the feet-to-miles conversion to find the number of degrees. Thus, if you move 1/10 of a mile (528 feet), the formula becomes this:

=ATAN((528/5280)/3958.82)*180/PI()

In this case the change in degrees is 0.014472944 degrees. Again, this distance change is an approximation, and it doesn't take any real-world conditions into account, such as obstacles or changes in elevation.

Once you know the change for each leg of your expanding square, the only thing you need to do is calculate whether you are adding or subtracting degrees (and whether from latitude or longitude) based on the direction you are traveling on the leg. Given you know the progression of your legs (in Fernando's case, East, North, West, South), you can figure this out rather easily based on which leg is being traversed. You can also, knowing the leg number, calculate how far of a distance needs to be traveled in that particular leg.

I try very, very hard in ExcelTips to explain things in my articles to the point that you can easily recreate the necessary formulas and data in your own worksheets. In this case, however, it really would be beneficial for you to simply download a workbook that contains an "Expanding Circle Calculator." I've developed one (with input from a few ExcelTips contributors) that you can find here:

http://excelribbon.tips.net/ExpandingCircle.xlsx

To use the calculator, you only need to enter three figures: The number of feet you want to travel in the very first leg, the latitude for the starting point, and the longitude for that same point. The calculator then derives 64 legs for the expanding square, giving starting point, direction, and distance (in feet) for each leg.

If you want to learn more about latitudes, longitudes, and distances, you will find this information helpful:

http://www.cpearson.com/Excel/LatLong.aspx

I also found the discussion on the Haversine formula to be interesting, at Wikipedia. (The Haversine formula is used to calculate great-circle distances between two pairs of latitude/longitude coordinates.

http://en.wikipedia.org/wiki/Haversine_formula

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5645) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Specifying a Data Validation Error Message

Data validation is a great tool for limiting what can be input into a cell. Excel allows you to specify what should appear on ...

Discover More

Running Macros Based on Keywords

Wouldn't it be great if Word could execute a macro every time someone typed in a particular keyword or phrase? Word may not ...

Discover More

Highlighting Duplicate Words

One way to help improve your writing is to minimize the number of duplicated words you use in your prose. Depending on the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Tracking Down Invalid References

When you discover that there is an invalid reference in a workbook, you can have a bear of a time tracking down the problem. ...

Discover More

Summing Only Positive Values

If you have a series of values and you want to get a total of just the values that meet a specific criteria, then you need to ...

Discover More

Reordering Last Name and First Name

If you've got a list of names in a column, you may want to change the order of each name. For instance, the name have the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 1?

2016-01-28 11:27:47

Ben Brock

Latitude and longitude do not refer to magnetic north.

Recognizing that these are just approximations, the spreadsheet still works well, but the step size in degrees for longitude needs to be corrected by dividing by the cosine of the latitude at each point. This corrects for the change in distance covered by a degree of longitude as the latitude changes (the polar problem referenced below).


2016-01-28 10:38:37

Susan

While the solution is definitely not as good as the solution from a geographic information system, it is a very quick approximation to support the situation. When you map the output coordinates of the spreadsheet with GIS software, it gives you an "X" shape for the points, and if you connect the points in the order provided in the "Leg" column, it provides an expanding open rectangular line with the longest side of the rectangle being in the North-South orientation. The location in the spreadsheet is in China, but you get a similar shape if you are mapping a location in the US. The shape would be more like a square near the equator. In northern Canada (or near either of the polar regions), the lines would be skewed with very long North-South lines compared to the East-West lines. For most of the populated areas of the world, the solution would work OK even if it is a skewed rectangle rather than a square search area.


2013-06-23 17:14:21

Aldo

Longitude and latitude are basically a percentage of a sphere. It does not matter how big the sphere is, the figures are always the same. Also they are related to magnetic north in the case of our "sphere", not to polar north, so standard deviation needs to be considered within the calculation.

All this is straightforward to code in VBA. Each map or chart will have this information on it. It will also give you a scale to work with. These factors must be considered when created an Excel sheet to create a search grid. You are NOT dealing with a true square. In fact the shape is more like a piece of an orange peel. It is a curved trapezoid or several curved triangles.

This solution will take more calculations then a simple ATAN() trig function in a cell.


2013-06-23 05:31:35

Martin Nicol

The polar search does indeed make this interesting. There would be no northern treaks and the first leg could only be in a southern direction, then the eastern leg would be a near full circle.
Also there is a real world issue that will determine the distance chosen. It has to be visibility distance. This would means the eastern leg would arc through 300 degrees. At this point searchers would be the same distance from the North pole and their first turn east, forming an equilateral triangle. In the polar pattern searches are never more than the starting distance from there last search path but in the square pattern corners are a greater distance.


2013-06-22 08:55:07

Tomas Wallgren

There is a big problem with this solution. The angular change in east-west as a function of distance travel varies depending on your north-south position.

Just south of the north pole, you might very well complete a full 360°around the earths rotational axis in a few steps. At the equator it takes a 40000 km walk to accomplish the same angular journey.

So the change in longitude depends on the latitude which must be taken care of in the calculation.

Furthermore the earths radius varies, but for a search and rescue mission this is probably a non issue when it comes to the accuracy of the expanding square.


2013-06-22 08:52:25

Tomas

There is a big problem with this solution. The angular change in east-west as a function of distance travel varies depending on your north-south position.

Just south of the north pole, you might very well complete a full 360°around the earths rotational axis in a few steps. At the equator it takes a 40000 km walk to accomplish the same angular journey.

So the change in longitude depends on the latitude which must be taken care of in the calculation.

Furthermore the earths radius varies, but for a search and rescue mission this is probably a non issue when it comes to the accuracy of the expanding square.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.