Excel woes - a Mathcad Marketing opportunity?

[StuartBruff]

Microsoft Excel blamed for gene study errors - BBC News

Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.

...

The researchers claimed the problem is present in "approximately one-fifth of papers" that collated data in Excel documents.

The trio, writing for the Melbourne-based academic institute Baker IDI, scanned 3,597 published scientific papers to conduct their study.

They found 704 of those papers contained gene name errors created by Excel.

Ewan Birney, director of the European Bioinformatics Institute, does not blame Excel and told the BBC: "What frustrates me is researchers are relying on Excel spreadsheets for clinical trials."

The Excel gene renaming issue has been known among the scientific community for more than a decade, Birney added.

He recommended that the program should only be considered for "lightweight scientific analysis".

Sounds like a Mathcad opportunity to me ...

Stuart

[VladimirN]

StuartBruff wrote:

Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.

...

Are there any examples of how it looks like in Excel?

[Werner_E]

VladimirN. wrote:

StuartBruff wrote:

Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.

...

Are there any examples of how it looks like in Excel?

-> https://bmcbioinformatics.biomedcentral.com/articles/10.1186/1471-2105-5-80

[VladimirN]

Danke!

[VladimirN]

StuartBruff wrote:

  ... Sounds like a Mathcad opportunity to me ...

No. For all of us.

[JonathanHodgson]

VladimirN. wrote:

StuartBruff wrote:

  ... Sounds like a Mathcad opportunity to me ...

No. For all of us.

I think Stuart's phrase means "to me, it sounds like a Mathcad opportunity," not "it sounds like a Mathcad opportunity that I can make use of."

[Werner_E]

StuartBruff wrote:

Sounds like a Mathcad opportunity to me ...

Maybe, but who should take this opportunity? PTC? Are you kiddin'?

[StuartBruff]

Werner Exinger wrote:

StuartBruff wrote:

Sounds like a Mathcad opportunity to me ...

Maybe, but who should take this opportunity? PTC? Are you kiddin'?

Well we could try appealing to PTC's better nature via the Bottom Line.  There's probably money to be a-making in genetics by getting the analysis right ...

Stuart

[AlvaroDíaz]

Hi Stuart. This is an historical characteristic of traditional Excel behavior, and came from the times when Microsoft emulate the IBM Lotus 1,2,3 bugs. Others examples, from news of year 2013: Microsoft's Excel Might Be The Most Dangerous Software On The Planet or 88% of spreadsheets have errors - MarketWatch .

Best regards.

Alvaro.

[dschenken]

I expect this is one sort of error the Quantix Modeler was developed to avoid.

Quantrix Key Concepts - YouTube

It was originally Lotus Improv, a much cleaner concept than Excel. I expect that for any Quantrix category there can be only one data type, so confusing dates with names and values is not possible in the middle of a sheet.

It seems to me that an RDBMS would be better, or software like R for dealing with bulk data manipulation.

[StuartBruff]

David Schenken wrote:

I expect this is one sort of error the Quantix Modeler was developed to avoid.

Quantrix Key Concepts - YouTube

It was originally Lotus Improv, a much cleaner concept than Excel. I expect that for any Quantrix category there can be only one data type, so confusing dates with names and values is not possible in the middle of a sheet.

It seems to me that an RDBMS would be better, or software like R for dealing with bulk data manipulation.

Yes, it would.  However, the major problem with Excel is that it is ubiquitous, and is the standard tool that managers and accountants understand and, thus there is often a large corporate pressure to use it for things that Excel is ill suited to.  As minor examples, ...

I wrote a quick Mathcad worksheet to solve a particular one-off, never-to-be-repeated problem.  The resulting worksheet was a virtual transcript of the problem and method of solution and, to be frank, an illiterate monkey could have understood it - heck, even I did!  I was asked to re-write it in Excel because of the "difficulties other engineers might have in understanding this non-standard tool".   So I dutifully rewrote it in Excel, obscuring half of what it was doing, and having to write another bit of paper saying how it worked and summarising the results.  

I've seen Excel sheets used to determine choice of integrators for simulation engine ... and get it wildly wrong because the copy-paste errors were hard to see and nobody bothered to do a reality-check on the results. 

Another colleague asked me for some help on a particularly interesting differential equation.  I solved his problem partly using Mathcad's symbolic processor and partly using the numerical integrators ... took me 20 minutes or so to produce a near release-ready document.   "Management" wouldn't buy him a copy of Mathcad (wasn't budgeted for) and insisted that he spend 3 to 4 weeks converting my solution to Excel (hand derivation of the integrals, design paper, expected test results, reviews, implementation, review, test, rework,review)... and then complained about the delay and cost.

(None of the above happened in my current employment, BTW).

I always thought it was a shame that Mathsoft divested itself of S-Plus (I believe R is a variant of the S Programming Language??); there was a lot from S-Plus that could have been incorporated into Mathcad.

Stuart

... And, yes, I've had to use to Excel for database work (although I have dug in my heels a couple of times and insisted on using Access).

[dschenken]

Part of what you refer to could be fixed if PTC produced a Mathcad freely-distributable runtime to allow sheets to run on their own or the ability to compile them into standalone applications. The Mathcad Gateway is OK, but a lot of effort for limited usage work.

One study I came across a while ago is What We Know About Spreadsheet Errors

Most of the failures are because all of this is software development, being done by people who aren't trained or of a temperament to be software developers. I see the same in Creo modeling pretty frequently.

[VladimirN]

"R (programming language)": R (programming language) - Wikipedia, the free encyclopedia

"R: Past and Future History":

R : Past and Future History

https://www.stat.auckland.ac.nz/~ihaka/downloads/Interface98.pdf

[StuartBruff]

VladimirN. wrote:

"R (programming language)": R (programming language) - Wikipedia, the free encyclopedia

"R: Past and Future History":

R : Past and Future History

https://www.stat.auckland.ac.nz/~ihaka/downloads/Interface98.pdf

Interesting. So, it started off being a Scheme-like equivalent to S and then drifted nearer to becoming a more S-like language.

The first example caught my eye ...

Stuart

I've got a rough outline (somewhere) of a worksheet I was drafting on how functions are (almost) first-class objects in Mathcad and how to do functional programming ... if I ever get round to replacing my PC (rather than using a Mac), I might dig it out, as I might have an additional approach to this problem in there.

[AlvaroDíaz]

Hi Stuart.

Your counter, improved.

First, with a restart option, useful for using inside some other routine, to ensure that the counter don't carry up previous calls of the main caller.

Second, the don't count option, for skip some values inside other program calling.

Edited: Third, multiple independent counters added.

Best regards.

Alvaro.