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

61–70 of 74 posts

Re: An alarming number of scientific papers contain Excel errors

#61

Post by MichaelGG »

dagaci wrote: post #12

First I thought this story was about math and numerical errors. But it's actually about auto- formatting and auto-correction. "Excel automatically converting gene names to things like calendar dates or random numbers" In this case, I think what is needed is some kind of rudimentary knowledge of data-types. Or perhaps more simply a scientific template which is actually plain text by default. But how are people not not…

I've found it hard to get Excel to turn off the coercions. In telecom tons of inter-company data is exchanged in Excel or CSV files. And very often numbers show up in scientific notation. Or timestamps (10.2 minutes) get coerced into dates. It's infuriating. Sometimes the manual formatting to no format works and sometimes it doesn't. I feel dumb admitting that I can't seem to figure something so basic out.

Excel should really offer an easy way around this.

Re: An alarming number of scientific papers contain Excel errors

#62

Post by kaybe »

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…

Using unfit programs is a hard problem. Remember this thread?

- Ask HN: Suggestions for spacewalk procedure writing?

https://news.ycombinator.com/item?id=5585535

(It is asking for alternatives to Word for EVA planning)

Re: An alarming number of scientific papers contain Excel errors

#64

Post by IndianAstronaut »

madaxe_again wrote: post #23
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…

At university, we were forbidden from using Excel, and only FORTRAN 77 was allowed. At the time, this seemed strange and anachronistic, but when you consider that it had been in use for 25 years at the time, and was a known quantity that didn't have strange and unknown bugs, it actually seems sensible in retrospect. I do however recall being mightily annoyed that I was having to calculate ephemera in "arcane bullshit…

A company I worked for banned Excel for analysis and made everyone use Ipython notebooks because the analysis was transparent and very easily repeatable.

Re: An alarming number of scientific papers contain Excel errors

#65

Post by omginternets »

thearn4 wrote: post #56
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.

>I found that MATLAB, in particular, lends itself to spaghettification.

How did you guess? ;)

I've been gently pointing people towards python for this exact reason. The younger generations need little convincing, but the old dogs would rather write the same shitty code.

I suppose change takes time.

Re: An alarming number of scientific papers contain Excel errors

#66

Post by thearn4 »

omginternets wrote: post #65
thearn4 wrote: post #56

Earlier quoted context omitted.

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.

>I found that MATLAB, in particular, lends itself to spaghettification. How did you guess? ;) I've been gently pointing people towards python for this exact reason. The younger generations need little convincing, but the old dogs would rather write the same shitty code. I suppose change takes time.

Python has definitely been a much better way forward for my work!

Re: An alarming number of scientific papers contain Excel errors

#67

Post by Steeeve »

sccxy wrote: post #18
Steeeve wrote: post #9

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

There is 'web' link under submission. 32 points by pns 6 hours ago | flag | hide | past | --> web https://www.google.com/search?q=An%20alarming%20number%20of%... Google Search usually gets around paywalls

That usually works for me (frequently getting me to a different AP news site), but not with this particular article. I have access to it now, but I thought I'd pass on the FYI.

Re: An alarming number of scientific papers contain Excel errors

#68

Post by sghi »

VodkaHaze wrote: post #60
sghi wrote: post #51

Earlier quoted context omitted.

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).

Yeah, it's a lot of manual entering of data (eg finding abundance of certain species of lichens, and lat/long, then manually entering all data and exporting this as a csv either for heatmaps with GIS or for more stats analysis and the same with animal species abundance) so it's good to know I'm not missing something fairly obvious!

Re: An alarming number of scientific papers contain Excel errors

#69

Post by brodie78382 »

MichaelGG wrote: post #61
dagaci wrote: post #12

First I thought this story was about math and numerical errors. But it's actually about auto- formatting and auto-correction. "Excel automatically converting gene names to things like calendar dates or random numbers" In this case, I think what is needed is some kind of rudimentary knowledge of data-types. Or perhaps more simply a scientific template which is actually plain text by default. But how are people not not…

I've found it hard to get Excel to turn off the coercions. In telecom tons of inter-company data is exchanged in Excel or CSV files. And very often numbers show up in scientific notation. Or timestamps (10.2 minutes) get coerced into dates. It's infuriating. Sometimes the manual formatting to no format works and sometimes it doesn't. I feel dumb admitting that I can't seem to figure something so basic out. Excel shou…

My experience is the same, also in telecom/networking. One of our vendors' serial numbers, when entered into Excel, get converted to something else entirely (not sure what exactly), and setting the fields to "text" or "numbers" doesn't seem to do much, consistently.

Re: An alarming number of scientific papers contain Excel errors

#70

Post by KMag »

cm2187 wrote: post #7
krapht wrote: post #5

Earlier quoted context omitted.

I'll just quote Soustrup here: "There are only two kinds of languages: the ones people complain about and the ones nobody uses." In particular you see the spreadsheet GUI as a downfall, but I think it is a great enabler that has allowed millions of non-programmers to create useful programs.

In fact a lot could be said of the millions of ways to shoot oneself in the foot with programming languages. Excel being visual (the formula is evaluated/tested immediatly after being entered), I'd argue a diligent user will make less mistakes with Excel than while coding.

> Excel being visual (the formula is evaluated/tested immediatly after being entered)

We generally call this "interactive" and not "visual", in order to avoid confusion with graphical programming languages.

I agree that being interactive probably helps rapid iteration, debugging, and experimentation. However, Excel could still be interactive without being so fast and loose with types, accurate display of numbers, and many of the other faults others have pointed out.

Also, if you're trying to make some subtle point about the reduced severity of mistakes, you mean "lesser" rather than "less", otherwise you mean "fewer". https://duckduckgo.com/?q=less+vs+fewer

Post reply on HN