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
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 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
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 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
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 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
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16611 Visits: 6534
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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
Spot ON! thank you for my Feel Good Article of the Day!
Steph Locke
Steph Locke
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 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
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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