Then you're only updating one equation (as opposed to dragging formulas around to repeat the calculation. Make use of dynamic array formulas for repetitive calcs. The re-useability aspect and maintainability aspect of doing it this way is also a no-brainer to me. Rather than playing wack a mole trying to find the error in numerous cells with similar equations.ĭon't underestimate the power of some beautifully written comments documenting the code. 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. RE: Excel is evil NorthCivil (Civil/Environmental) 10 Dec 20 01:15 I want to make sure they understand what they're designing before giving them a black box. The senior engineers kind of police the newer engineers using them. 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 department split up and vetted the programs.These were one off type programs (a steel beam a CMU wall etc.) You ran the application and printed it (either to the printer or to Adobe). It was a strictly a fill in the blank program.He's retired, but editing permission is still tightly controlled. There was and still is a bulletproof firewall. The programs were locked solid and only he had the key.Some of the techniques he used to avoid the Excel pitfalls were: They're similar to TEDDS, except more industry specific. The calculations presentation look like what an engineer would do, except a lot neater. We had a very energetic guy who set up calculations for certain common applications in our industry. RE: Excel is evil drawoh (Mechanical) 9 Dec 20 18:34 It's too bad as the constraints management had better feedback than any I have seen in parametric CAD programs. It seems like MA has been swallowed by time. I used a product called Mechanical Advantage (MA) from Cognition. Example: sketch up an i-beam and drive formulas from the dimensions on the sketch and extract the area of the section. 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. 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. Looking back one sees Lotus Jazz, which avoided most all the errors mentioned in that video. It's not like an alternative never existed. I suspect there is insufficient markets for excel auditing tools besides brute-force. This format is still visible in the xml in the. RE: Excel is evil IRstuff (Aerospace) 9 Dec 20 15:54Įxcel 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.įor 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. 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. The point is this: always check your work. 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. Several government reviewing agencies I've worked with have required verification calculations of every "home made" calculation tool or software. 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. Where equations are used, provide them in conventional format for easy verification. It needs to be kept simple, and references need to be provided. It's all about the writer of the spreadsheet, though. If you're talking about design calculations, Excel sheets can work really well, and can even be easy to check. It's all about understanding the limitations of the tool and applying it to the correct problem. 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 an interesting viewpoint, and I don't disagree with the crux of his point.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |