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

 

[MegaStructures]

Of course excel makes the input and checking of long equations extremely difficult. If they would add in some better graphical input options to compete with Mathcad it would be amazing. If I have to use excel to do a calculation that uses a lot of spreadsheet type data I usually create a separate Mathcad calculation to verify my equations and then drag the verified equations "through" my spreadsheet data.

“Any idiot can build a bridge that stands, but it takes an engineer to build a bridge that barely stands.”

 

[dik]

For unwieldly formulae, I often write then in 2 or 3 parts and combine by cutting and pasting... about 90% of the spreadsheets I have, I've written by myself and know the content is reasonably correct.

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Feel any better?

-Dik

 

[rb1957]

like most things, it depends on the author. A well laid out s/sheet is easy to understand. A trusted source is, well, trusted. Yes, it is easy to hide things in the calcs (intentionally or not) and very easy to make errors (particularly if you enter a hard number rather than linking to the cell that derived it).

another day in paradise, or is paradise one day closer ?

 

[phamENG]

It's an interesting viewpoint, and I don't disagree with the crux of his point. If you're dealing with large masses of data that require significant processing, then you should absolutely use a more appropriate database tool. It's all about understanding the limitations of the tool and applying it to the correct problem.

If you're talking about design calculations, Excel sheets can work really well, and can even be easy to check. It's all about the writer of the spreadsheet, though. It needs to be kept simple, and references need to be provided. Where equations are used, provide them in conventional format for easy verification. When reviewing a document, what's the difference between a hand written c=a+b=5+3=9 and a computer generated "8" next to "c=" with a "c=a+b" next to it, so long as a and b are also defined elsewhere? I agree that very few people do this, though, and that IS a problem. Several government reviewing agencies I've worked with have required verification calculations of every "home made" calculation tool or software. Because this problem isn't limited to Excel - I could write a Python code to do some neat calculation that draws from the wrong data set, and you'd never know. The point is this: always check your work.

In terms of other tools that make checking easier, WYSIWYG programs like SMath and MathCAD are nice and usually pretty transparent, though it's possible to write functions and push them out of the printable area.

 

[IRstuff]

By its very nature, Excel was intended to deal with repetitive and arrayed calculations.

It's unfortunate that Mathcad's price has gone up so dramatically from the days when you could get it for $99, particularly since it is so much more amenable to the types of calculations used in engineering. AND, it does UNITS!

Anything that requires units, I put into Mathcad; anything that requires some hint of understability, I put into Mathcad. However, Excel is still better at displaying data in graphical form.

SMath Studio is a free facsimile of Mathcad, but I still find it to be a bit clunky.

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

 

[JoshPlumSE]

The biggest issue I had with an Excel spreadsheet was when a subordinate modified the spreadsheet calculation, but didn't modify the descriptive text for the calculation. Essentially she modified it to use a doubled up cold formed stud. I would have preferred she add an extra line showing the doubled-up stud.... making it clear how it differed from the original spreadsheet.

Because of that, I told the client one thing, but then had to back track a couple of days later. My boss, at the time, hated it when we had to do stuff like that.

 

[Mohanlal0488]

I see a lot of comments with regards to huge equations which are put into excel. I find the easiest is to develop a function for your equation in the VBA interface as this makes it easier to troubleshoot...

 

[1503-44]

Yes, I make user defined functions when I intend to use the calculation multiple times.

For complicated formulas what helps me the most is a liberal use of named variables rather than using simple cell references.

 

[rb1957]

to paraphrase the hand gun issue in the states ...

excel isn't evil, people who use excel can be evil.

another day in paradise, or is paradise one day closer ?

 

[JLNJ]

In the macro, it's about critical thinking skills. Blindly using somebody's spreadsheet can erode these skills or leave them underdeveloped.

Me: Where did these loads come from?

PE, MSCE(structural): ASCE 7. Fred's spreadsheet.

Me: Why this spreadsheet? It looks like it's for Non-Structural Components, Chapter 13. You have a Non-Building Structure.

PE, MSCE(structural): It's what Fred told me to use.

Me: Fred doesn't know what he's doing - don't listen to Fred. Look at ASCE 7 Chapter 15.

 

[JoelTXCive]

I second what 1503-44 said.

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

You have to be careful of global versus local names, but once you get the hang of them they are great.

 

[atrizzy]

I make sure the spreadsheets I design don't promise too much. If you keep your calculations simple and you're aware of the limitations of the tool, you can easily amass a set of several tools to quickly perform most of your calculations in a series of steps.

In my experience, trying to get a spreadsheet to do too much is just asking for trouble. Either it will be useless for repeat applications or it will have errors that are impossible to find.

Make the calculations transparent and visible in your spreadsheets. Show your work. This isn't a programming exercise.

 

[3DDave]

Excel is the little voice that says "this will be easy, just do it." That seems like it's the basis of evil in the hands of the incautious.

For auditing though? It used to be possible to write out explicit formulas with a text editor exactly the way the video says is applicable to the development of software, though the lack of control structures, such as loops, makes it really verbose. This format is still visible in the xml in the .xlsx and .xlxm format files.

I suspect there is insufficient markets for excel auditing tools besides brute-force.

It's not like an alternative never existed. Looking back one sees Lotus Jazz, which avoided most all the errors mentioned in that video. It seems to live on in a product called Quantrix which is used by companies where errors are unacceptable - financial firms, but I expect the "call for a sales negotiation" means it's too expensive for most people.

I would have been on board with MathCad if it had a 2-d geometry constraints solver so that graphical depictions driving calculations was involved. Example: sketch up an i-beam and drive formulas from the dimensions on the sketch and extract the area of the section. I used a product called Mechanical Advantage (MA) from Cognition. An example paper on this is

http://papers.cumincad.org/data/works/att/ab63.content.pdf

It seems like MA has been swallowed by time. It's too bad as the constraints management had better feedback than any I have seen in parametric CAD programs.

 

[drawoh]

novembertango88,

I can make equations as intelligible as equations in Octave and MathCAD, by naming cells. Excel has excellent support for this. The multiple sheets are a useful resource for managing raw data in spreadsheets. Change control is a significant effort with any analysis application, including spreadsheets, code, and FEA[ ]models.

Spreadsheets, like a lot of computer tools, definitely including CAD, are not idiot resistant.

I have prepared documents in LaTeX. I have used Octave to do calculations, and generate LaTeX code to be integrated with the document. I control all this from a Makefile. Are people out there willing to learn code like this?

--
JHG

 

[dik]

SMath Studio... a little clunky, but still a really good program.

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Feel any better?

-Dik

 

[skeletron]

The presentation is mainly about bad practices in Excel. It's a pretty good tool for rapid calculation, but if you aren't checking/writing/understanding your own code it doesn't matter the program or language.

Also, he's approaching it from a research point of view. In practice Excel is more a tabulation of my recognizable, repetitive variables. I do agree that coding up something can generate a better solution, but:
(a) I don't necessarily have that time or resource when a made-to-work program is available
(b) Excel data, if formatted correctly, is much easier to verify through rational methods than using ASCII printout of a coded program.

 

[IRstuff]

Pretty easy to write convoluted and unreadable code, too; if the coder is undisciplined, then variables will be defined without comment or unit and woe to the subsequent coder trying to make changes.

One thing that's really good in Mathcad and Smath is when you use units and the program squawks because your units don't match, or you'll get a stupid unit in the result because you mistyped something. And it's a great check when your final answer comes out with the correct units, which is something Excel can't do 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

 

[canwesteng]

Yeah, despite how people may talk about naming cells, it's simply impossible to make excel as clean as Mathcad no matter how hard you try.

 

[271828]

SMath to the rescue.

I've switched mostly to it in the last six months or so.