SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excuses Execrating Excel


Excuses Execrating Excel

Author
Message
herbaltea001-winter
herbaltea001-winter
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 69
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.
Steph Locke
Steph Locke
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 870
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
herbaltea001-winter
herbaltea001-winter
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 69
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.
Steph Locke
Steph Locke
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 870
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 :-D 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)
Robert.Sterbal
Robert.Sterbal
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 2000
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.
herbaltea001-winter
herbaltea001-winter
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 69
Would it be true to say then, that an experienced Excel (or other spreadsheet) practitioner would be aware of the sources of error and be able to factor them into the calculation?
Gary Varga
Gary Varga
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27195 Visits: 6545
Clearly if Steve had written a poor editorial he wouldn't be putting it down to the tool he wrote it in.

Has Word ever won a Pulitzer or Excel a Nobel Prize for Mathematics?

Lazy journalism. Yet another reason why I no longer read newspapers,

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Brad Roberts-480600
Brad Roberts-480600
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 15
Spot ON! thank you for my Feel Good Article of the Day!
Steph Locke
Steph Locke
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 870
herbaltea001-winter (4/22/2013)
Would it be true to say then, that an experienced Excel (or other spreadsheet) practitioner would be aware of the sources of error and be able to factor them into the calculation?


So, there are two types of error to account for:
1) Coding errors - where the user codes incorrectly
2) Computational error - where functions cause precision/rounding issues

For 1, there are many common issues and certainly someone experienced can put preventative steps, tests, and reconciliations in to prevent or uncover issues. For 2, it's doable to be able to highlight areas where imprecision can occur and giving a general error range, but quantifying the exact impact on the end calculation can be difficult without recourse to an outside system.
herbaltea001-winter
herbaltea001-winter
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 69
Coding errors are not necessarily dependent on the software, although some are easier to use than others.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search