Hey All,

I hate to be yet another person asking for this... so I made my own and posted it on my website so anyone can download it if they want...

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

I don't know how it compares to Carol's spreadsheet... but it works well enough. I also don't mean to step on any toes... just figured it was easier to make my own.

The main thing I wanted was to be able to turn items on and off and have the totals all update.

I used checkboxes at first to do this... but they are a huge pain since they do not occupy a cell... they float above everything. So if you want to add new rows, delete rows, move rows, sort rows... etc... the data all moves around the page... but the checkboxes stay put... making it very confusing. Plus you have to add checkboxes one at a time and associate them with a new cell... etc. They are just really cumbersome.

So... instead... I just used a column with a character in it. I used the letter x to indicate that an item is "checked" or "turned on". To turn an item off... just delete the x or type any other characer BUT x. The formula that looks for the x and makes this work is in column F. I made the text white in that column so it wouldn't show up when values are being populated (either 0 or the weight in grams).

Also... all of the weights are based on grams and everything in the spreadsheet (formulas, etc.) is keyed off what you enter in the grams column. I did this since my scale can measure in 1 gram increments and a gram is a smaller unit than 0.1 oz... so I figured this would be the most accurate measure... esp. for tiny items. It would be easy enough to change the formulas tho' to key off ounces instead.

And before anyone says so... yes... I know... my sleeping bag is INSANELY heavy. That's my one last big lightweight purchase. I'm debating between an Arc Alpinist (worried about drafts) or the GG Sleeplight.

Edited by davidlewis on 07/22/2005 16:24:05 MDT.

David,

If you are considering the Nunatak Arc Alpinist or the Gosamer Gear Sleeplight sleeping bags consider the Feathered Friends (FF) Vireo, optional Volant Hood, and FF jacket.

Rather than go through these discussions again review the discussions here on BPL in the down quilt thread at:

http://www.backpackinglight.com/cgi-bin/backpackinglight/forums/thread_display.html?mv_session_id=IHUnAGDu&mv_pc=99&forum_thread_id=576

and the multiple use sleeping bag thread at:

http://www.backpackinglight.com/cgi-bin/backpackinglight/forums/thread_display.html?mv_session_id=Gioht95L&mv_pc=91&forum_thread_id=542

Rich

Edited by naturephoto1 on 07/22/2005 17:23:59 MDT.

Thanks for the thread pointers Richard.

Did you check out the spreadsheet? It's nothing revolutionary or anything... but I do like the ability to turn line items on and off. I thank Google for helping me figure out that one :) I'm not an excel person by any means!

David,

I have not had a chance to check your spread sheet. I have borrowed the one from Mark Verber at:

http://www.verber.com/mark/outdoors/gear/backpack.html

As Mark points out on his site it is best imported in Excel. Once in Excel you can make all your changes to his list.

Rich

Carol-
A copy of your spreadsheet would be a great asset! If it would still be possible, an e-mail to kincaids5@msn.com would be most welcome. Thanks!

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

Edited by davidlewis on 07/22/2005 22:29:45 MDT.

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

Carol, I'm interested in the spreadsheet.
You can send it to ellsfamily@yahoo.com
Thanks, Jim Ells

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.

Hi Carol,

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

Many thanks!!
Pamela
pamela3@joimail.com

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...

Edited by ben2world on 07/24/2005 15:29:55 MDT.

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 :)

Edited by davidlewis on 07/26/2005 21:49:55 MDT.

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!!

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

Edited by mlarson on 07/26/2005 22:38:50 MDT.

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' :)

Edited by davidlewis on 07/27/2005 04:57:23 MDT.

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.

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.

Edited by pj on 07/27/2005 10:37:45 MDT.

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.

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.

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.