Home

Feedback forum

Get a Handle on Pack Weight

A Gear List Super Model

Download Here*

By Jim Wood

Published December 4, 2005

Last update: December 4, 2005

Submit or read comments about this model here.

 

* This free download is a backpacking gear list spreadsheet template that includes enhanced reporting capabilities. It will run either under Microsoft Excel or the "Calc" module of Open Office (it may also work, but has not been tested, with other spreadsheet products). To use this utility, you'll need to have one of these two programs installed on your computer and will also need an understanding of basic spreadsheet operations. Instructions and usage notes for the model are included on the first worksheet tab. This model uses no macros and is free of viruses or other malware. Download size = approx 600K. 


Top

Home

If postings on Internet outdoor forums are any indication, a great many backpackers are now using computer-based models to help manage pack weight. The practice has undoubtedly become more popular in recent years with the upsurge of interest in lightweight backpacking, since in order to systematically reduce pack weight, one must first understand it. In the old heavyweight days, most backpackers had little clue about gear weight, which is probably the main reason that so many shouldered (as some still do) bone-crushing loads in the backcountry.

The substantial benefits of lightening up have motivated many hikers to examine—some down to the last gram—the mass of everything that's carried on the trail. Because they're easy to create, update, re-configure, and backup, computer-based models can serve as effective tools in the continuing struggle to trim pack pounds.

Some backpackers currently use mini-software applications such as Chris Ibbeson's handy "Backpacking Gear Weight Calculator" to maintain gear lists. The key advantage of such utilities is that they're easy to learn, especially for casual computer users. The principal drawback, however, is that they're also relatively inflexible. 

A more powerful approach is to use an electronic spreadsheet, such as Microsoft Excel or the "Calc" module of the free Open Office suite, to build a custom gear weight model. But to do so, users must first learn the basics of spreadsheet operations. If one is willing to make this investment, however, a tool can be developed that more precisely meets his or her needs.

The model offered here (see link above) is an easily modifiable spreadsheet template that can serve as a starting point for this process. Unlike similar templates, it also includes an important reporting feature that can significantly enhance its value over more commonly used spreadsheet tools.

Enhanced Reporting Capabilities

According to experts, maintaining lists is one of the most common uses for electronic spreadsheets. I've been working with these tools since their earliest days and have observed that when most people create spreadsheet lists (often in the form of simple flat file databases) they usually stop there. When they want to see different views of these lists, they normally just re-sort the tables and/or create subtotals for groups of items.

There are, however, at least two problems with this approach. The first is that re-sorting and subtotaling offer only limited ways to examine alternative views of their lists. The second, and probably more important, is that this approach requires the user to operate on live data—a process that can sometimes lead to the accidental deletion or corruption of records. To avoid this hazard, some people will first make a copy of a data table before manipulating it, but in so doing, they create a second database that must now be updated when changes to any record are made.

The Gear List Super Model overcomes these problems by using a separate reporting function that is completely independent from the underlying data. Accordingly, the template behaves more like a real database application than a traditional spreadsheet model.

With this tool, it's possible to create and save an unlimited number of reports that all draw their data from the same Master List of gear items. Since this reporting mechanism is read-only, it ensures that the live data remains safe and intact. Because it's also extremely flexible, it allows data to easily be viewed in ways that extend far beyond simple group subtotaling. It's even possible to "drill down" through these reports to see supporting detail.

This capability does not require an external software application, but instead, is a part of the core functionality of both Microsoft Excel (the "Pivot Table" feature) and Open Office (where it's called the "Data Pilot"). For convenience, since the Microsoft product is the more widely-used, I'll describe the model's operation primarily under Excel.

Easy But Powerful

Though it's one of the most powerful features built into Excel, Pivot Tables are also one of the most under-used. If you're interested in trying this model, it would be helpful to learn a little about Pivot Tables (a good place to start is the Excel Help facility), but if you'd prefer not to invest the time initially, you can still use the sample Pivot Table reports that are included with the template. Just be sure to read the note at the top of each report about how to "refresh" the tables when changes are made to the Master List.

To adapt this template for your own use, simply replace the sample data in the Master List and backup schedules with your own, then update the Pivot Table reports. The sample data is all real and represents a subset of my own gear collection, but its primary purpose is to demonstrate how the model works. 

With this template, you'll be able to maintain a permanent record of every item in your equipment inventory and then for any given trip or test configuration, you can just tag each entry as either "in" or "out" of the current mix. 

After refreshing the Pivot Tables, you'll be able to see updated pack weight totals that are grouped by gear classes. In addition, the model will calculate totals for base pack weight, consumables weight, and the weight of clothing worn or of other gear that is carried separately. Note that you can define these categories (or add new ones) any way you see fit.

Instructions and Usage Notes

  • The first worksheet tab in the model includes instructions and usage notes that you may find helpful. As mentioned above, you should have at least a basic working understanding of spreadsheet operations including how to insert and delete rows, edit cell contents and create totals for columns of numbers. If you're just starting out with spreadsheets, perhaps this model will provide you with an incentive to learn more.

  • Though the model was built using Microsoft Excel (tested with Excel 2000 and Excel 2002/XP), it will also run under Open Office, which available as a free download at OpenOffice.org. The newest version (V 2.0) was released in late October, 2005 and provides better support for Excel Pivot Tables than earlier versions, so I'd suggest using the latest release for this model. Even so, some formatting will be lost under Open Office, but the base functionality seems to work OK, at least with the limited testing I've conducted.

  • This model supports both English (ounces and pounds) and metric (grams and kilograms) units, though some adjustment to the embedded formulas will be necessary if you want to actually input metric weights. See the instructions page for more information.

  • If you're already handy with spreadsheets, I'm sure you'll quickly see ways to enhance the model (I mention one possibility—a multi-configuration version—on the instructions page). Note, however, that I've intentionally tried to make the model as straightforward as possible in order to maximize ease-of-use. 

  • If you currently maintain a spreadsheet-based gear list, this template might be helpful in demonstrating how to incorporate Pivot Table reporting into your existing model.

Technical Support and Model Derivatives

Unfortunately, as noted on the model's instructions page, I have little bandwidth to provide technical support, so you're largely on your own. In most cases, general questions can probably be answered through the Excel Help files, the Microsoft knowledge base or through online Excel forums. If you really get stuck on a model-specific issue, however, please contact me and I'll try to assist. 

In any case, I would appreciate your feedback (see below) and suggestions for improvement. The model is being released into the public domain without restriction, so feel free to hack it up as you wish. If you find it useful and happen to develop an interesting derivative, please let me know and I'll be happy to post your version on this site for others to download. If you submit a new version, just make sure that it's virus-free (the use of macros is discouraged) and doesn't infringe on the copyrighted works of others.


 

 


You can submit or read comments about this model here.

Copyright © 2005 James E. Wood. All Rights Reserved.