This project introduced me to a huge bug in all the major spreadsheets (Excel, Open Office, Google Docs).
Normally, any formula I make in a spreadsheet has its input either in the same row or on a different page of the spreadsheet. For example, grade books work that way since each student has his or her own row and each student's grade is calculated using only his or her own scores.
However, the cost list is an exception. Some rows represent materials and thus price a fixed amount of wood, canvas, leather, rope, iron, etc. Then built items have their own rows, in which they take their material cost from one or more of these earlier rows.
The bug involves sorting. If you try to sort rows whose formulas mix rows the result is haywire.
This is easy to test. In your favorite spreadsheet, make a small table such as the following and try sorting it by the first column. It does not matter if you use "fixed" variables, such as $B$2.
Fred 10 =B2+B3
Zoey 3 =B2-B3
Ginger 12 =B3*B4
Alan 4 =B4*2
I am both amazed the bug exists and that it took me so many years of spreadsheet use to come across it.
5 comments:
Um, there's a problem with your test data: I started by entering "Fred" into A1. This puts the formula in the Zoey row (=A2-C2) in the field C2. You cannot put a cell into its own formula.
And besides, if you have to move things around, wouldn't you use the fixed-figure commenting feature (I don't know what else to call it). Like $B$2 instead of B2?
Oops. Fixed now, thanks.
Okay, you've still lost me here. If there are text values in column A, how can any of the formulas be for values A2?
More oops. I'm too rushed, trying to get this done while Smiley is babbling in his crib (and not falling back asleep after waking early from a nap).
Funny: In the setup you have (1) alan times 2, (2) zoey plus ginger, (3) ginger times alan, and (4) zoey minus ginger
After Sort by column A, you get (1) alan times 2 [same], (2) zoey plus ginger [same], (3) ginger times zoey [different], and (4) zoey minus empty cell C5.
Of course, I never would have thought about sorting without converting the formulas to the value of their answer: Highlight Column C (formulas), Copy, Paste Special...Values. Then Sort.
But I'm a weirdo. :)
Post a Comment