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.
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.
Learn more about Allen...
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:
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:
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:
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:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5645) applies to Microsoft Excel 2007, 2010, and 2013.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!