Even worse, this particular sheet now has entries all the way out to GI, and there are over a dozen sheets in the workbook, some of which are just as bad. :smack:
This is what happens when you give a data-heavy project to someone who only knows Excel. And then get asked for lots of additional derived information in timeframes that precludes you being able to tidy the damn thing up into a proper database.
Ah well.
I just jumped back into that world myself. I’ve been a database analyst for the past couple of years and recently I was offered a promotion (which I accepted) as an information analyst.
The difference there being a step up from yanking numbers out, to actually doing something with them, but I digress.
One of my qualifying skills was that I knew databases. Then they showed me the “database” I was going to be working on – it’s one Excel file with about 15 tabs. The main “database” tab has about 150 columns, and the rows are further separated into different sections. And all of it flows to a bunch of other tabs which act as reports … once they are PDF’ed of course.
It’s like I took a step back in skills needed.
But at least they’re paying me more for it.
I hope all that data is still on track.
<d&r>
Actually, I think it’s pretty much fucked. Or at least, on the verge of collapse.
For instance, I just spotted where a colleague has inserted a column in the middle of a block of unit costs to calculate a unit time. Unfortunately there is a column on the end of the block which uses sum() over that block, and which now is totalling Euros and minutes. :dubious:
There’s probably more stuff like that somewhere in among all this crap, and that’s on top of all the other quirks/bugs/errors like if()s referencing blank fields that used to have something meaningful in them umpteen version prior to the consultants handing this thing over (I think we got it at v64). At least I got what I needed out of it, with no more errors than were in the original data.
Ah, a businessman’s database. Lovely.
Time to switch to FileMaker, I’d say
I wish I knew more about Access or any other database I could dump my stats in and pull up nice reports. On behalf of us management types I can only say, Excel is incredibly easy to start out with and, by the time enough information has amassed that we really should put it elsewhere, it’s too late and we’re stuck with massive Excel files that run the risk of an errant inserted column or row that corrupts equations.
Although in my defence, although I have gone to double digits on tabs, and quadruple digits in rows, I have never gone beyond the A* series of columns. The F* columns seem excessive, even to a hack like me.
Heh. GI. Track. Very nice.
I have been on Spreadsheet DC (A thru Z then AA thru AZ then BA thru BZ etc…) within one humongous Workbook that was This Month’s Workbook; and each Spreadsheet contained what seemed like a ludicrous number of columns (not into the Fs though…), and each spreadsheet’s A and B columns referenced calculated values from the previous freaking sheet. And Spreadsheet A referenced the previous ^#$@!@@ WORKBOOK, ok?
It was all about chasing moving targets: salesperson commissions that, like bowling scores when you’re rolling strikes and spares, counted for more if in the previous session, you’d done well. Oh, and people with more seniority got better commissions. So date hired subtractee from current date plugged into a formula associated with chunks of previous months’ commission and month before that and month before that and month before that ad freaking infinitum, as a multiplier multiplied by zillions of small numbers each of which is a commissionable item.
Oh, each item’s commission was distributed across up to 10 “roles”. A given salesperson’s take of the commission depended on whether the other roles “downwind” were live, each of which, if it were, took a piece of this person’s pie. Whereas this person took a piece of whatever pied did lie above. Assuming those roles were not empty.
Oh, some items would get cancelled (deactivated) up to 6 months later. That would be assessed as a chargeback against commissions that had been paid in the up to 6 months earlier period. Oh, except that sometimes people who had worked at the company 6 or fewer months ago were no longer employed, so they could not be charged, so their chargebacks were distributed among those who are currently being paid, but only those who have been working for at least 3 months, the rest are exempt.
Oh, and a certain portion of the commission is not paid only in the month for which recipients are qualified, but rather they keep paying for up to 2 freaking years assuming there is no service deactivation. As with dhe deactivations, if the recipients depart the company, the remaining employees get the distributions of those commission assets but only if they have been working at least 3 months.
Oh, and on average of approximately once every 18 months, the parent company that pays all these damn commissions changes their rules on what comms they will pay, so activations prior to a certain date get Rule 1 and those subsequent to that get Rule 2.
Trust me, it’s just easier in FileMaker. It will still make you hurl stuff at the walls and curse, but it’s easier. Formulas are in one place, data is in another.
Ah, the dearly beloved Excel Hell. I work for a database consultancy, and we often get landed with 50 MB Excel files with dozens of tabs that reference other files in a delicate tracery of reporting, held together by glue, string and willpower, apparently. Such grace! Such elegance! Such artistry!
Except, of course, that only one person really understands what the hell is going on, and if anyone so much as sneezes, it all breaks and resembles nothing so much as a junkyard post-tornado.
It’s amazing how these things get out of control so quickly. I still fondly remember the moment when we replaced an entire, bulky, awkward, cobwebbed-together Excel report with a couple of pivot tables feeding off a database that actually gave them more data. Joy.
"Dear Boss,
“You know this business-critical spreadsheet on which I work? I’m not convinced it will withstand an audit. For instance, I’ve just discovered X, and there’s a considerable lack of documentation, so how can we be confident of correct results? I’d like to spend a week going through it properly while documenting it properly. This will bring to light any other deficiencies which we can then correct. Looking forward, we can use this documentation to determine if another platform might be more suitable to our business needs.”
The killer sentence is the one about the audit. After that you’re being proactive about resolving the issue. And, if your boss has not long been in the job, he or she can claim credit too - after all you’re a member of their team.
Change that to “I’d like [technically competent analyst] to spend two weeks rebuilding it in Access while documenting it properly” and I think we have a winner.
AHunter3 - There are two opposing views on sales compensation schemes. Once holds they should be simple and obvious, so it’s immediately clear to the sales tarts how behaviour affects their paycheck. The other holds that the more byzantine the better, so that you can pay people whatever you feel like and no-one without a PhD in Creative Accounting can successfully challenge a payment.
Got me beat for columns, but I do have 12594 rows in one of my worksheets. What the client wants, the clients gets. :rolleyes:
That may be the aim, but it’s not the correct first step. Document it properly, then use that document to show that Excel is not the most appropriate package. Then get the thing re-implemented with Access & SQL as appropriate using your documentation as the first version of a design document