The power of speadsheets

My day job requires me to make heavy use of Excel and database files. My gear list can get out of hand since I have a lot of time to think about it and less time to use it.

I have all my gear listed in columns of:
Basic Category – Base Weight or Consumable
System – Packing, Shelter, Cooking/Hydration …..
Item Name – "name of item"
OZ – oz per unit
Quantity – how many (more useful on consumables)
Total OZ – Quantity x OZ

Then I have a column called "Select" where I place a 1 or 0. If I need it for a trip it gets a 1.

On a new tab I have a pivot table which neatly arranges everything by Basic Category/System/Name total OZ per item and grand total per system. The pivot is filtered to only show stuff I ticked with a 1 on the gear list.

It may sound complex but my Excel skills are crazy. It takes me minutes to build something like this (sans typing in the values).

Nice thing is that I can go down the list and mark 1, refresh the pivot table and have a ready to go list.

Here is how on eof the pivots look:

> On a new tab I have a pivot table which neatly arranges everything by Basic Category/System/Name total OZ per item and grand total per system. The pivot is filtered to only show stuff I ticked with a 1 on the gear list.

That's a sweet trick! Thanks for the idea.

acronym 8/9/2010 5:53 PM

Man, this is so powerful. Thank you so much for sharing. I'm using the OpenOffice Calc DataPilot (analog to pivot), and it's working great. What a sweet tool!

>Then I have a column called "Select" where I place a 1 or 0. If I need it for a trip it gets a 1.

I want to be able to handle quantities (2 bandannas, 2 underwear, 3 granola bars, etc.). Here's the tweak to your setup:

I changed your "Select" column to be the "Quantity" column, and then select where quantity >= 1. I then created a new column of "Quantity * Kg" to calculate the new weight with the entered quantity. Of course the pivot or pilot then includes the "Quantity * Kg" column (in addition to or in lieu of the weight column).

I knew keeping all this stuff in a spreadsheet format was the way to go!

Thanks again!

acronym 8/10/2010 2:56 PM

I have to try that "select" feature! Lots easier than moving items in and out!

For consumables, I use two columns. I have the weight per day and then a formula based on a fixed cell. That cell has the number of days for the trip. It works very well for me!

Instead of 'select' I do something similar and call it 'quantity' so I can carry 0, 1 or as many as I like. This work well for stuff that might carry a bunch of (ie. water bottles, batteries etc). It doesn't hide the stuff I'm not taking though, so I do have another page with 'other gear' listed that I manual add and remove but my list doesn't fluctuate much because I usually sell gear I don't use regularly. I can see how if you had a huge gear closet and you carry a highly different gear list everytime then making the gear disappear that you aren't taking would be very valuable.

Here's how I have it setup:

Can anyone give a tutorial on how to set this up in Appleworks? I've never done a spreadsheet in my life. Still have my gearlist in a binder.

Would you be willing to share this great spreadsheet page? I use Excel on a MAC.

I'm not sure who's you're asking for, but here is mine in case:

http://www.mediafire.com/?is8v6m34x3z6cym

I think that will work with both MAC and PC, but if you want it and that doesn't work then let me know and I'll save it differently.

Dan wrote, "It doesn't hide the stuff I'm not taking though, so I do have another page with 'other gear' listed that I manual add and remove but my list doesn't fluctuate much because I usually sell gear I don't use regularly. I can see how if you had a huge gear closet and you carry a highly different gear list everytime then making the gear disappear that you aren't taking would be very valuable."

I have a "master" spreadsheet that lists all my gear. Here's what I do before packing:

1. Add a new worksheet ("tab") and copy the master list to it. This new worksheet will serve as "working copy".

2. Using the working copy, indicate quantity of each item to bring — and delete out all the rows of items to be left at home. Worksheet computes weights and all.

3. Save the file. The file now contains the pristine master list, the working copy of the current trip — plus individual worksheets ("tabs") of all past trips — a great gear record of what you had and used.

4. Print the current working copy and use as packing list.

Starting from a master list of all gear items and using it as a packing list will minimize the chance of inadvertently leaving something at home.

Gear Grams

Not a spreadsheet…but a neat little online tool for tracking kit and making up a gear list for a trip from a gear closet type concept. I have been using it for a bit and definitely find very handy.

(no affiliation with the website..just a user)

I do the same exact thing Ben does – with one slight modification. If I'm going on a trip very similar to a prior trip, I just copy that tab over to a new one and modify it from there.

Has anyone tried the spread sheet from backpacking.net by Chris Ibbeson. It is really easy to use and also has a tutorial. Here is the link.http://www.backpacking.net/featured3.html I've been using it for three years and I am really not very good on a computer.

Dan. That link does not work on my mac. I try to open it and it just makes a copy of itself on my desktop. I'm running OSX 10.411

"Has anyone tried the spread sheet from backpacking.net by Chris Ibbeson. It is really easy to use and also has a tutorial. Here is the link.http://www.backpacking.net/featured3.html I've been using it for three years and I am really not very good on a computer."

I have been using a version for years, very easy to use for equipment, not so much for consumables but I like it.

> Instead of 'select' I do something similar and call it 'quantity' so I can carry 0, 1 or as many as I like… It doesn't hide the stuff I'm not taking though

Sorry I mis-spoke. I just learned how to use the pivot table a couple hours before writing my post.

Don't select on quantity. Filter on quantity greater than or equal to (>=) 1.

If you add a filter, Mr. Durston, you won't have to make a middle-man sheet.

acronym 8/12/2010 11:32 PM

Just wanted to share for those interested.

Attempted to recreate the pivot table gear list setup that was mentioned above. I haven’t been able to figure out setting it up so you can enter the quantity and have that reflected in the total weight. But consider this a work in progress.

-Adam

click here

yeah, Im having a problem understanding the same. I've used excel lots, but this format is new to me. Perhaps a copy of the original page of data, showing how the set up is done would clarify? could we get that? then we can go and do our own with the formulas a bit more transparent to copy.
Thanks!

I've got the source data on the 'gear list' tab and the pivot table is on the next tab. You can click on the Pivot table in the 2nd tab and it should show you how the data is arranged.

You should be able to edit things in the gear list tab, click over to the pivot table, right click in it, click refresh and the new information will show.

There are some good basic tutorials about pivot tables online that will help you to get your feet wet in this area.

Adam,
I tried to access the data, but says its not available?

Ken,

Gosh, I haven't used AppleWorks in decades. For its time, it was a pretty cool program. I Also used the 3 Easy Pieces version on an Apple Lisa.

It is a pretty basic spreadsheet program. You just have to build a couple basic spreadsheets to understand how it works, and learn how to build formulas. It does not have a lot of features like newer applications. But it will work.

Here is what I use. I build it a couple years ago, and haven't gotten around to sophisticating it. It really is a rough draft. Eventually I will add a grams converter and would like to play around with nutritional values. If anyone wants a copy, PM me. However, I probably can't send it until next week, as tomorrow I have to get ready for a trip and will be gone until Sunday. It will only work on MS Excel for Windows (2000 and higher), because it is dependent upon VBA code.

What I wanted to do is use a spreadsheet that would be easy to do "What if." As I enter items, the main category weights are calculated at the top. Also all my gear is listed on one sheet, and I can filter it so it only shows the gear I have chosen for a particular trip. It hides my entire gear list, but when the Unfilter button is clicked, all items on the list are visible.

There are drop down lists for items. The picture below is a scree shot of what it looks like after I have filtered it, by just clicking the filter button (hides all gear items not in the current kit). It can also sort the list, un-filter it show all my gear, etc. Here are some descriptions of items in the maroon row.

Status: Choose "In" if I am taking it.

Category: Choices are Base, Consumable or Wear.

Class: Cannot change. Used for sorting. I should probably hide it it.

Class Name: Can add anything you want and it will sub-sort it. Currently using: Pack, Sleeping, Clothes, Kitchen, Utility, Kitchen and Office.

Description: Self evident.

QTY: How many you have.

Lbs: Enter Lbs. Not needed as you can instead enter ounces (i.e. 32 ounces and leave the lbs empty).

The last 3 columns are used for calculations and should probably be hidden.

Buttons:

– Sort: Sort by Category, then Class, then description of item (easier to locate an item).

– Filter: Hides all items that are not chosen for a particular trip.

– Unfilter: Shows all items in your gear closet.

– Print Filtered: Prints you kit for your trip.

– Print Unfiltered: Prints everything in your gear closet.

The gear list below if the final Kit for a trip. Notice that the categories are at the top for quick and easy review. Shows Big 3, Base, Consumables, Worn, and FSO.

Daniel has shown me why I should have taken pivot tables more seriously. I will learn and then remodel my spreadsheet.

CLO values are new to me so I looked them up. A naked person 0 and a typical business suit 1. Down jackets rate 0.55 on the table I consulted. That's all down jackets, apparently, so if you want your spreadsheet popping out useful insulation information, Dale, you may be faced with a lot of experimentation to establish informative values.

John, there's actually been quite a bit of research on the BPL forums about clo value for various garments. In particular Richard Niesly has done some phenomenal work in an effort to enlighten the community on insulation and has measure the clo/r values for many garments. I suggest doing some forum searches if you really want to get deep into the nitty gritty.

He's also got some useful information on appropriate clo values given temperature and activity levels given a "standard" human that can be adapted, with some experimentation, to each individuals comfort levels.