Attention spreadsheet geeks! | High Sierra Topix  

Attention spreadsheet geeks!

Grab your bear can or camp chair, kick your feet up and chew the fat about anything Sierra Nevada related that doesn't quite fit in any of the other forums. Within reason, (and the HST rules and guidelines) this is also an anything goes forum. Tell stories, discuss wilderness issues, music, or whatever else the High Sierra stirs up in your mind.
User avatar

Attention spreadsheet geeks!

Postby Wandering Daisy » Thu Feb 27, 2014 9:33 pm

I have built a database for the Wind River Mountains, as part of eventually putting out a second edition of my guidebook. I thought spreadsheet geeks would be interested in what I did. Although building the database was tedious, now that it is done it is a snap to configure thousands of trips or reverse a trip to see if that works better. It took several weeks almost full time to put together the database.

First, I drew and labeled 950 logical connected route segments (most about 1-2 miles in length) using the TOPO software. I did the segments in geographical blocks. After drawing each segment I used the "build profile" feature to get miles, starting elevation, ending elevation, elevation gain and loss. Database columns are: segment number, start name, endpoint name, trail or x-c, difficulty rating (three trail difficulties A B C and three x-c ratings -G, PG, R) segment miles, starting elevation, ending elevation, elevation gain, elevation loss.

For travel in the opposite direction, I use a negative segment number and use formulas to adjust all data for traveling the opposite direction. I also have a column for estimated travel rate and two columns that I use to verify/check elevation gains/losses. A lot of my end point elevations are taken from a lake elevation or manually looking at the topo map. I found that what TOPO calculates on its profile is sometimes significantly wrong! So I have a formula that corrects the TOPO errors. I also find my own errors when doing the check. Sometimes I goof and put the elevation gain in the "loss" column.

The database is the first sheet. The other sheets are used for routes. Actually, I use the other sheets for book chapters with several routes on each sheet. These are set up to match the columns in the data base sheet and to match column widths that will copy into WORD that is set up in my book format. I just hide columns that I do not want to show up in the book.

So now when I want to devise a trip, all I have to do is look at my master TOPO map with segment codes and make a list of segment numbers (some positive and some negative depending on which way I want to travel). I am currently just manually linking the endpoint and then copy "as formula" the remainder of the columns of information. I also have columns where I calculate estimated travel time based on mph x miles + 1 hr/1000 feet gain.

I now have my entire route broken into small segments. I then decide base mostly on where I want to camp and total travel time, which segments I add up for each days travel. It is very easy to see if my desired day is too long and adjust it. Trip planning for the Rockies, where afternoon thunderstorms are the norm, is a bit more complicated because each pass or high altitude section has to be done in the morning hours. If any of you have my guidebook, this is how I make those travel plan tables.

If I want to see if reversing the rout makes more logistical sense, I use the sort function to reverse the segment numbers and multiply by -1 (this changes the direction). Then I manually re-link to the database. It only takes about 15 minutes to do this. Then it is just a matter of copying formulas.

You could eliminate the manual linking by using lookup table functions. I hesitate to do this because I have done this quite a bit in the past (at my real job) because lookup tables of this sort can be touchy.

I am not sure I will try to build a similar database for the Sierra. Maybe after I get the second edition of my guidebook out (a few years away) doing the same for the Sierra could be my winter's project.



User avatar
Wandering Daisy
Topix Junkie
 
Posts: 2607
Joined: Sun Jan 24, 2010 8:19 pm
Location: Fair Oaks CA (Sacramento area)
Experience: N/A

User avatar

Re: Attention spreadsheet geeks!

Postby Jimr » Fri Feb 28, 2014 9:36 am

I guess since it's for a guidebook to be published, we won't get to see it?
What?!
User avatar
Jimr
Topix Fanatic
 
Posts: 1122
Joined: Fri Jul 31, 2009 2:14 pm
Location: Redondo Beach
Experience: Level 4 Explorer

User avatar

Re: Attention spreadsheet geeks!

Postby Wandering Daisy » Fri Feb 28, 2014 10:00 am

I do not know how to show the spreadsheet on this forum. It is full of formulas and such and long. I am not sure how to turn a spreadsheet into a .jpg. I do not currently have the software to make a .pdf As for "giving" the database to others? I have not decided on that yet. Sometimes open "sharing" on the internet results in someone else taking your work and monetizing it.

When I did the first edition of my book, I just did everything per trip, and ended up having a lot of minor inconsistencies (like saying it was 1.1 miles in one route and 1.0 in another) because I re-measured with TOPO for each trip. Fixing all those little inconsistencies was a real pain. So this time I decided to make the database, primarily for internal consistency. More work up front; less editing work. Once I did this, I realized how useful it was. I now use it for planning my own trips. Taking a functioning spreadsheet and turning it into a finished product suitable for the public is beyond my meager computer skills. I do not plan on ever "selling" the spreadsheet. It will not be a part of my guidebook- it is just a tool for me. I just wanted to present the concept.

I used Excel because that is what I am most familiar with. You could do the same in any database software. Or for any area. I realize now that for someone who does a lot of trips in one area, building a database like this is not as difficult as I originally thought and it is more useful than I thought it would be.
User avatar
Wandering Daisy
Topix Junkie
 
Posts: 2607
Joined: Sun Jan 24, 2010 8:19 pm
Location: Fair Oaks CA (Sacramento area)
Experience: N/A

User avatar

Re: Attention spreadsheet geeks!

Postby Jimr » Fri Feb 28, 2014 1:18 pm

Is it in Excel? If so, what version? I'm pretty darn good at programming in VBA, but my employer is a bit cheap with the technology they give me, so I'm still working in Office 2000. If you were so inclined, I'd be willing to play around with a front end for it to make it easier to play with. Even if it were only for your personal use.
What?!
User avatar
Jimr
Topix Fanatic
 
Posts: 1122
Joined: Fri Jul 31, 2009 2:14 pm
Location: Redondo Beach
Experience: Level 4 Explorer

User avatar

Re: Attention spreadsheet geeks!

Postby SSSdave » Sun Mar 02, 2014 9:31 am

Looking forward to seeing what you created.

To turn each part of the spreadsheet you are looking at on your screen into a jpg, you can perform a series of screen captures. With Windows 7 one method is to use the snipping tool:

http://windows.microsoft.com/en-us/wind ... =windows-8

An good freeware screen capture program easily downloaded I've used for years is mwsnap.
User avatar
SSSdave
Topix Fanatic
 
Posts: 1965
Joined: Thu Nov 17, 2005 11:18 pm
Location: Silicon Valley
Experience: N/A

User avatar

Attention spreadsheet geeks!

Postby Jimr » Sun Mar 02, 2014 11:48 am

You can also install OpenOffice. It has PDF conversion tools integrated into it.


Sent from my iPhone using Tapatalk
What?!
User avatar
Jimr
Topix Fanatic
 
Posts: 1122
Joined: Fri Jul 31, 2009 2:14 pm
Location: Redondo Beach
Experience: Level 4 Explorer

User avatar

Re: Attention spreadsheet geeks!

Postby Wandering Daisy » Sun Mar 02, 2014 12:30 pm

I just printed out a small part of the spreadsheet and took photos with my camera. It will give you a bit of an idea of how it works.

The first two are the data base. I type in the text in black. The bold typeface is the original entry. If this is copied, a formula links to the original entry - so if I want to edit or change anything I only change the bold items. This creates consistency. The negative numbers are the opposite direction. You can use this as a lookup table, although I did not do this. The blue and red columns are elevation checks. I only allow a 25 foot discrepancy - if it is more I go back and revise either the start or end point elevation, or more often the elevation gain and loss that TOPO spits out.


Image

Image

This table below is a particular trip. All I do is type in the segment number and link the endpoint label to the database. Everything else is one copy/paste of a block of cells. Here is where if you were to set up lookup table functions you would not have to individually link anything.

Image

This is a pared down table that I import into WORD for the book.

Image
User avatar
Wandering Daisy
Topix Junkie
 
Posts: 2607
Joined: Sun Jan 24, 2010 8:19 pm
Location: Fair Oaks CA (Sacramento area)
Experience: N/A

User avatar

Re: Attention spreadsheet geeks!

Postby Wandering Daisy » Sun Mar 02, 2014 1:15 pm

Here is a part of the map that has all the segment numbers. The color of the lines means nothing - just different so I can clearly see where one ends and the other begins.

Image
User avatar
Wandering Daisy
Topix Junkie
 
Posts: 2607
Joined: Sun Jan 24, 2010 8:19 pm
Location: Fair Oaks CA (Sacramento area)
Experience: N/A

User avatar

Re: Attention spreadsheet geeks!

Postby ERIC » Wed Mar 05, 2014 8:45 am

The attachment feature of these forums allows you to post Excel docs.
New members, please consider giving us an intro!
Follow us on Twitter @HighSierraTopix. Use hashtags #SIERRAPHILE #GotSierra? #GotMountains?
Follow us on Facebook: https://www.facebook.com/HighSierraTopix
User avatar
ERIC
Your Humble Host & Forums Administrator
Your Humble Host & Forums Administrator
 
Posts: 2909
Joined: Fri Oct 28, 2005 9:13 am
Location: between the 916 and 661
Experience: Level 4 Explorer


Return to The Campfire



Who is online

Users browsing this forum: No registered users and 4 guests

cron