Excel's comedy of errors needs a new script, not new scripting There is a perennial fad for finding deep truths in religion. Really only one concept stands up: without reincarnation, how can one explain the constant reappearance of old ideas as new in IT? This last week has seen a couple of turns of tech's cosmic wheel. LG's 2015 V series Second Screen idea woke up in the body of the …
"The European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors."
ESRIG: http://www.eusprig.org/
In the same chapter, he also talks about the same Excel issue that you just mentioned and how biologists have had to rename enzymes because Excel "autocorrects" them
TL;Dr Another quote from "Humble Pi: A Comedy of Maths Errors" by Matt Parker -
"Tell them to use a real database LIKE AN ADULT."
I have been waiting patiently for years for the option to disable auto conversion on imported times and dates. I would not have an issue if this was customizable, but it cannot be blocked - only worked around. Have Microsoft never heard of multi-national companies using different locale settings?
Can't they focus on fixing major frustrations with the product rather than adding new features?
Similarly leading zeros: Folio 0020
Stings of digits with an 'E' in them: product code 12E3
Long strings of digits: Source ID: 12345678901234
Slashes with digits: house number: 12/13
Things that look like dates. serialnum: 01DEC
The work-around around work-around around work-arounds I've had to go to. It's. A. String. Ooooo, it looks like a number. NO. IT'S. A. STRING!
The problem is that the people who pay for most software aren't end users. The ones writing the checks tell vendors "we'd buy another X licenses if only it could do this one other thing". Because of the cost of switching to a different package, user frustration usually doesn't have much effect on renewal rates. So the economics favor new features over fixing problems.
I don't see how market forces are likely to shift enough to solve that, or how to apply regulation to change it, unfortunately. Seems to be a hard problem to solve. With security issues at least there's now some regulatory pressure and it's gradually increasing, but usability (aside from accessibility, which does have regulatory pressure) and even correct functionality are harder to control.
Great book. Another great read, which is full of such reference is "Science Fictions" by Stuart Richie. (Royal Society Book of Year nominee. Check out the of Austerity study by Reinhart and Rogoff. Global policy to implement Austerity (especially Tory UK) was based on a spreadsheet that an error in it. If you fix the error (as they did in 2013 ... it shows that Austerity should not have been the chosen route. horrific .....
Many thanks for the book recommendations.
it shows that Austerity should not have been the chosen route. horrific
Rather than the UK, I'd point to Greece to highlight the pain of Austerity as a policy. For Greece it was eventually imposed by the lenders (IMF and EU) effectively saying 'no more credit'.
Borrowing is not inherently wrong - ask boomers who now own their own homes how they got there and most will say they took on debt - and some fell foul of 'negative equity' and had to start over.
Reinhart and Rogoff's error in their spreadsheet weakened but did not destroy their central finding which was that high international debt is associated with low growth. They should not have been storing their data in a spreadsheet. It's the wrong tool for the job.
It seems entirely reasonable that paying interest on or paying off debt leaves less value to be spent on all the good things a country needs to progress - like medical care, education, infrastructure (and sadly, defence). Every now and then there is a push to forgive some international debt - which is a good thing when it's got out of control. It's a bit like a personal bankrupt these days may have their finances managed for them by an externally appointed administrator - but the law takes a very dim view if they bypass their administrator and take on even more debt.
Lastly, Thomas Herndon (the student who discovered the error) deserves high praise.
"Tell them to use a real database LIKE AN ADULT."
My career as a data professional tells me exactly this. And yet putting on an alternative hat as a user I really, REALLY understand why they do it. Users have no (or very limited) access to, or training in how to use, real databases. And in every organisation I've been, which is a lot, it's the same story - IT bods and database professional do not have the available capacity to train users, or it's considered out of their remit and seen as a waste of time by their bosses in IT and the users' bosses in business.
So the users have stuff they need doing, limited help from organisations that increasingly pigeonhole roles, and bosses breathing down their neck to get things done yesterday. And so they turn to the only tool that is available to them which can deliver, which is Excel.
Of course complex spreadsheets have errors, for the same reasons that code has bugs. It's less to do with the competence of the user creating the spreadsheet or the developer creating the code (although a fair chunk of errors is due to cluelessness), and more to do with the pressure to deliver, which means that testing, verification, error-checking etc are last priorities.
One of my peers saw me reading El Reg the other day and sneered "You don't still read that do you?" as if a couple of rungs up the corporate hierarchy should cure me of this bad habit. Well yes, I still do, and guess what? That's why I'm better informed than said colleague :-)
I have used spreadsheets for membership lists of various voluntary groups, inheriting these lists from previous society officers. How little so many people understand how to set up data systematically! British postal addresses are particularly variable and therefore difficult.
There are people who know in detail how to use word processors even though they are not computer specialists. We need the same for spreadsheets.
Hey, that's my phone number (to five significant figures)!
Of course, users should be able to enter phone numbers as strings of digits with no punctuation or whitespace. Just as they should be able to enter them with those things, and should be able to enter credit card numbers with or without spaces or punctuation, and so on. Humans shouldn't be forced to adapt themselves to trivial limitations in software.
British postal addresses are particularly variable and therefore difficult.
Postcode and house number is all you need, the rest is just redundant window dressing. Often better to ignore it anyway, people who live just across the parish or county boundary from a more desirable address often use the "wrong" town/county name because it sounds more upmarket.
As long as you also allow for house names instead of numbers. And numbers with a, b, c, etc, after them. And flats / sublets that can have compounded numbers. And all the other edge cases I've probably missed.
But yes, your point that an address has a unique identifier + postcode still stands. (But no doubt someone will now point out an example where it doesn't.)
My house is two buildings knocked into one and has two numbers, usually written as <N>–<N+2>. Most address handling systems I deal with can only handle a single number without punctuation. The majority lock onto the lower number, my bank uses the higher. The TV licence lot accepted the dual number form for their main database, but their licence checker only used one, so for years I'd get an automatically renewed licence and threatening letters telling me I hadn't got a licence and they were sending someone round. (They never did.)
"(They never did.)"
They never do.
I have never (5 decades) owned a television set. I have only recently (last decade) paid a licence fee because I discovered that, without any parliamentary debate whatsoever, there had been an order in council to regard a 'broadband connected computer' as television receiving apparatus.
Icon: waves palsied fist at heavens
Like he says, get a a refund, and stop paying.
It’s been years since a licence was needing simply for owning receiving apparatus. You need a licence in the UK to watch or record any tv channel either live or as live, regardless of where it comes from, or to watch anything on the BBC iPlayer.
You can watch all other streaming services content without a licence, unless you’re watching a live or as live broadcast - e.g. tennis or Premier League football on Amazon.
Live or as live allows for the inherent delays in broadcasting, including the short delays added to allow for cutting away if something untoward occurs.
"an address has a unique identifier + postcode still stands. (But no doubt someone will now point out an example where it doesn't.)"
Assuming you have the correct and/or up-to-date postcode.
Many companies use the same postcode from the day the premises was just 4 sticks and a bit of string in a muddy field. I used to work in a building where they had sold a parcel of land that meant that the entrance was now on a different street. One day we received a letter from Royal Mail with a new postcode on the other street and everybody immediately thought 'no more "but the entrance is on Other St", that is except for the company's building management team, who insisted on using the original address ('it's on the blueprints').... until the day I pointed out that their fire instructions gave a non existent postcode for locked unmarked gate at the end of a narrow unmarked alleyway.
I've also had a customer give an address and postcode that was perfectly valid, except it was for the same address in a town 5 miles away... they still insisted it was correct when the parcel didn't turn up (it eventually returned as 'unwanted' by a no doubt peed-off 'recipient')
"Postcode and house number is all you need, the rest is just redundant window dressing."
That was true right up to the time, several years ago, when a new development used the same numbers as many houses in my Post Code area. It has caused untold problems because most believe this wrong information and use the default address offered by the address finder. It is a real problem and landed me with a £700 unauthorized overdraft (thank you waterboard) when the tenants changed in a house down the road.
I consulted the Post Office and the idea that solely a UK Post Code and house number uniquely identify an address has been wrong for a long time.
British postal addresses are particularly variable and therefore difficult.
Do you mean postcodes? They're not. One or two letters, number 0-99, possible additional letter, space, digit-letter-letter. And the outward code is human-readable and so has a built-in error trigger.
If you mean the whole address - well, that's the same the whole world over. The Caravan, The Farm Annex, Tom's Farm, Warren Farm Lane, Flyde Moor, Heskthorpe, XY0 0XZ
The really dangerous users are not those who are clueless as they don't know enough to be dangerous. The really dangerous users are those who know a little (or think they do). Every organisation for which I have managed IT has had users of this type, often in the finance and marketing departments. If I'd had sufficient IT resources to provide database querying/report writing "on demand" to end users I would have banned the use of Excel. However, I was never in this position because the "I can do it in Excel" attitude prevailed.
Any alternative that involves making users more dependent on the IT department is going to be a very hard sell.
It's not enough to just store, query, analyse and share data. Users need to be able to do all this ad hoc, experimenting with dozens or hundreds of permutations of queries, parameters, operations and presentations, before they hit on some formula that they think makes sense.
And they "need" to be able to do all this on their own, without discussing it with another living soul. There will be failures. Many. There will be all-nighters. Reputations are made and lost that way.
Short of telling them all to learn SQL (and just imagine how much safer that would be, even if you could talk them into it), what else do you suggest?
Yeah, I suggest they learn SQL. Which is not hard, dammit! It is not harder than the crazy Excel stuff people do. It is quicker. It is less error prone.
My pet peeve: Excel translates the macro names, though I think not all of them. So instead of "=AVG( A1:A3)" you need to write "MITTELWERT(A1:A3)" or whatever the translation is, it should be "ARITHMETISCHESMITTEL", IMNSHO, because that would actually distinguish between that and the geometric mean (for example). Plus, the English macro names are in many cases abbreviations and thus shorter and similar to the names used in e.g. SQL, SSRS, or real programming languages (like, say, Fortran).
"My pet peeve: Excel translates the macro names,"
Yes it does, and it's a giant pain, especially with the recent trend of IT departments not giving enough rights to users (including tech-savvy IT ones) to change the install language of Office.
Hence one of my permanent bookmarks in one of my recent contracts being a list of Excel functions and what they are called in French
I remember the accountants at a previous firm back in Excel 97 using very large spreadsheets filling up pretty much every cell plus macros and so on.
I think the line then was that you could assume 1 error for every 20 lines in Excel so they probably had hundreds of errors in something that was basically driving the company. <Shiver>
Plus they kept breaking it because it was so large, average spread sheet back in the day was a couple of hundred Kb if memory serves, these were Mb's.
Thankfully they didn't understand Access or that would have been the next thing they would have pounced on.
On the other hand if you want to actually understand Excel and use it to it's fullest extent it's always best to talk to an accountant.
Back in the minicomputer days, someone wrote (approximately) that a spreadsheet is a program written by someone with no experience in analysis, coding, or debugging, and with an axe to grind.
This spring, in Risks Digest 33.14, John Levine wrote about a time in the 1980s, when he worked on a time-series modeling package name Javelin, saying among other things that
'We converted a lot of 1-2-3 spreadsheets to Javelin models for prospective and current customers, and found that to a first approximation, any spreadsheet large enough to be interesting had mistakes. We also found that people Did Not Care. A particularly telling comment was "it's my manager's job to find the errors in my speadsheets."'
I used Javelin. It had some very nice features but constrained functionality.
I found it most useful as a communications tool for data projections. You could document the data so that both were visible at the same time.
Problem was that it didn't really do anything you couldn't do in Lotus123, which could also do so much more. Truth was that anybody able to learn Javelin and really benefit from it could also handle creating time series support in a spreadsheet - not least because if your job needed Javelin, you were almost certainly going to be using a spreadsheet too.
Microsoft has never had any idea what-so-ever about security, and the fact that some (a lot?) of people using their products have little, or no, better idea either. Instead of bleating about rounded corners, and other niff naff and trivia that tries to emulate Apple and mobile devices, they'd do far better to secure and consolidate their products, and get some consistency in them. Enough of the eye candy syndrome, please.
Like many of us, I have seen appalling things done with spreadsheets. Spreadsheets which have become "mission critical".
Part of the problem is getting corporate IT to provide a proper solution. The solution may not be that complex, but when someone is faced with an IT department that demands a business case and a committee approval and a PM and an architect and more committees and .... Well, it's no surprise they just give up and resort to Excel. Even when errors (or regulation) could make this an existential threat to the company.
The alternative to demanding a business case and PM and architect, and having committees is the other nightmare situation - "just make it work" with all the unwritten specifications and feature creep, you end up with a mutually inconsistent mess which is never finished.
We wouldn't need PMs and architects et al if the customer could write down, clearly and unambiguously, their complete requirements with a guarantee that they cover everything, in detail, and won't grow half way through the project.
Which brings me to the role you missed off - Business Analyst. Or do you want to do all the requirements gathering, analysis, technical spec writing, cost estimation and so on yourself, before presenting the developers with a complete and full spec? Because we'd really like that, rather than it being assumed we'll do it for you, and that we can read your mind...
Well, I didn't mention BAs because ... my experience with them hasn't always been great. This was a long time ago, but my best experience was with expert users working directly with devs with oversight from a lead tech/architect.
My point being that "enterprise IT" by trying to achieve hyper-control over everything encourages shadow IT. It makes it much more likely that your accounts department is dependent upon "Dave's Spreadsheet" (Dave having left 4 years ago).
If you want to do everything with process-heavy waterfall, then fine. Just be aware that it almost never works.
The problem is that in an environment where everything is wanted yesterday, shadow IT delivers something much faster than enterprise IT. It may be a pile of festering dung that's going to kill the company in the long run, but all managers wanted an answer yesterday and shadow IT gave it to them only 24 hours late so they aren't going to kill it.
The problem is when enterprise IT takes a minimum of 3 months and tens of thousands of pounds to deliver even the smallest change.
I remember at one place chatting to a DBA about why a particular DB was slow. He was well aware of the problem and which alterations to indexes would've fixed it in minutes, but couldn't bear the idea of going through the obligatory process.
Yes, so as usual, unreasonable / impossible management demands / expectations are the root of the problem.
The failure of so-called Shadow IT is it tries to deliver on it, rather than rightfully pointing out that any answer given on management's crazy deadline would be bollocks from the start.
Maybe so-called Enterprise IT goes too far in the other direction, with schedules and requirements and so on.
But total appeasement every time is not a good path. Sometimes "no", or at least "not right now", is the only responsible answer.
Trust me, it isn't the IT that wants this. It the Business Manglement that wants this.
Simply, cost. If a project can be stalled by the beur ... beuar ... business civil service, then that has saved the company some money in avoided expense. 2 logical fallacies for the price of one.
It does take time and money to do something right. It takes a lot more time and money to document "what is right" so that when the next Excel guru throws in the towel, the next one can stumble on a bit. And there's the rub of it.
Back in the day we were analyst/programmers. That meant that we actually talked to users, possibly at their desks to observe processes,* to find out what they did and coded up a solution. Of course that sort of thing was liable to make several layers of manglement unhappy because it didn't need their high-ceremony processes. As a result it seems to have got stamped on rather heavily.
*Remote working wouldn't have made that easy.
Back in the day we were analyst/programmers. That meant that we actually talked to users, possibly at their desks to observe processes
And we got shit done.
Of course that sort of thing was liable to make several layers of manglement unhappy because it didn't need their high-ceremony processes.
Nothing makes management more unhappy than people getting stuff done without their input. They would always rather not do anything and be in control.
A smallish company I worked at many years ago, IT management decided to tighten up on procedures and documentation. Demand everything go through all these processes. The result was, the users set up their own IT department to develop stuff they needed. I saw some of the code they produced and it was horrible but apparently it did the job.
Which is exactly why they need Excel. Nobody in the whole history of business computing has ever understood a non-trivial problem well enough up front to write a spec for it. (Like, literally nobody. Not once. Anywhere, ever.) It takes experimentation, trial and error - often lots of it - to hit on something useful.
By imposing some half-arsed waterfall development methodology on them, you are erecting an insuperable barrier to at least 95% of all people who might otherwise be persuaded to take that route. (5% may be willing to attempt it, but they'll probably fail. The exceptions will be people who've already done the analysis themselves. Using Excel.)
"Part of the problem is getting corporate IT to provide a proper solution"
Sometimes a very big part. Many times I've consulted with organisations that kept their sensitive infosec documentation on group-organised sharepoint, which meant there was no individual level control over who could get access to what. In every case but one, when I proposed segregated secure storage with individual access permissions this was either rejected out of hand or the "reviews process" continued until the proposal stalled and died. And in that one case the implemented storage was only used as a source from which to make floating copies of documents that finished up on sharepoint. I often wonder whether there might have been a more rewarding career than infosec managemnt.
The problem with that is Microsoft would add a raft of "new ideas" to Excel that would do little to make it "better", but would slow it to a bloody crawl. Like that damned yellow word-suggestion thing that showed up n Outlook in the past year that absolutely slows things to a crawl while it's trying to figure out which three-letter words to suggest. Even though I've supposedly turned it off, it still rumbles back to life at random moments, like a drunk at a frat party. Imagine something like that running constantly in Excel. Yeah, no thanks. I'd rather go back to Excel 2003.
The root of the problem is that there aren't many Good Ideas left at Microsoft these days, but there is an apparently never-ending supply of stupid, pointless, and/or wasteful ideas.
No you cant. google "company destroyed by excel". provide the top 10 names pls.
Excel cocks up
and user accepted the results without thinking and peer reviewer accepted the results without thinking and managers unquestioningly accepted the results without thinking and the chief accountant accepted the results without thinking.
Now, you may be able to seriously damage a financial firm but I do not think the trading algorithms are in VBA.
Some may point to the VAR projections used by LTCM but again dont think they were in VBA and anyway, where was the human judgement.
As noted above "The managers are supposed to check..."
Having said that, plenty of lives ruined by mailmerge as noted below...
Excel can be equally dangerous when given to the military!
https://www.theregister.com/2020/08/07/army_promotion_excel_snafu/
Lot of people doing stir because of spreadsheet errors https://slate.com/business/2009/10/were-hundreds-of-criminals-given-the-wrong-sentences-because-lawyers-messed-up-a-basic-work-sheet.html . Nowdays they use an interactive document, with more text and fewer dynamic cells visible to the user, but still. Being a judge doesn't require smarts in particular.
More seriously
Business processes are continually evolving.
One of the first mistakes IT people make is that they (I) expect people to come to them with a nicely documented petition pleading for something to be different and this is considered once a year and if they feel merciful, it might appear a year after that implemented in a way that solves the documented issue but not what they (I) wanted/needed.
That "beautiful" planned, tested, trained, maintained ERP-DB system may be 100% as far as the tests the group IT have in place, but in many cases the product is less useful than the hokey old macro enabled sheet that intern did last summer.
Yes, spreadsheets do have lots of errors, but if you compared the utility of a spreadsheet and all its issues with the utility of an enterprise solution and all its issues, well it is pretty obvs that if the spreadsheet was not about as bad or slightly less bad, it would not be used.
After a few years there is a string vest (I would have said 'web' or network' but...) of linked hokey solutions that sort of work enough of the time (there is a survivorship bias here - if it failed, the sheets would be gone or the company or both) to keep the business going.
The core issue is in the gap between "Proper solution" and what a business wants. Wants change. People do not know what they want. It is usually what they are offered but with a number of 'alterations'. Once they get those alterations, there are more alterations wanted.
This is dynamic and iterative.
If IT cannot provide the service, and there is a low up-front cost (a helpful young thing who learned excel in college) the business will do it themselves with the tools they have. Most businesses work off numbers. Excel happens to be the dominant tool.
Thus the article.
I put it to you that the issue is not primarily Excel.
It is the IT team's ability to provide the service quickly and at about the same (up front) price as the business.
There are a number of solutions
- take Excel away (you will taken away first)
- teach everyone to program (but there are programmers and ... programmers)
- do something else
Like google sheets where the macro language is a bit trickier and so discourage fledgling foot gunners
Or a *really good* db / reporting tool
That "do something else" starts with gathering your business requirements and writing them down. If they're all in your head, and you're relying on mashing together a spreadsheet to turn your thoughts into business reality, you are pretty much doomed to fail.
Although many businesses do seem to run on bodges and just-in-time hacks, many businesses also fail when they come up against the challenge of things like regulatory change, or the need to grow. A big part of this comes down to not being properly organised. Excel may or may not be a culprit here, but I've never seen it used in a way which improves a business...
I think it should be harder for a big company to fail at this, but many SME sized companies the IT dept is somewhat generalist. And this is aso a problem becauwhats so different between that and theyse you encouter just process issues and getting it off the ground.
If say you are lucky and have someone who can create a db anda decent frontend, you need a PM to ease the scope creep, juggle stakeholders covey what they want to the techs in, you need stakeholders who dont really know what they want and likely don't get between what is being propsed and their excel 'database', you need to be able to explain the extra development and running costs gaints this.
User issues have a short distance to c-suite and a lot of their work has the similar short distance, thats added things to manage.
You have users that are sticky as hell to the old system (I once got roped into a fucked erp install (1st job off of helpdek of all things...) and the most fucked thing about it was all the users where point blank refusing to use it and still running all the ordering etc from the old excel, macroed, pivot tables 'now shadown' ERP system they had been using for years. (Admittedly the install wasnt confidence inspiring either),
For me excel is a problem and some of that problem is lack of understanding. Better training on even basics of data integrity and spreadsheet construction so on would go a long way, everyone is supposed to just know spreadsheets when they come in and they get a lot more complicated than word in daily use.
IInstead of everyone teaching I had some spreadsheet mokneys who know the trick and work them closely with the IT dept.
IT dept needs to get access to a decnt PM to work with the stakeholdes, it still may not be faster but theres way more conisderations sometimes also. But at least a project moves along (theorectically).
Prototyping is fine, if only to show the users why their idea won't work.
OK, being serious, a prototype can be useful in getting users to discuss and think through what it is they need. A former colleague had a bit of a problem with this approach: he could never get his users to realise that what he was presenting as a model for discussion wasn't the real thing so his attempts got snared up in their rubbishing this unsatisfactory "product".
There is a perennial fad for finding deep truths in religion. Really only one concept stands up: without reincarnation, how can one explain the constant reappearance of old ideas as new in IT?
Never mind religion, think of horror films. The Big Bad has finally suffered an unsurvivable event. The plucky nubile heroine tentatively approaches his corpse. And then he sits up.
SAP is a big offender here. Best case scenario a t-code lets you export data to a spreadsheet. If you're less than lucky it might be something like a tab delimited text file. Since you can't really do much in the way of manipulating data within SAP, you're stuck exporting it to Excel, doing your thing, then importing it BACK into SAP.
We're blaming the wrong tool here. Sure, Excel sucks, but maybe instead of blaming a hammer because it makes for a poor screwdriver, we should stop creating situations where the only tool you have is a hammer.
make sure you've selected all columns and rows for you crucial calculation. My former boss once placed and order from "his" spreadsheet, using my hand collected data for every switch closet in the network. As the end of the refresh came near, I noticed that we were 3 switches short! Ensue lots of shouting, brow furrowing and finger pointing, by everyone in the office, he finally went back to his spreadsheet and found he had neglected to select the last row in his calculation!
My handwritten list had the correct number at the bottom, plus spares for the shelf, glad the spares came in handy so fast! He got lucky, the project finished on time, and he only needed to order the spares.
I respectfully suggest that Access is still the bigger danger. The number of projects I've had where the brief starts "This team have dug themselves into a hole with Access. Get them out".
That said I did come across one case where Excel was being used as a Javascript generator for a very visible web application....
Couldn't agree more - the most messed-up small database projects I've been called on the optimize have all been written by people pretending to be DB designers and using Access to build their project.
Most of these gentry wouldn't have recognized a data structure diagram, let alone think of drawing one as the an essential first step in their development process.
Excel is like any other tool. If you haven't been trained (or seriously trained yourself) to use it and computers then you shouldn't be using it. But management today doesn't want to spend any money on employee training, and IT is far more interested in pushing everyone onto overpriced, recurring cost cloud systems so they can reduce their in house technical personnel budgets - resulting in untrained IT people as well as untrained employees. Once you learn how to use it and secure it, Excel is an extremely versatile tool when used for the purposes it was designed for. Which is exactly why it is forty years old and still used worldwide.
A hammer drill is an extremely versatile tool when used for the purposes it was designed for.
I wouldn't use one for doing my tax returns, and I certainly wouldn't put one on every desk in your average office, but there you have Excel, installed in a business environment by default...
It is called spankoverflow.com, and you have just to type "Office" in the provided search box to get video tutorials.
Or so I was told, for some reason the site is blocked from my workplace.
Major reshuffle of posts in a huge national org. This comes in the form of a 'pink and grey'. Left side of the workbook in Excel is current post. It has about twenty columns for each person, one person to a row. The grey side is where they're going. Another twenty columns. You had to check the left which was a straight database pull, and then manually change it all in the right, to whatever you had written down on a paper notepad.
There were blank spaces dotted all over the place, but that was fine, as you didn't filter or sort, you just changed the original entry in the grey.
I'd just finished the nth sheet totalling about 1000 people. Saved all the copies, all good. Put that aside. A couple of days later I'm looking at them and it doesn't seem right.
'Boss, did you review these sheets?'
'Yes, I did.'
'Did you sort or filter by any chance?'
'Yes, just to make it easier to read.'
'.... Did you save afterwards?'
'Yes... Why?'
Excel of course getting confused with blanks, who h meant entries were no longer aligned. So John in the pink on row 19 was mirrored in the grey on row 438.
We couldn't establish when the last good copy was, because he'd been doing it on the old sheets for a while. Had to scrap it all and start again.
Again, the answer here is using structure tables and formulae. This way, the sort and filter controls will always apply to your entire databody range (table). Use 'Format as table' from the Home ribbon, and then reference the table name and columns/rows in your formulae directly using the table1[column1] type format. This table method even keeps column formulae consistent in most usage cases.
I'm not entirely sure how that would be accomplished, whilst also maintaining the simplicity. Forget scripting / macros - the spreadsheet is already pretty optimal as a concept that a lot of people can grasp and run with.
The problem I see is that there's a massive gap between a spreadsheet, and programming languages. The latter may offer "better" things - better separation of code and data - but it's a very big step between a spreadsheet with a few formulae dotted around it, and a piece of software absorbing and processing the same data and outputting the same result, with graphs, print-outs, etc.
Exactly this. Then also factor in continuous development requirements! The cost / complexity step of moving from a Subject Matter Expert managing their own Excel data model to a corporate IT controlled tactical application that the SME can still operate and improve is HUGE.
This is perhaps why it's important that everyone going through the education system has to learn to code, at least to some extent.
Better everyone becomes somewhat proficient, at least to know what is possible and when to call in an expert, than carrying on in ignorance with a spreadsheet. And if every other SME is also a half way decent coder, so much the better! For example, it's interesting to see loads of biologists / immunologists who are now busily learning R.
I don't know what you think, but I don't think that purely graphical languages are an answer. Better something text based, with help (eg Jupyter?)
Doesn't anybody else remember spreadsheets as sheets, spread out on desks, with the numbers penciled in, then inked in when 'finalized' ?
And copied by hand from one spreadsheet to the next, with summery sheets calculated on calculators, and child sheets created by dividing up the pool?
Spreadsheets were always full of errors, finding, correcting, and avoiding those errors was one of the great reasons spreadsheet programs were so popular. That and printers -- printing out your spreadsheets was awsome.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
