April 20, 2013 at 12:34 pm
Comments posted to this topic are about the item Excuses Execrating Excel
April 20, 2013 at 2:52 pm
Excellent editorial. Made me laugh.
Best wishes,
Phil Factor
April 20, 2013 at 6:41 pm
There is a saying, "A bad workman always blames his tools".
It occurs to me that Excel is the wrong tool for the job in so many cases. The bottom line (pardon the pun) is that the businesses should be getting the right analysis tools instead of blindly relying on a one-size-fits-all solution.
April 20, 2013 at 7:44 pm
Thank you, thank you, thank you.
When I read reports like this in the newspaper (yes, we still have one of those), I rail against them. My wife can't understand why I am so excited.
Now, if we could just get people to talk rationally about evolution....
April 20, 2013 at 11:47 pm
Same thing happens with SQL Server (or any other software for that matter). In 2005, in a dysfunctional government department not too far from here, an employee (let's call him Bob) was charged with the task of porting a mountain of SPSS code to SQL Server. The decision was made by his boss, an angry ignoramus who understood neither SPSS or T-SQL. And understood even less, how to deal collaboratively with subordinates.
Bob didn't know much T-SQL himself and thus didn't want to be porting anything anywhere. Bob's boss helpfully decreed that Bob either did what he was told or considered other employment arrangements. So Bob dutifully did what he was told, but with an overriding philosophy in the vein of "If it compiles, ship it." Several errors were identified during the process and invariably Bob would say "It's SQL Server that's the problem - it won't reliably produce the right answers, and talk about SLLLOOOOWWW. Don't get me started on that!" Then Bob resigned, leaving dozens of totally uncommented stored procedures replete with invisible "SQL Server" errors (the worst kind). A classic example was the good old "Let's divide an integer by an integer and then wonder why it produces an unexpected result" trick.
print 1/2
print 1./2
print 1/2.
print 1./2.
That was 2005 and to this day errors still bubble to the surface. The boss's rationale for years of avoiding a comprehensive code audit seems to be
1) If problems are uncovered, it's going to be embarrassing, and
2) The code produces answers that seem about right, so why go hunting for problems that might not even exist.
A lot of sad things came out of this. A staff member resigned, leaving a mess. The acceptance of SQL Server BI (which were far more appropriate tools than SPSS in this situation) in related government departments was set back years because of the misinformation. Saddest of all though was that big decisions affecting some of the most vulnerable segments of the community have been made based on calculations that are probably inaccurate enough to affect those decisions.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
April 21, 2013 at 7:11 am
Doesn't matter what software you use - if you don't understand your data, or if you ask the wrong questions, you're going to get worthless results. Over and over I tell people, just because a computer spit it out, doesn't mean it's good. Sometimes they listen.
Years ago I had a girlfriend in a PhD. program, and she came to me because her calculator was broken. She was trying to figure out something called Gibbs free energy in a chemical reaction, and the calculator kept saying 'Overflow', so there was clearly something wrong with it. Turns out the formula she was using contained 10^10^6, naturally, far beyong the poor calulator's range. But she had no clue that the formula she was using was nonsense, and if the calculator had managed to spit out the answer in some tidy notation, I'm sure she would have just copied it down and turned it in, with no clue that the amount of energy cited would be far in excess of the total known energy in the entire universe.
I'll go even further - if you don't understand your data, you have no business asking questions of it.
April 21, 2013 at 9:24 am
I wonder how often software is validated before it is used. Taking Excel as an example, it's a fantastic piece of software and is extremely useful, I use it a lot, but has anyone outside the Microsoft campus taken the trouble to check that the results of various functions are actually correct to the degree of accuracy required by the specific application. When a complex series of calculations are concatenated, how can the compound errors affect the end result? When banks are applying their rocket science calculations using such a tool, how confident can they be that they are getting even close to the correct result?
Now what was that about Sarbanes Oxley and best endeavours?
April 21, 2013 at 9:03 pm
@gpo, at least you made me feel a little better about my current situation after that story. It makes me wonder, how do you make an ignoramus like that responsible? Because he probably got a fat bonus for all the 'savings from SPSS licencing costs', while downstream, the public services real customer is suffering.
It's bad enough when the business creates Feral Apps and they become business critical and 'must be supported by default'. It's worse when Feral apps are being built inside IM and people being rewarded for it.
I find myself being dragged into sneaky politics in order to try and throw some light on the dimwitted decisions being made.
April 22, 2013 at 2:39 am
There's a saying:
"Don't blame the tool, blame the fool behind the tool".
April 22, 2013 at 3:03 am
herbaltea001-winter (4/21/2013)
I wonder how often software is validated before it is used. Taking Excel as an example, it's a fantastic piece of software and is extremely useful, I use it a lot, but has anyone outside the Microsoft campus taken the trouble to check that the results of various functions are actually correct to the degree of accuracy required by the specific application. When a complex series of calculations are concatenated, how can the compound errors affect the end result? When banks are applying their rocket science calculations using such a tool, how confident can they be that they are getting even close to the correct result?Now what was that about Sarbanes Oxley and best endeavours?
There is some work out there on how accurate the various spreadsheet programs are...
http://www.jstatsoft.org/v34/i04/paper
Excel is an all-round tool, but proper statistical analyses should be done in sas/r/spss/AN Other. I love excel but there is the mistaken belief that because it is on your desktop and anyone can type in it, it is easy - this gives people a false sense of confidence that leads to insufficient thinking and testing.
April 22, 2013 at 3:52 am
The report seems to show that none of the popular spreadsheets are up to the job. I doubt if they were ever intended to be used for such critical calculations. This is a little reminiscent of the warning that comes with Java in that it should not be used for aviation or nuclear facilities.
April 22, 2013 at 4:38 am
herbaltea001-winter (4/22/2013)
The report seems to show that none of the popular spreadsheets are up to the job. I doubt if they were ever intended to be used for such critical calculations. This is a little reminiscent of the warning that comes with Java in that it should not be used for aviation or nuclear facilities.
It's a few years out of date so the conclusions might not hold for later excel versions, but it's a question of whether a spreadsheet *should* go to that level of accuracy in such hugely niche cases, particularly when there are programs specifically designed to handle these situations with ease?
Hopefully, what the devs think is something along the lines of:
A spreadsheet is meant to be good at a very broad range of tasks and time should be spent delivering great functionality to >0.1% of users instead of spending time making it correct for the 0.1% who may or may not wake up and realise that they needs a stats package and can get them for free.
I never knew that about java, but it makes sense that a still evolving language can introduce risks into an existing codebase and you certainly wouldn't want it to affect super important things. Of course this is partly why FORTRAN and COBOL are still used in banks :crazy:
April 22, 2013 at 5:20 am
Aside from the errors introduced by individual functions, most spreadsheets have chained functions, each contributing its own error. On a good day the errors might cancel but good days are hard to find.
Can't remember where I saw this but it fits...
Always use the right tool for the job.
A hammer is always the right tool.
Anything can be used as a hammer.
April 22, 2013 at 6:29 am
herbaltea001-winter (4/22/2013)
Aside from the errors introduced by individual functions, most spreadsheets have chained functions, each contributing its own error. On a good day the errors might cancel but good days are hard to find.
The same is true of any overall system which gathers data, transforms it, and aggregates it 😀 IT folks are just less confident that everything they do is 100% correct and put steps in (hopefully) to try and find errors before they cut millions of pounds off their company's net worth
I've found two ways to cut some of the more common errors in excel:
1) a standard layout for spreadsheets (summary[1:n], raw data, code/definitions) where the raw data in particular is rigidly formatted (PK on LHS, no column or row gaps etc) which encourages people to think about the different layers of analysis as well as documentation
b) a macro to build dynamic named ranges so that raw data can be referenced more safely by pivot tables, vlookups and [sum]ifs. This cuts down on the reference errors like incorrect $ signs and ranges not fitting all the data, and also greatly increases the ease at which someone can look at a formula and understand it e.g.
sumifs(balance, inceptiondate, ">="&reportstartdate, inceptiondate, "<="&reportenddate)
and
sumifs(sillySheetName!$A$2:$A$156732, sillySheetName!$D$2:$D$156732,">="&AZ1, sillySheetName!$D$2:$D$156732, "<="&AZ1)
April 22, 2013 at 7:01 am
My Mom's final bill from the personal care home was about $90 higher than I calculated in Excel. The home calculated the charges based on a 30 day month and I calculated them based on the 31 day month she died in.
I'm fine with Excel's calculations, and when I dealt with reporting billions of dollars the issues weren't with Excel.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply