Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Excuses Execrating Excel Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 3:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 8:22 AM
Points: 35, 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.
Post #1444908
Posted Monday, April 22, 2013 4:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:18 AM
Points: 162, Visits: 836
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
Post #1444919
Posted Monday, April 22, 2013 5:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 8:22 AM
Points: 35, 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.
Post #1444926
Posted Monday, April 22, 2013 6:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:18 AM
Points: 162, Visits: 836
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)
Post #1444946
Posted Monday, April 22, 2013 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:57 AM
Points: 169, Visits: 1,848
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.
Post #1444965
Posted Monday, April 22, 2013 8:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 8:22 AM
Points: 35, 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?
Post #1445007
Posted Monday, April 22, 2013 8:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 5,565, Visits: 3,414
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!!!
Post #1445011
Posted Monday, April 22, 2013 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:59 AM
Points: 6, Visits: 15
Spot ON! thank you for my Feel Good Article of the Day!
Post #1445030
Posted Monday, April 22, 2013 9:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:18 AM
Points: 162, Visits: 836
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.
Post #1445032
Posted Monday, April 22, 2013 9:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 8:22 AM
Points: 35, Visits: 69
Coding errors are not necessarily dependent on the software, although some are easier to use than others.
Post #1445037
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse