Excel is evil 19

[novembertango88]

Hello everyone

I just watched an interesting talk on youtube by Colin Caprani who's a lecturer in structural engineering.
He discusses the problems with spreadsheets from the perspective of an academic but a lot of this translates to practising engineers too.

I find checking spreadsheets tedious at best and dangerous at worst.

Does anybody use any tools to make the production and checking process better?

Here's a link to the lecture on youtube;

https://www.youtube.com/watch?v=-NuTlczV72Q

It's only 20mins long.

Thanks

 

[human909]

There are specialists tools for many things. Excel is a general tool that is available and easy to use. Choose your tool appropriately.

That said this guy is a an anti Excel Zealot. Excel clearly has its uses. His comment to a well written reply shows his bias.

Appreciate your nicely articulated viewpoint. And while it is certainly possible to not make mistakes, the evidence is clear that even when massive sums of money, people's lives, or even legal obligations are at stake, people still make mistakes with Excel:

His conclusion seems to be that excel causes mistakes. No PEOPLE cause mistakes.

 

[r13]

Evil or darling is in the eyes of beholders - darling to the creator, evil to the reviewer; and most importantly, danger to the not very sophisticate users.

 

[IDS]

I've known Colin on-line for many years, and I've met him face to face a couple of times. I must say I'm a bit surprised by this presentation, considering the number of spreadsheets he has on his web site (or at least had, I haven't checked recently), but anyway I totally disagree.

Spreadsheets are not evil, any more than any other flexible and easy to use computer software is evil. Failure to implement proper independent verification of all end results that may have serious adverse consequences is evil. Switching to different software won't fix that problem, and may make it worse (if the checking is reduced because a "more reliable" design process has been used.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

He has a few legitimate complaints, particularly with regard to "raw" data sacrosanctity. But, it likewise is just as easy overwrite original data in any software program, and possibly easier in any actual programming language. I would actually argue that Excel is the least likely culprit in that regard, in the sense that we typically never operate on the "golden" original data; I usually get data in .csv or some other format, read that into Excel and save it as .xlsx, so the original data is untouched.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[r13]

In my case, evil is the headaches in reviewing spreadsheets created by persons not so sophisticated in presentation, that leads to a lot of guess works, misunderstandings, and wasted times. Though commercial software makes mistakes too, it will likely be caught and made known very quick by the purchasers due to the large user pool and daily uses. However, the privately developed spreadsheets usually do not have that level of review, uses and scrutiny, thus user beware.

 

[NorthCivil]

I used mathcad 14.0 exclusively, hated mathcad 15, then switched to Smath a couple years ago.

I peer review a lot of calculations, and cant believe how many engineers still do hand calcs or use excel sheets.

Its impossible to really review an excel sheet unless the raw sheet is provided (most engineers wont release their sheets to a peer reviewer).

 

[IDS]

NorthCivil - But why do you want to review an Excel sheet, or an Smath sheet for that matter? Doing an independent calculation is both quicker and more reliable.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[JedClampett]

We had a very energetic guy who set up calculations for certain common applications in our industry. He used Excel with visual basic. The calculations presentation look like what an engineer would do, except a lot neater. They're similar to TEDDS, except more industry specific. It was an amazing amount of work.
Some of the techniques he used to avoid the Excel pitfalls were:
[ul]
[li]The programs were locked solid and only he had the key. There was and still is a bulletproof firewall. He's retired, but editing permission is still tightly controlled.[/li]
[li]It was a strictly a fill in the blank program. Loads, length, bracing.[/li]
[li]You didn't save anything. You ran the application and printed it (either to the printer or to Adobe). You could not save your work. These were one off type programs (a steel beam; a CMU wall; etc.)[/li]
[li]The department split up and vetted the programs.[/li]
[/ul]

We still use the programs. It's a job to update them with the latest codes, but most of the time the older version of the code is fine. The senior engineers kind of police the newer engineers using them. I want to make sure they understand what they're designing before giving them a black box.

 

[NorthCivil]

IDS - some of the peer reviews I do will have 100s, or occasionally 1000s of pages of calculations. There is no way I will run a full set of my own independent calculations on a peer review, or even any of my own calculations, on the majority of peer reviews I do.

The only time I ever run my own independent calculations on a peer review is if something doesn't pass the smell test, and the math around it looks fudged to support a flawed concept. this is pretty rare.

 

[r13]

Agree with NorthCivil, that's where the Excel most useful, and powerful. Have done that for stability analysis for large hydroelectric powerplant project, too tedious to do hand calculation, but pity the downstream/third party reviewer.

 

[IDS]

IDS - some of the peer reviews I do will have 100s, or occasionally 1000s of pages of calculations. There is no way I will run a full set of my own independent calculations on a peer review, or even any of my own calculations, on the majority of peer reviews I do.

I don't see how doing a detailed check of 1000's of pages of calculations is quicker than doing independent calculations, but the point is people have different ways of working. To insist that Method A (the way I do it) is the only way to do it, and that Method B (the way unenlightened others do it) is evil, and doomed to failure, is just wrong.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[drawoh]

IRStuff,

I don't know about research, but I have collected data, dropped it into an Excel page, and linked to and processed it from the other pages. I need my original data to stay intact. I have no problems sorting and processing the links.

--
JHG

 

[IRstuff]

There are, undoubtedly, ways to screw ANYTHING up; as one saying goes, "Those who try to build idiot-proof systems always underestimate the persistence and ingenuity of idiots"

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Agent666]

Does anybody use any tools to make the production and checking process better?

Yeah, I created a number of macros that use cell highlighting to identify which cells have hardcoded constants in formulas, constant in cells, cells that have conditional formatting, cells that have data validation, cells that have array formulas, non-blank cells, etc. All run from an add-in on the ribbon, you can toggle the highlighting on and off at will. Very useful to me at least in dissecting others spreadsheets and identifying fopars such as hardcoding variable constants in formulas.

Using named ranges in excel can make cell formulas much easier to read and backcheck.

If you're not doing this, you're not using Excel right....

I find the easiest is to develop a function for your equation in the VBA interface as this makes it easier to troubleshoot...

Definitely, the other thing this helps with is if you did make an error, you can basically update it in the VBA and it will obviously reflect through to any cell that uses the function. Rather than playing wack a mole trying to find the error in numerous cells with similar equations.
Don't underestimate the power of some beautifully written comments documenting the code.
The re-useability aspect and maintainability aspect of doing it this way is also a no-brainer to me.

Other tips from me....
Make use of dynamic array formulas for repetitive calcs. Then you're only updating one equation (as opposed to dragging formulas around to repeat the calculation.

I have a personal setup where each spreadsheet in a template directory has an MD5 hash, so we can tell if someone inadvertently altered the original template. I also implement version control, having a master spreadsheet that is queried each time someone opens a spreadsheet to ensure you know when you are using an older version.

Similarly, I use GitHub to store all the spreadsheet templates with the VBA code automatically extracted via some code I wrote see here and here for the code.

Additionally, we lock the VBA down so no one can access it to make unauthorised changes, the spreadsheets are also locked down to our own network. Open one off network and certain things just won't work, and in some cases the file will be killed.

I find the person who wrote the spreadsheet is generally the best person to check it, check as they go. Produce some independent calcs to cover all outcomes, you check these, spreadsheet follows these. Usually this highlights either an error in the spreadsheet or the hand calcs and you investigate and correct. This serves as a permanent verification record, and also provides examples people can learn/understand what is being calculated. It also helps if you need to modify a spreadsheet at a later date in some instances.

Don't underestimate the usefulness of the built in spreadsheet compare program that is sitting in the excel install directory. It's super useful for checking changes between two spreadsheets.


Make use of the trace dependants and precedents tools, they are super useful for tracing how the calculated cells have been pulled together.

 

[IDS]

I hadn't heard of spreadsheet-compare. According to the link "Spreadsheet Compare is only available with Office Professional Plus 2013 or Microsoft 365 Apps for enterprise."

I found an app of the same name at:

https://www.lifewire.com/how-to-compare-two-excel-files-4690172

with a link to a free download at:

https://sourceforge.net/projects/spreadshcompare/files/latest/download

I haven't tried it, but from a quick scan of the lifewire article it looks good.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

I looked up "dynamic array formulas" and it appears only to be part of Office 365. Is that true? I recall using array formulas a long time ago, but not the "dynamic" part.

Interesting read on the dynamic additions:

https://exceljet.net/dynamic-array-formulas-in-excel

 

[1503-44]

Excel may have a lesser demon or two, but the real potential for evil is in the hands of the beholder.

 

[IRstuff]

Oh, wow, those dynamic array functionalities are WAY cooler. I was always annoyed by the linear regression array formula because I could never remember how big the "spill range" was, and now, it does it on its own.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[CrabbyT]

XLC Calc (

http://www.excelcalcs.com)

is a really nice add-in that will generate equations based on cell formulas. Here's an example.

 

[Agent666]

I looked up "dynamic array formulas" and it appears only to be part of Office 365. Is that true? I recall using array formulas a long time ago, but not the "dynamic" part.

Yeah I believe that's the case, the normal office is pretty dead in terms of them updating it to include all the cool new features that they have been adding lately. They only regularly add features to 365 versions.

Also check out the addition of lambda functions. In beta at the moment, but it has the potential to become a real game changer.