BPL Gear List Spreadsheet CONTEST

[Phil Barton]

Hmmm. My thought is that I don’t need to be constrained by any particular unit. Maybe you can enter the weight of a piece of gear in whatever units you like – oz., g, or lb. It is a spreadsheet after all. Then the weight can be displayed in whatever units you like. Here’s an example of where I enter a weight in cell A1, in ounces. This formula can display the weight in pounds and ounces. This isn’t a really clean formula but it works.
=IF(ISNUMBER(A1),FIXED(FLOOR(A1/16,1),0)&” lb “&FIXED(((A1/16-FLOOR(A1/16,1))*16),1)&” oz”,””)

There’s also a nifty function called CONVERT that can do the g <-> oz. conversion for you. The CONVERT function is part of the Analysis ToolPak, a free component for Excel for both Mac OS X and Windows.

Seems like it’s time to be creative.

[Phil Barton]

Anon is right. You can download OpenOffice for Mac OS X. But my gear spreadsheet works fine with Excel on either Mac or Windoze.

[David Lewis]

Thanks Phil :) I’m new to Excel formula writing… so that’s cool. The convert function sounds interesting. I’m also writing out subtotals in pounds and ounces… but I did it in a different (and not much cleaner) way than the formula you posted.

As for what units to use, my question to the group was not really “what units should I use” but rather, “what is YOUR preference”. It seems the answer is that there may be as many preferences as there are users. Knowing that (thanks to reader input earlier in the thread), I’ve decided to build my spreadsheet with a selection of units and formats. Users can simply delete rows with units or formats they don’t care for or don’t find useful. For instance, in my personal sheet I don’t show any totals in kilos. or pounds and ounces… I choose to use just ounces and pounds with decimals.

[Miguel Arboleda]

I, too, was a little dismayed when the spreadsheet contest focused, as usual, on Windows. Too often Microsoft products have played havoc with my Mac (it took me three weeks to figure out how to completely remove all Windows Media Player files from my system), especially because Microsoft so often refuses to input safeguards into the Mac versions of their software or even to finish the product! I simply refuse to use Windows software on my Mac machines.

Here is a Mac alternative spreadsheet application. It is mature, from a company that has gained a solid reputation in the Mac world. It can import Excel files:

Mariner Calc:
http://www.marinersoftware.com/sitepage.php?page=14

Hope that is a good solution.

[Anonymous]

OpenOffice 2.0 isn’t working on my mac running OS 10.3. I will try version 1.1.2 tomorrow.

[Bill Fornshell]

Miguel, I looked at that web site and think I will get their spreadsheet software. they have a couple other software programs I might also be interested in. Thanks a lot for that information.

[David Lewis]

I don’t think the contest is Windows focused. Excel is available for Mac and OpenOffice is free and runs on just any platform. That said, I too am a long time Mac user and I fully understand your not wanting to use MS products.

I’m going to download OpenOffice now and see if my spreadsheet works on it.

[brian stein]

Bill accessing spreadsheets shouldnt be a problem. Openoffice is available for mac OS X and version 2.0 works fine for me on 10.4.3.

Agree with the units: grams is the way to go.

[David Lewis]

Quick question

How particular are you all about accuracy? By accuracy… I’m talking maybe 0.2 oz out of say a 20 pound total skin out weight. That’s silly to worry about… right? right? LOL :)

Reason I ask is… without going into all the gory details of how my formulas are built and cells formatted etc… I basically have a choice between nicely displayed item weights but ever so slightly inaccurate grand totals or 100% accurate grand totals with slightly less attractive looking item weights. This issue is partly related to do with what appears to be a bug in the Mac version of Excel… which I’m working to support since I’m a Mac guy :)

I’m thinking it’s kind of a non-issue and I will just make a decision either way… but I’d be interested in what others think about rounding “errors”.

[Benjamin Crouch]

You know….there is a very good freeware database on flatfoot’s backpacking site that works very well and it converts from Lb to metric at the click of a button….which you could also do in a spreadsheet with macros and radio buttons etc. Just FYI. Cheers and good luck!

[David Lewis]

Thanks for the info re: flatfoot. It’s PC only… but I fired up Virtual PC to give it a look see and it’s very nice. You could probably do something similar in Excel by using a List Manager and some form elements… but it still would not match the functionality of a custom application.

[David Reid]

This is just the thing I have been looking for. I’m not much of a computer programmer and my spreadsheet, well, isn’t very good. I have downloaded a couple from the net but most seem to have errors in the program.

I have seen some really good suggestions which I hope make it to the final product.

[David Olsen]

You can creat a spreadsheet on a Mac in Appleworks or Excel and save as an older version
of excel and it should work on either machine.

It would be simple to add weights in all measurements with additional columns.
Kilos, ounces, grains etc.

[kevin davidson]

I guess I can live with an error of 5.6690 grams over 9.07184 kilograms.

[Bob Gabbart]

It would be easy to make it so you enter the weight in say grams and then the other column displays ounces, but is there a way that you can enter the value into either column and the other displays the appropriate value? Is that clear?

[paul johnson]

Bob,

off the top of my head, w/o trying this, my guess is that you’ll probably get a “circular” reference error. at least one cell/column will need actual “values” vs. formulas. the other cells/columns can then apply formulas to the values. furthermore, if i understand your question correctly, if you type a value (the weight of a piece of gear in this case) into a cell, it will overwrite the formula the was prev. in that cell.

[David Lewis]

Hey Bob…

Paul explained this very well… but here is another way of thinking about it… Essentially, what you’re talking about would require a cell to have a split personality… being sometimes an input (weight of the item) and other times an output (converted weight of the item). I’ve seen it done with conversion software utilities of course… but I don’t know how you’d do that with a spreadsheet. That’s why I’ve created two version of my spreedsheet… one that uses ounces as the original values and one that uses grams as the original values. All formulas are then based on those values / inputs.

[Douglas Frick]

That’s the real stopping point I’ve found in turning my gear list from a dumb spreadsheet to a real app. It should be possible to have both a value and a procedure associated with a cell–that’s what “objects” are all about. But I haven’t touched VBA for years, and I’m not sure I want to start in again now :) I just enter my weights in ounces and let the pounds and grams columns calculate themselves. Checkboxes are something else that could also be added to construct packing lists, but they are either ON or OFF. My solution is to make a Quantity column, which has the advantage of allowing me to indicate carrying more than one of a particular item (e.g., batteries), which a checkbox doesn’t do well.

Hmmm…maybe I will look into VBA again.

[Douglas Frick]

After wasting some time reading manuals, Google found the answer here: http://www.ozgrid.com/VBA/run-macros-change.htm

This is the VBA code that now updates my “grams” column when the “ounces” column changes, and v.v. Column F is “ounces” and column H is “grams”.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
‘do nothing if more than one cell is changed or cell was cleared
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
‘if changing ounces, then update grams
If Not Intersect(Target, Range(“F2:F65536”)) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target.Offset(0, 2).Value = Target.Value * 28.35
Application.EnableEvents = True
On Error GoTo 0
End If
End If
‘ if changing grams, then update ounces
If Not Intersect(Target, Range(“H2:H65536”)) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target.Offset(0, -2).Value = Target.Value / 28.35
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

It’s not very elegant and can probably be cleaned up, but it works. Unfortunately, VBA is Microsoft Excel specific, and the updating didn’t occur when running the spreadsheet in OpenOffice.org. I guess, in that environment, one of the columns will need to be a formula based on the other.

In my spreadsheet between columns F and H is column G, “pounds”. The formula in this column is (from cell G2):

=IF(F2=””,””,IF(F2>16,INT(F2/16)&”# “,””)&IF(MOD(F2, 16) < 10, " ","")&FIXED(MOD(F2, 16),1,0)) This displays a nicely formatted pounds column (e.g., “Black Pine 0F sleeping bag” is “8# 4.0”). I’m too lazy to write a parser to make a pounds-column entry update the ounces and grams columns. The other day I added outlines with subtotals, so now I need to clean up and reorganize my list into groups.
P.S. Arrrrgh! The column update DOESN’T always occur if you change a value and use an arrow key to move to a different cell. You have to press <enter>, move back into the cell with an arrow key, or click the cell to get it to update. Sigh. Typical Microsoft.

PPS. Except when it DOES. Also, it thinks fomulas are numeric, so it will gladly stomp subtotals and other calculations if you even click on the cell. I’m not sure this auto-update feature is really worthwhile!

[Bob Gabbart]

Ryan,

Is there any chance that you could extend the deadline for spreadsheet submission to Dec 5th? It would be nice to have one more weekend.

Thanks,
Bob

[Jim Colten]

If there is no extension, is the deadline at 00:00 or 23:59 on 12/1?

[John S.]

I think the deadline should be extended at least one week because of the holidays. You might get alot more people finding time on their hands to compete.

[Phil Barton]

Of course, [big sarcastic, self-critical wink] this sounds like every software project I’ve ever been around. Sure, we need another week.

[Bob Gabbart]

Ryan,

One last plea to extend the deadline till Monday Dec 5. Also, can you clarify if the deadline is at 00:00 on the deadline date or 11:59.

Thank you,
Bob

[Richard Matthews]

Extending the deadline is not fair to the people that might have sacrificed time with their family to tweak their spread sheets.

The IRS convention would indicate the email has to be sent 12/1.

[Author]

Posts