Topic
BPL Gear List Spreadsheet CONTEST
Forum Posting
A Membership is required to post in the forums. Login or become a member to post in the member forums!
Home › Forums › Gear Forums › Gear Lists › BPL Gear List Spreadsheet CONTEST
- This topic is empty.
-
AuthorPosts
-
Nov 19, 2005 at 7:48 pm #1345491
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.
Nov 19, 2005 at 7:51 pm #1345492Anon is right. You can download OpenOffice for Mac OS X. But my gear spreadsheet works fine with Excel on either Mac or Windoze.
Nov 19, 2005 at 8:10 pm #1345496Thanks 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.
Nov 19, 2005 at 8:44 pm #1345499I, 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=14Hope that is a good solution.
Nov 19, 2005 at 9:29 pm #1345500AnonymousGuestOpenOffice 2.0 isn’t working on my mac running OS 10.3. I will try version 1.1.2 tomorrow.
Nov 19, 2005 at 10:11 pm #1345504Miguel, 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.
Nov 20, 2005 at 5:42 am #1345510I 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.
Nov 20, 2005 at 4:34 pm #1345546Bill 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.
Nov 22, 2005 at 6:10 am #1345680Quick 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”.
Nov 22, 2005 at 2:26 pm #1345724You 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!
Nov 22, 2005 at 2:30 pm #1345725Thanks 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.
Nov 23, 2005 at 5:08 am #1345782This 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.
Nov 23, 2005 at 9:33 am #1345794You 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.Nov 23, 2005 at 9:57 am #1345795I guess I can live with an error of 5.6690 grams over 9.07184 kilograms.
Nov 23, 2005 at 10:24 am #1345799It 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?
Nov 23, 2005 at 10:38 am #1345802Bob,
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.
Nov 23, 2005 at 1:30 pm #1345820Hey 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.
Nov 24, 2005 at 7:50 am #1345846That’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.
Nov 24, 2005 at 9:49 am #1345849After 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 SubIt’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!
Nov 29, 2005 at 10:33 am #1346142Ryan,
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,
BobNov 29, 2005 at 11:22 am #1346147If there is no extension, is the deadline at 00:00 or 23:59 on 12/1?
Nov 29, 2005 at 5:43 pm #1346175I 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.
Nov 29, 2005 at 5:51 pm #1346177Of course, [big sarcastic, self-critical wink] this sounds like every software project I’ve ever been around. Sure, we need another week.
Nov 30, 2005 at 7:36 am #1346209Ryan,
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,
BobNov 30, 2005 at 10:02 am #1346210Extending 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.
-
AuthorPosts
- You must be logged in to reply to this topic.
Forum Posting
A Membership is required to post in the forums. Login or become a member to post in the member forums!
Our Community Posts are Moderated
Backpacking Light community posts are moderated and here to foster helpful and positive discussions about lightweight backpacking. Please be mindful of our values and boundaries and review our Community Guidelines prior to posting.
Get the Newsletter
Gear Research & Discovery Tools
- Browse our curated Gear Shop
- See the latest Gear Deals and Sales
- Our Recommendations
- Search for Gear on Sale with the Gear Finder
- Used Gear Swap
- Member Gear Reviews and BPL Gear Review Articles
- Browse by Gear Type or Brand.