Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An alarming number of scientific papers contain Excel errors

washingtonpost.com

Post reply on HN
Print view View original

51–60 of 74 posts

Re: An alarming number of scientific papers contain Excel errors

#51

Post by sghi »

ramblenode wrote: post #3

MS Excel is absolutely unfit for most scientific and engineering problems. The spreadsheet GUI, lack of good version tracking/history, and eagerness to coerce data types and "correct" values makes it easy to introduce errors that will go unrecognized and propagated through calculations. Unfortunately this story just keeps repeating itself. But all of this is just a secondary concern to Excel's real trouble: it's hist…

I'm an Ecology student, and the last few days there's been a lot of (justified) criticism of Excel. I use R and Python for data analysis, but a lot of what I do is gathering data, inputting into Excel and then exporting this as a CSV for use in R/Python for statistical analysis - are there any other tools that can replace Excel for this step?

Re: An alarming number of scientific papers contain Excel errors

#52

Post by omginternets »

When my Ph.D is finally done (~3 months), I'll post some of the code I've had to work with daily for the past three years.

"Spaghetti" doesn't even begin to describe it. "Ball of yarn under a cat-lady's sofa" comes readily to mind, as does gouging my eyes out and amputating my fingers.

The problem isn't excel. The problem is scientists.

Re: An alarming number of scientific papers contain Excel errors

#53

Post by Thriptic »

omginternets wrote: post #52

When my Ph.D is finally done (~3 months), I'll post some of the code I've had to work with daily for the past three years. "Spaghetti" doesn't even begin to describe it. "Ball of yarn under a cat-lady's sofa" comes readily to mind, as does gouging my eyes out and amputating my fingers. The problem isn't excel. The problem is scientists.

The way I got people in my lab to start complying with some best practices is by linking everything back to bench top work. It would be unacceptable to run in vitro experiments the way people write software in science. You need to run controls (unit tests), you need to write your procedure down in your lab notebook (comments, version control) etc

Re: An alarming number of scientific papers contain Excel errors

#54

Post by wongarsu »

JumpCrisscross wrote: post #35
SNvD7vEJ wrote: post #13

Why is the auto-convert 'features' in Excel not opt-in? When Excel encounters the first cell in a new sheet that it thinks should be auto-converted, why does it not ask if that is desirable for that sheet? Like: "Do you want Excel to interpret and auto-convert all strings with format into the type in this sheet?" At least for conversions where the original data is lost.

I don't think Excel ever destroys the underlying data. It simply layers formatting over it. One can override by specifying custom formatting, or opt out by selecting all cells and setting their format to text.

Once the data is in the cell, changing cell formatting is a nondestructive visual layer. But while inputting data, cell formatting changes the interpretation of the input, which is destructive.

The article describes in some detail how inputting SEPT2 in a cell with default formatting displays 9/2/2016, but is stored as 42615 (which you get if you later change the cell to text formatting).

Re: An alarming number of scientific papers contain Excel errors

#55

Post by wongarsu »

Steeeve wrote: post #9

Is everybody a washington post subscriber? Or have I missed the route around the paywall somehow?

I opened the developer tools and added `style="display:none" to the relevant layer, and unchecked the "overflow:hidden" style. Given that this is HN I suspect many other did the same.

Re: An alarming number of scientific papers contain Excel errors

#56

Post by thearn4 »

omginternets wrote: post #52

When my Ph.D is finally done (~3 months), I'll post some of the code I've had to work with daily for the past three years. "Spaghetti" doesn't even begin to describe it. "Ball of yarn under a cat-lady's sofa" comes readily to mind, as does gouging my eyes out and amputating my fingers. The problem isn't excel. The problem is scientists.

During grad school, I found that MATLAB, in particular, lends itself to spaghettification.

I can think of a lot of reasons why that is, but the one-function-per-file and single flat directory structure of MATLAB programs is part of it.

Language quirks are another, but I could write an entire book about that.

Re: An alarming number of scientific papers contain Excel errors

#57

Post by proofmaster »

ramblenode wrote: post #3

MS Excel is absolutely unfit for most scientific and engineering problems. The spreadsheet GUI, lack of good version tracking/history, and eagerness to coerce data types and "correct" values makes it easy to introduce errors that will go unrecognized and propagated through calculations. Unfortunately this story just keeps repeating itself. But all of this is just a secondary concern to Excel's real trouble: it's hist…

I have to agree, Excel and science just don't go with one another.

Re: An alarming number of scientific papers contain Excel errors

#58

Post by gaius »

Rapzid wrote: post #22
ramblenode wrote: post #11

Earlier quoted context omitted.

I would counter with "use the right tool for the job." Excel is great at churning out fast and dirty estimates for low impact work. The problem is when it's used for large, complex, or important problems because these are just not Excel's domain--something obvious when looking at the kinds of features and bug fixes MS has prioritized over the years.

Excel is used HEAVILY at all levels of business for large, complex, and important things. I'm curious what this right tool is that they should be using instead.

Python with a good DAG library, e.g. https://github.com/manahl/mdf (but there are plenty of others kicking around).

What this lets you do is in Python build networks of computations then change one value and it will cascade through the rest of your model, exactly like you do in Excel, but in actual code that can easily be versioned, reused, etc.

Re: An alarming number of scientific papers contain Excel errors

#60

Post by VodkaHaze »

sghi wrote: post #51
ramblenode wrote: post #3

MS Excel is absolutely unfit for most scientific and engineering problems. The spreadsheet GUI, lack of good version tracking/history, and eagerness to coerce data types and "correct" values makes it easy to introduce errors that will go unrecognized and propagated through calculations. Unfortunately this story just keeps repeating itself. But all of this is just a secondary concern to Excel's real trouble: it's hist…

I'm an Ecology student, and the last few days there's been a lot of (justified) criticism of Excel. I use R and Python for data analysis, but a lot of what I do is gathering data, inputting into Excel and then exporting this as a CSV for use in R/Python for statistical analysis - are there any other tools that can replace Excel for this step?

How do you gather the data? You could learn scraping in python, for example. If you manually input the data, a simple spreadsheet is fine for that.

If you have the data in a non csv format and use excel to transform it into csv, python and R have utilities for that job (python has pandas, I don't use R as much for data cleaning, but I know it can).

Post reply on HN