Spreadsheets versus MathCAD or SageMATH 5

[CELinOttawa]

This says it all in terms of what I want to discuss; do you believe it? How do you approach your own work? I have always been an avowed spreadsheet guy, but this had the exact intended effect and has made me *pause* to think.

Thoughts?

Source:

https://www.ptc.com/~/media/Files/P...rGraphic_Disaster_Waiting_to_Happen.pdf?la=en

 

[dik]

I use spreadsheets all the time and of late have started programming stuff into my new TI calculator. I'm currently converting earlier source into a 3D Rigid frame program for my calculator. The spreadsheets seem to churn out the correct magnitude for answers. Once I've written a sheet and checked it, I seldom check it again unless there is a code change.

You might look into SMathStudio... a freeware Mathcad work alike. Not the same bells and whistles, bur is pretty good.

Dik

 

[canwesteng]

I'm the complete opposite; I would never touch spreadsheets for design calcs. The only thing I miss is a goalseek function. PTC has a free version of Mathcad with less content, not sure if it's meant for commercial use though. Mathcad sheets are also much easier to share within the company than spreadsheets. Idk about anyone else but when someone hands me an excel for checking I find it faster to redo the calc myself.

 

[bridgebuster]

PTC also offers Mathcad Express, which is free. Not all the bells and whistles and Mathcad files are not compatible with it.

 

[3DDave]

It depends on the type of problem. PTC has been pushing MathCAD pretty hard (or hardly) since they bought it. Their development on it has been spotty with recent releases (Prime) having less capability than earlier ones (1-15) and so much so that they include a license to use 15 with each license of Prime. Prime cannot translate earlier versions, because of the missing capabilities.

The real problem is that the people who usually create spreadsheets aren't programmers and don't use good sense in programming the sheets. The next problem is that spreadsheet software, such as Excel, fails to report basic problems, like units mismatch and make it difficult to ensure that region evaluations clearly use the expected region.

For the latter there's a program called Quantix that separates the functions from the cells, so it is easy to see what the program will do. Still no units that I know of, but better.

For targeted use MathCAD, Maple, Mathematica, MatLab, R, and so forth, along with all the 'regular' programming languages are fine, but are not general purpose solutions. Joel Spolsky, a member of an early Excel development team, wrote that their interviews with spreadsheet users turned up the amazing fact that most people used Excel because it was a gridded table and they had no need or use for advanced functionality.

http://www.joelonsoftware.com/items/2012/01/06.html

It's an old problem, probably dredged up by PTC marketing.

http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm

 

[KootK]

My thoughts:

1) I'm probably the world's biggest MathCAD fan, at least up to V15. It does offer some nice QC features. Namely, forced, consistent units and formula transparency.

2) I feel that the "Disaster Waiting to Happen" stuff is fear mongering and I'm a little embarrassed for PTC that they've taken that tack.

3) Most of the stats come from the accounting world. My brother started of as an accountant and I've witnessed him doing his thing. They take huge amounts of data, rotate it, flip it, sort it, and cross pollinate it in a matter of minutes with next to no checking. I'm not surprised that they have problems. Much of their data manipulation would be more appropriately handled in a database in my opinion. In summary, I suspect that there would be far fewer erros in our "template calc" applications. I've no problem with Excel being used in this way.

4) While a handful of errors have cropped up in some of our vetted Excel sheets over the years, I can't say that a single one has really led to any dire consequences. QC is always and for ever about less errors. In practical terms, it's never about no errors. I feel as though we've been batting a pretty good average with Excel so far.

5) Having managed some larger teams, my experience is that most hand calculations will be full of errors and there will be very little consistency between team members. Truly, I'd rather have everybody using the same spreadsheet, even if there is a small chance of a systematic error in that spreadsheet. You gotta pick your poison and I pick that one.

6) In my work, I use both Excel and MathCAD for template calcs. Most of my stuff is MathCAD and most of other people's stuff is Excel. I also use MathCAD for free form, project specific design notes which I love. Due to the transparent nature of MathCAD, we're also sometimes willing to use it for template calcs that haven't yet been properly vetted. If somebody needs to run a calc that already exists as somebody else's MathCAD sheet, it makes sense to give it a quick once over and then roll with it. That also serves as a check on the original which is nice.

I like to debate structural engineering theory -- a lot. If I challenge you on something, know that I'm doing so because I respect your opinion enough to either change it or adopt it.

 

[JStephen]

Those studies don't really help matters any. It doesn't matter to me how many errors OTHER people have in their spreadsheets, but how many errors I have in mine.
Some kinds of errors are going to be independent of the software. If your analysis is wrong, your formula is wrong, your input number is wrong, in each case, that's an error, but it'll be an error in hand calcs, in Excel, in Mathcad.
It is time consuming to go through and check an Excel spreadsheet, but even more time consuming to go back and redo hand calcs because you changed one of your initial numbers.

 

[CELinOttawa]

I have fought pretty hard to make good, consistently error free, spreadsheets. To do this I build the formulae from several small sections, each hand checked, and use cell labels so that when you look in a cell they 'read' like a formula. Every cell with a formula also has a copy of that formula in plain text (ie:the exact cell content) right next to it.

I use a lot of 'that no longer makes sense' checks too.

I have often toyed with switching to MathCAD, but my deep and unabiding hatred of proprietary file formats and our loss of my father's early work to formats which are no longer supported has really precluded the adoption of a locked in format.

Of late I am toying with SageMATH and OpenModelica... One of those may win soon enough.

 

[KootK]

It's hard to know how best to approach the legacy question.

1) Most file types are proprietary.
2) Hard to imagine an Excel free world short of nuclear/comet issues.
3) With the transparent platforms, I take comfort in the fact that I could recreate my sheets in another platform fairly easily with a printed PDF of the Mathcad sheet in hand.

What was your dad using? Lotus123?

If I had the skills, I kinda like the web app approach with Python etc.

I like to debate structural engineering theory -- a lot. If I challenge you on something, know that I'm doing so because I respect your opinion enough to either change it or adopt it.

 

[CELinOttawa]

Actually Kootk I'm from a long line of Maintenance Engineers... Most of my Dad's stuff that I lament loosing are reports written in now antiquated formats, and sometimes on formats I can no longer access. Forget the 5.25" floppy; we have some earlier 8" floppies and I even have reports on 3" floppy from the Amiga. No, NOT 3.5" which became dominant, but the original 3" drive. You can't even find evidence that it existed on the internet. So much for the sum of human knowledge thing!

So the spreadsheets aren't the real loss. Some of the custom programs would be lovely to have, and while the spreadsheet would be really nice, they are models of calcs which are mostly based on codes and change with such regularity that unless you're constantly updating they are dead in the water. You're right about the Excel format being pretty solid (as well as now effectively reverse engineered so that it should be impossible for it to go away, and I do use it for most of my custom stuff...

But the biggest point is that an Engineer who worked on the PdP series would have never seen that going way. Someone who purchased an Apple II or early Macintosh could never have imagined that a competing brand would nearly wipe Apple from the market. Both happened...

I guess it just annoys me to think that work I am doing, which should be of future value, could become inaccessible based on someone else's business decision. Yes, that's less likely than ever, but still a threat.

Answer me this: MathCAD goes the way of the Dodo, no one releases/opens the file format, and a fundamental shift in computing happens. Where are you with your custom calc sheests, other than reprogramming them into something else?

 

[IDS]

2) I feel that the "Disaster Waiting to Happen" stuff is fear mongering and I'm a little embarrassed for PTC that they've taken that tack.

I didn't notice the link was from PTC the first time I looked, although to be fair, they did but PTC down the bottom in big type.

The headlines by themselves are meaningless. It might have had some value if they compared time to complete work, and error rate between spreadsheets and Mathcad over a range of different tasks, with a range of different users. I have no doubt that there are some tasks and some users where Mathcad would perform better, and some where spreadsheets would perform better.

Whatever tool is used, any calculation where errors may have significant consequences must have an independent verification.

To blame the tool when errors pass unnoticed is just unprofessional.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

The Amiga used the standard 3.5 inch floppy** - same as Macintosh and all the PC clones. The only difference between them was the format. The Amiga team wrote a full track each time, saving space for sectors and gaining about 10% more capacity, but with the right software could write MS-DOS compatible multi-sector tracks.

My A2000 is still in the basement, along with a hundred or more 3.5 inch floppies. My first Amiga was the original A1000 with the development team signatures engraved on the inside of the top cover. (OK engraved in the mold for the top cover.)

It is unlikely that people fail to see computers going obsolete and more likely they did not see value in re-hosting their work to a new platform. I've done small scale moves and it's a pain. If the reason for the calcs is not a continuous money maker, there's some disappointment at the loss, but it's not for the value the software delivers.

**From Wikipedia: Things changed dramatically in 1982 when the Microfloppy Industry Committee, a consortium ultimately of 23 media companies, agreed upon a 3½-inch media specification based upon but differing from the original Sony design.[60] The first single-sided drives compatible with this new media specification shipped in early 1983,[61] followed immediately in 1984 by double-sided compatible versions.[62] In 1984, Apple Computer selected the format for their new Macintosh computers.[63] Then, in 1985, Atari adopted it for their new ST line, and Commodore for their new Amiga. By 1988, the 3½-inch was outselling the 5¼-inch.[64]

 

[CELinOttawa]

Yes, the LATER Amigas used 3.5". Just like you can find IBM drives using tape, 5.25, and 3.5. Unfortunately what I have is on 3" and we don't have a 3" drive, which are basically impossible to find as they were an early adopter device and quickly overtaken by 3.5". *sigh*

 

[dik]

KootK... I still have an original 5-1/4 with Visicalc, down in the basement somewhere...

 

[3DDave]

Doug - some software development environments make common errors more obvious; Excel is not one of them.

You've likely already come across the manner in which Excel fails to correctly match seemingly identical items because of internal/hidden data types. In a recent case, it fails to MATCH a text item to a numeric item even though they appear identical on screen. And the reason this is difficult is they are numeric labels for items, some of which include a "-" character that Excel automatically classifies as text, and some don't and are classified as numbers. I don't have write access to the MATCH destination sheet where this classification has taken place and have taken to coercing the MATCH item to be both string and numeric and taking the value that does not fail the match.

Had there been an initial regularity to the typing and it was changed, the spreadsheet would fail to work correctly and been very difficult to diagnose.

Hiding data types from the user is not a good thing.

Even one of the engineers you follow fails to note this particular quirk:

http://www.engineerexcel.com/excel-index-match-functions-look-up-engineering-data/

 

[CELinOttawa]

Well shoot. At least I can admit when I am wrong: It has been bugging me for a very long time that I can never find any internet evidence of the Amiga using the 3" drives, so I asked my brother (previous questions to my father have been met with blank stares; Computers are tools to him, pure and simple. Never got "bit" by the computer bug).

Apparently the added advantage of being 8 years older than me is remembering that OUR Amiga had a 3" drive because of how thoroughly annoyed our father was at having all these "perfectly good" 3inch floppies and no way to use them. So he had a guy fit our old Amstrad's 3.0 drive to an external drive bay for the Amiga. I must have one of the only collections of Amiga 3" media on the planet.

How's that for obsolescence??? *sigh*

Apologies to 3DDave... lol

 

[3DDave]

I was about to suggest that it was Amstrad, though I thought you had confused the computer names, not that there was a Frankenstein in the family. I'll bet your dad spent big bucks on those floppies. I recall they were almost $1 each, or about the price of a gallon of gasoline.

 

[KootK]

Answer me this: MathCAD goes the way of the Dodo, no one releases/opens the file format, and a fundamental shift in computing happens. Where are you with your custom calc sheests, other than reprogramming them into something else?

I would be screwed. And, really, this has alread come to pass. I tend to develop programming heavy spreadsheets and all if my good stuff is V15. Problems Inhabe now.

1) 15->Prime migration is worthless for complex worksheets.

2) With Express available, few structural firms are willing to pay for a real
license for staff. And you can't use most of the programming features in Express.

I've begrudgingly come to accept that, so long as your using out of house software, nothing lasts forever. Even the software that I've developed myself has problems because of OS changes. Stability / longevity is one of the biggest benefits of Excel in my opinion.

It's a remote possibility but, if PDF reading software ever went the way of the dodo w/o a conversion utility, THEN I'd be in real trouble. I'd probably just drive truck.

I like to debate structural engineering theory -- a lot. If I challenge you on something, know that I'm doing so because I respect your opinion enough to either change it or adopt it.

 

[CELinOttawa]

You ahould do what I've done for our olded software: I bought two identical Win XP Pro machines and turned them into digital islands.

We have. ADOS, SAP 5 and 7, Hilti HAP, and a printer to drive our 24 bit dot matrix printer all set up and running great. No internet = no Win 10 and no updates.

Before you ask: We print our own NCR paper site sheets with the 24bit dot matrix. Works great and cheap as chips.

 

[XR250]

I'm sticking with Excel. Granted, I stuck with an Amiga for way too long and currently stick with an iMac (running Win XP when needed)