return to home page



Basics using a spreadsheet for calculating route verticals from USGS topographic maps.

by David Senesac
August 2013

Many who hike and backpack in mountains don't have a way to easily estimate how much up and down vertical they may be making when looking at a topographic map unless a trail never dips. In other words, anyone with rudimentary math skills can easily view their starting elevation that might be at 8200 feet and note how their destination lake is at 9500 feet and simply subtract the two for a net of 1300 feet uphill. But then many trails go up and down many times on their way up.

For example a trail may go uphill 500 feet from a 8600 foot trailhead over the first mile to 9100 feet then dip two hundred feet down to 8900 feet into a forested stream canyon, climb the opposite canyon wall 400 feet up to a ridgeline at 9300 feet, then descend to a stream following it down canyon 800 feet a couple miles to 8500 feet before climbing a tributary stream up 1600 feet to a destination timberline headwaters lake basin at 10,100 feet. The obvious solution is to add all the uphill sections and all the downhill sections.

up:
 500
 400
1600
------
2500 feet

down:
 200
 800
------
1000 feet

On the return the up and down are reversed so one would be climbing up 1000 feet and dropping down 2500 feet. Thus the trip would total 3500 feet up and down. For any route that starts and ends at the same location, the up and down totals ought match.

Although the math is trivial, one can set up a spreadsheet to perform the math that also provides a useful visual form and can be reused. Spreadsheets are structured with numerated rows and columns that can automatically perform math. Some cells contain raw data as numbers while other cells contain formulas that perform math and operations on cells in those formulas. A cell is addressed by a column/row number coordinate. For example a cell 3 columns from the left edge and 4 rows from the top would be C4. The syntax in these examples is for Microsoft Excel however basics in other spreadsheet applications are similar though one ought check the online guide for each. Some spreadsheets are free online like the one with Google Chrome.

In the following example where the actual work is done, there are 3 columns and 6 rows. There may also be rows for text headings and totals. The first column, say column A, is for elevations in feet where a route changes from up to down and vice versa. The second column, say column B, is for Uphill verticals and the third column, say column C, would be for Downhill verticals. At the bottom the spreadsheet would add up the total up and down columns.

For instance in the above example column A elevations would contain:

row      Elev     
A4	 8600
A5	 9100	
A6	 8900
A7	 9300
A8	 8500
A9	10100

In the B column one enters a formula. In Excel text in a cell that starts with an equal character means is a formula. The resulting value displayed is not that text but rather the number so calculated. For example at B5:

=A5-A4

That would be 9100-8600 = 500. Thus the spreadsheet would display 500 at B5. Similarly in the C column at C6:

=A5-A6

That would be 9100-8900 = 200. Thus the spreadsheet would display 200 at C6. In like manner one would put into

cell B7:	=A7-A6
cell C8:	=A7-A8
cell B9:	=A9-A8

At cell B8 one could add up the column B items by entering a formula:

=SUM(B4:B9)

At cell C8 one could add up the column C items by entering a formula:

=SUM(C4:C9)

One could also add some text for column headings, for example at A3: Elev, at B3: Up, at C3: Down. Thus the display would show:

 Elev	 Up	Down
 8600	 
 9100	 500
 8900		 200
 9300	 400
 8500		 800
10100	1600

In Excel, one does not have to enter the math into each cell but rather can simply drag the top formula down a column. For example at B5, we put:

=A5-A4

If we drag that down to B9 and likewise drag C6 down to C9, the application expects the same type of formula coordinate structure as viewed from each of those lower cells. One could also use usual copy and paste commands. The result of dragging is however several - minus values. Simply delete any cell formula showing a minus value.

When calculating values for a future trip, one can simply make a copy of the above spreadsheet file as a template master by clearing the elevation cells and dragging down the UP and down columns so formulas are in each cells. With my own system, I have sections per above for each day. My longest trips are about 10 days so I have 10 such sections that will fit on a single 8.5x11 standard sheet. Each section has room for 15 elevations. On my spreadsheets, I also have a fourth row for mileage.

Of course there is more to this than the above as one has to visually be able to estimate elevations of points on a trail in order to populate the above Elev column. So lets use this online 7.5 degree USGS topographic map in an example.

mapper.acme.com

Lets say we were calculating the route between the Virgina Lakes trailhead and the west end of Summit Lake. On the map each narrow vertical line is 40 feet of vertical and each dark line 200 feet of vertical. The trailhead shows between the first and second lines above a dark line. A bit southeast, the map shows a dark line with 10400. Thus we note our dark line is the third dark line below or 3x200=600 feet lower or 9800 feet. We can estimate the trailhead is halfway between the first and second lines so 40+20=60 feet thus 9800+60= 9860 feet. In like manner we note Summit Lake is midway below the 10200 foot dark line and the next light line so will estimate that to be 10180 feet.

 Elev	 Up	Down
 9860	 
11065	1205
10020		1045
10195	 175
10180		  15


total	1380	1060

The trail is the dashed black line. By inspection we will note the trail never dips along the route until it reaches the third light line above the 11000 dark line and barely goes above that line. Thus 11000+20+20+20+5= 11065 feet. The trail then drops down without any uphill till it reaches a stream midway between the 10000 dark line and first light line we will estimate to be 10020 feet. Finally the trail follows that stream up consistently to the east end of Summit Lake then climbs a bit to just below the 10200 foot dark line we will estimate to be 10195 feet before dropping a bit to the west end at 10180 feet.

The following is an example screen capture of a trip I will be making this month. It is a bit more involved than the description above but the basics are the same. Each time I plan a new trip, I merely copy the master template of the below and fill in the basic elevation and location data. When I make changes to a route, all the numbers are quickly readjusted. If I have sub-plans in case of usual circumstances like if weather prevents going to exposed destinations, I may have multiple spreadsheet sheets each with variations.

pbss