Excel Gear Spreadsheet

[David Lewis]

Whit, I also made ad excel sheet which you can download at:

http://www.davidlewis.ca/GearList.zip

To turn an item on (so it’s weight shows up in your totals)… place an x beside it in the first column.

Also, if you’re reading this thread Carol, I’d love to see your sheet and/or host it for you on my site (so you don’t have to keep checking this thread).

davelewis [at] eastlink.ca

[Mark]

Thanks for the spreadsheet, David. It gives me some ideas for re-tooling my own.
-Mark

[Jim Ells]

Carol, I’m interested in the spreadsheet.
You can send it to [email protected]
Thanks, Jim Ells

[David Lewis]

No problem Mark.

Just so you know (it’s mentioned above but it’s a long post)… the sheet is based on grams. You enter the grams and everything else keys off of that. If you wanted to make everything key off ounces, all you’d have to do is change the formula in the F column and change the conversions being done in the subtotal rows.

For example, the cells in col F all have this formula

=IF(A3=”x”,C3,0)

That means, if there is an X is A3, put the value of C3 in this cell, otherwise put a zero in this cell. In my sheet, the grams are in col C.

The subtotals are then arrived at by totalling the values populated in col F and have conversions applied to change the total grams to oz and pounds.

[Pamela Wiget]

Hi Carol,

Could you re-send that spreadsheet? I lost it in a hard drive crash last week.

Many thanks!!
Pamela
[email protected]

[EndoftheTrail]

Curious… has anyone who requested a spreadsheet here received one from Carol?

Just thought it would be good to know, as folks are continuing to post their email addresses here…

[David Lewis]

Hey All,

I don’t know if anyone tried out my simple speadsheet or not… but if you did… you should go download it again!

http://www.davidlewis.ca/GearList.zip

I just found a big flaw in the way I was getting my grand totals!

All of the weighs in the sheet are entered in grams… the smallest possible unit for the highest level of accuracy. To get columns showing ounces and pounds, I simply added conversion formulas. I then subtotaled each of those 3 columns (gm, oz, lb) for each grouping of equipment (sleep, cooking, clothing, etc.). The problem was… to get the subtotals, I simply did a sum of each column. But of course, the oz and lbs. for each item are all subject to rounding, so those subtotals are all a little off. This was compounded even further since I then totalled all of the subtotals to get my total base weight.

The end result was that, due to adding up all of these rounding figures instead of adding up the total grams and then converting that… my total base weight in pounds was off by 0.6 pounds! The best part is, it was 0.6 pounds too heavy. The total in grams of course was accurate… but I didn’t convert the grand total of grams to pounds as a double check. I jsut did that and that’s how I discovered the error. So I just went from 7.3 lbs. to 6.7 lbs.! I just changed all of the totals to all be based on the original weights entered in grams rather than the converted and rounded values in oz and pounds. This was a very happy accident! I just lightened my load by over half a pound without removing any gear from my list… LOL :)

[Janet L Besanceney]

Legit and very helpful, no spamming or any silly thing like that…..just my experience for what it is worth…..
and very well done spreadsheet!!

[Mark]

Agreed, well done. Thanks again, David.

About the rounding: I think you were able to ‘lose weight,’ because grams are expressed in whole numbers; whereas, your oz and lbs were probably decimals, and were rounded off.

I believe rounding off numbers is an MS Excel default setting. To avoid it you can just use the Format->Cells command [or keyboard command: press the “control” key, then the number “1”]. Use the “General” setting, and all the precision will be preserved. This is the easiest way to ‘lose weight’ if you’re not using grams.

Also, I thought I’d share a modification. I added some specific-item formulas to my spreadsheet that let me calculate the weight of those items that will vary in number or volume from trip to trip [e.g. tent stakes, TP allotment, alcohol fuel, food, etc.]. In practice, instead of marking a field with an ‘x,’ meaning ‘yes, I will bring this item,’ I mark it with a specific number, meaning ‘I will bring *8* stakes’ or ‘I will bring *4* days of TP’ or ‘I will bring *3*oz of alcohol.’

So the formula for the particular item’s cell ends up being a basic [8 * .226oz] or [4 * .2oz] or [3 * .82oz], and so on, rather than the “IF” formula. By indicating the quantity of an item that you might bring, instead of an x [“yes/no”], you can be a bit more precise, and fine tune for each trip without having to generate a new sheet or do too much re-weighing or re-calculating. Just another idea.

I hope this post is at least tending towards clarity; let me know if I’ve generated any confusion.
-Mark

[David Lewis]

That’s a GREAT modification Mark! Thanks :)

I never thought about using general formatting. That is the default… but the thing is… my converstion factor for grams to oz goes to like 10 decimal places or something ridiculous like that… so with general formatting, I ended up with oz. that looked like 5.2039182745. I used number formatting to keep the ounces and pounds to 1 or 2 decimal places. But as a result… I ended up with all this rounding. I like using the huge conversion number… again… for accuracy (ironically enough). So will keep using number formatting but just make all my totals and subtotals based on the original weights in grams rather than the larger converted units.

Anyway, I can’t tell you how cool it was when I decided to put the totals in grams and ounces as well (originally I had the totals in pounds only) and then I plugged the total grams into a conversion utility and it said 6.7 pounds. I’d suddenly “lost” half a pound… LOL :)

Of course, the other rounding that happens when something is broken down this far (down to individual stakes) is rounding from your scale itself. My postal scale’s smallest increment is 1 gram. So I will often noticed that if I weigh, for instance, 1 stake, it may be 10 grams let’s say. But then I will weight 8 stakes and then will be 84 grams. So what’s happening there is all those fractions of grams that don’t show up when you are measuring such a tiny piece do show up when you measure a larger group.

So ultimately, these spreedsheets are not ever going to be 100% accurate. They are a great tool… but to really get your true final weight of course, you have to weight the actual pack. But I think the way I have the sheet set up now, it should be as accurate a prediction as possible for the types of detailed lists that we all make.

Still… I can’t believe I just “lost” a half pound due to rounding. Hillarious. A very ‘happy accident’ :)

[David Lewis]

Hey Mark,

I took your idea and revised my formula for populating cells with the weights of items. Before, it just looked for an ‘x’ and put in the grams if there was an x, otherwise it put in a zero. But I love your idea of being able to have multiples of one item (like stakes for instance). So here is the new formula I’m using:

=IF(A9=”x”,D9,IF(A9>0,(A9*D9),0))

So with this… you can either put an x or a zero or a number from 0-9 or nothing at all… and the calculation will work. If you mark an x, that’s the same as marking a 1. If you leave the cell blank, that’s the same as marking it with a zero. And if you mark a 2 if will take the gram weight of that item and multiple it by 2.

[paul johnson]

haven’t specifically looked at your spreadsheet, so not sure of precisely what formulas you have embedded in each cell.

however, regardless of displayed formatting (i.e. number of displayed decimal places), XL uses an internal double-precision floating point format (~13 or so decimal places; NOT strictly the IEEE standard dbl-precision floating pt format) for all calculations.

so, unless other parts of a cell formula “mangles” (not necssarily a “bad” thing – might be what is intended by the author of the formula, e.g. conversion to text having fewer decimal places & then conversion back to a true number) the value in some fashion, any calculations will NOT be affected by displayed significance/precision. Internally stored significance/precision will still be used in calculations.

taking what is one of the most trivial examples of worksheet formulas:
enter the value 1.14 in cell A1
enter the value 1.13 in cell A2
enter the formula =sum(A1:A2) in cell A3

the result 2.27 will be displayed.
changing the number of displayed decimal places in cells A1 & A2 to only a single decimal place (now each cell shows 1.1) does NOT affect the value displayed in cell A3 – it still shows 2.27. now change the number of displayed decimal places for cell A3 to display only one decimal place. this causes cell A3 to CORRECTLY display 2.3, NOT 2.2 as might be thought be adding just the VISIBLY DISPLAYED values. XL is using its stored internal representation (which retains far more precision than that being displayed) of the displayed values.

[David Lewis]

Yup. The problem was, I was taking a number of values which only went to once decimal place and subtotalling those numbers and then totalling those subtotals… so the internal precision was lost.

[paul johnson]

another frequent Poster to these Forums (G.R.) uses a similar method for “activating” line items for summation.

instead of a ‘x’, he changes the FONT for the column of cells containing the “activation” indicator character to the Wingdings (or Webdings – i forget which one) character set. Then typing the letter ‘a’ (w/o the sng-quotes) on the keyboard causes a literal “check mark” to be displayed in the that column instead of an ‘a’. make sure the formulas “look”/test for an ‘a’.

pretty neat. good idea G.R.

[David Lewis]

I wrote:

“Yup. The problem was, I was taking a number of values which only went to once decimal place and subtotalling those numbers and then totalling those subtotals… so the internal precision was lost.”

Actually Paul… you were right. What happened was simply that two rows were ommited from the sum totals and I didn’t notice it. The half pound I thought I lost from “rounding” was actually just from my MEC Fleece not being taken into account. DOH!!! :) Makes sense. It would be a pretty useless program for accounting if it accumulted errors in precision as a result of rounding. Opps.

[paul johnson]

both simple iterative & more complex type of rounding errors can occur, but these occur out at the 13th decimal place or so & may, depending upon the actual situation (i.e. the formulas involved), propagate upward. however, the situations where this effect is actually a factor are exceedingly rare. 4 to 6 dec. places is generally sufficient for most (not all) “gov’t work”.

[paul johnson]

seen this b/f, but a first for me.

inadvertent double-post.

to BPL web meister:
in my case, best as i can tell, seemed to be caused by “refreshing” the browser window.

[Mark]

Thanks for the clarification, Paul. I discovered that part of the reason I wasn’t seeing the precision was because my cell sizes weren’t large enough to display all the digits. [sheepish grin]
-Mark

[David Lewis]

I don’t know if anyone is using my simple little spreadsheet… but if you are… it had a number of small errors. I’m confident that it’s all good now. If you are using it, you might want to download it again (with the fixes)

http://www.davidlewis.ca/GearList.zip

If you use it… only enter weights in grams! All of the other weights and totals etc. are calculated with formulas based on what you enter in grams.

[paul johnson]

need some feedback from y’all.

kickin’ around an idea. more specific details WILL be made known should this plan develop.

would anyone be willing to voluntarily contribute somewhere b/t $1 and let’s say $5 (your choice at time of download – this statement and the amt. are tentative – other suggestions welcomed) to download a somewhat more sophisticated XL Gearlist? it is highly customizable & flexible. allows the playing of “what-if” games using customized gear lists of gear you own or might like to purchase. displays various totals, viz. base pack wt., total ‘skin-out’ wt, wt of clothing worn/gear carried, “true” pack-wt, pack wt. w/o water. displays sub-totals for various “categories” (packing, shelter, etc.).

fuel consumption can be optionally “tied” to the # of days of the hike, or selected separately if you want to take/carry more of less fuel.

selection lists come pre-populated with many of the most popular L/UL gear items in each category, but if you can type, you can easily change them. lists can also be added to, or shortened to include only your gear.

if you can use Windows to launch your browser and then use your browser to get to this web page and read this post, you already know enough to use and modify this spreadsheet – it’s not complicated at all. in fact, it’s quite intuitive even without instructions and a support Thread.

once a list is set up with gear, the entire list is always available as a single line item – very easy to pick which one you want.

[note: “what-if” games = it’s very easy to change, for example, what shelter, from a list of shelters, & see immediately how various wts & sub-totals are affected.

do you think a significant number of people might download the gearlist?

a Thread could be set-up to answer any questions downloaders might have about customizing the XL workbook, changing formulas, explanations of how to make another one for yourself, etc.

100% of the contributed amount would go to help a young family who lost their husband/father. this family is part of the UL backpacking community and may be known to you.

i would NOT be involved in the collection or distribution of any funds. this would be handled, hopefully, by those far more capable & also known to y’all.

now, this whole plan might NOT come to fruition – at this point i don’t know for sure. in which case, the XL gearlist would be made available for all to download, share, etc. at no charge whatsoever.

i’d like to get enough info fr/y’all so that within a couple weeks or so, this might become a reality if there is enough interest on your part.

what do y’all think? what are the pros & cons as you see them? i guess i pretty much have the blinders on in this case & am having trouble seeing any downsides, so i need your help? i just don’t have any conception of how much interest there might be in this. i’d very much appreciate your feedback on this matter. constructive criticism is welcomed. anonymous replies are welcome also. if you think that this is the dumbest idea since invisible ink erasers (ok…maybe not a good example), please speak up.

many thanks,
pj

[Bill Fornshell]

Hi Paul, I would be happy to comply if you could make it so it would work on my Mac without Excel.

My new laptop has the current version of something called AppleWorks that has a SpreadSheet but I have never used it.

Thank you. Bill

[David Lewis]

I wouldn’t count on AppleWorks being able to import an Excel file and maintain everything. I’m a Mac users, but I don’t have AppleWorks or I would test it for you.

[paul johnson]

Bill,

thanks for your reply & input.

unfortunately, i don’t have a Mac. Maybe someone else has both a Windows based PC & a Mac and can do the conversion & offer it back to you, or as a download choice?

if someone is willing to do this, post a response please.

[Carol Corbridge]

Gosh, lots of interest while I was away. Carol here. I’m just back from 7 days in the Trinity Alps. WOW! Great country.

Anyway, I’m not just collecting email addresses for spamming purposes. And if anyone besides Ben (somehow I missed him and have sent him a copy now) has not received a spreadsheet, please let me know. It’s done in ounces and works great. I’ve used it for about 7 trips this year. I’m lucky enough to be retired and spend as much time as I want in the wilderness. Every trip I use this spreadsheet to track my gear and then I save it with a unique name for future reference.

I’m happy to share it for free with anyone who wants it. I enjoyed building it and am happy others can find a use for it.

Hope this clears up any concerns about my intentions and encourages people to request it from me if they want to try it.

David let me know what you think.

Ben, if you could let me know you got it, that would be nice.

Thanks to all for your interest and happy trails.

Carol

[Anonymous]

Carol,

Welcome back and thanks for sending me a copy of your excel gear spreadsheet.
It worked flawlessly on the first try. Intuitive and simple to use, without all the bells and whistles requiring additional downloads and fixes.

Hike your own hike.
Roger

[Author]

Posts