An alarming number of scientific papers contain Excel errors
washingtonpost.com
washingtonpost.com
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 history of incorrectly implementing numerical and statistical procedures. One could plumb the depths of this topic for hours, but here are a few highlights: regression formula accepts illegal/nonsensical inputs (e.g. collinear predictors) and gives illegal/nonsensical outputs [0], variance/standard deviation change incorrectly with sample size [0], output of a paired t-test changes when missing values are included [0], formulas are mislabeled [0], v. 2007 gives very wrong answers to 11 of 27 tests in the NIST test suite used for statistical software benchmarks [1], the random number generator was broken as late as v. 2007 [1], and calculations relying on any of 12 particular floats display an incorrect result [2]. There are plenty of other issues mentioned in the links and elsewhere; if you're interested you'll have no trouble finding them.
Remember, friends don't let friends use Excel for science. :)
[0] http://people.stern.nyu.edu/jsimonof/classes/1305/pdf/excelr...
[1] http://www.pages.drexel.edu/~bdm25/excel2007.pdf
[2] https://blogs.office.com/2007/09/25/calculation-issue-update...
Edit: clarify and add a new issue I became aware of while researching further.
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.
Based on the evolution of Excel, they clearly see it as a data analysis tool only, with lots of iterations of the pivot tables functionality. And clearly not as a modeling tool.
My guess on Excel usage is that it is 10% of the time used for data analysis (pivot tables, time series analysis), 20% of the time used to create a simple table (planning for the week by non technical people), and 70% of the time used for modelling (business plan, tax calculations, accounting, pricing financial instruments, running an inventory, calculating something scientific/mathematic, etc). Most companies are run on Excel.
For instance one thing where Excel sucks at one of its core functionalities is linking a powerpoint presentation to an Excel model. Any consultant, expert, banker, accountant, salesperson or marketer will do that all day. You can paste a table as a linked image but the formatting will be completely unstable and it is very dodgy. And no way to link a number in a textbox to Excel. Microsoft should focus on these problems rather than yet another pivot table functionality.
Though office seems to be frozen time. I can't think of any major new feature since Office 2007.
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.
Yes, it's true Excel is used for a lot of big important things, but I'd agree with GP it's not very good at them. Moreover, I think most people who use Excel for big important things, would agree it's not good for it--they get stuck in that situation when a small spreadsheet grows big over the years. Lack of version control, limited modularity, and no ability to unit test make large spreadsheets error-prone, and Excel tends to barf on you when you push it too hard (seems to be a number of crash bugs and performance bugs that never get fixed).
Excel is incredibly productive for quick analysis and making charts. But it's really not a development platform and it's really not for heavy lifting.
The second big reason is that Excel just works, and all programming languages usually require installing tons of bullshit - editors, toolchains, whatever - to be able to work somewhat conveniently in them, and the moment any link in the bullshit chain crashes, you're SOL if you're not a developer. Yeah, we sometimes underestimate just how much minutiae knowledge we have that allows us to fix random problems in our tools without even thinking for a second about it.
So maybe Excel is unfit for purpose, but it's still the most fit for purpose tool available.
I think that Kenny Tilton's Cells was an attempt to get that sort of interactive update functionality in Lisp. I was never able to figure it out though.
--If you want to re-use a formula, you basically have to copy-paste it. Generally there isn't much modularity. Programmers are well aware of the dangers of this.
--It's also not a friendly platform for writing regression tests (possible with VB, but who does that?).
--The two-dimensional nature of the spreadsheets and lack of loops means you often need to do hacky stuff to emulate multidimensional arrays or loops. Easy to make mistakes here.
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
The question is if for most of the use cases the margin of error is just fine...
Most engineers are more similar to business people in that regards, they see traditional programming languages as too complicated. We learned MATLAB in university, two classes, basic intro to programming and then numerical methods. Many people had to retake the first class at least once.
I'd love to bang out my calculations in IPython Notebooks, but the most important requirement of engineering calculations is that they can be documented and understood by peers. Since none of my peers are interested in learning python, it's useless. Excel is the lowest common denominator, everyone gets it.
I once read this interesting contrast on HN and it stuck with me:
"Traditional programming languages show the program but hide the data. Spreadsheets hide the program but show the data".
I do however recall being mightily annoyed that I was having to calculate ephemera in "arcane bullshit", so I can understand why many shortcut to excel.
Experience makes fools of our past selves.
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).
- Ask HN: Suggestions for spacewalk procedure writing?
https://news.ycombinator.com/item?id=5585535
(It is asking for alternatives to Word for EVA planning)
"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 noticing auto-correction and auto formatting taking place!
The only perfect solution is to hire a developer to build you a data entry system. The developer can build the system which they have no cause to entirely understand the science behind, and thus a human to take the blame for errors instead of excel.
Excel should really offer an easy way around this.
The SEPT9 gene is problematic enough to be memorable, though. https://en.wikipedia.org/wiki/SEPT9
Type apostrophe at beginning of the gene name ('MARCH1) or format the column for gene names as text (click column letter, then Format | Cell and select text)
If people want to use a spreadsheet application for this kind of data collection (and that is a big if I think) then they perhaps need to have some agreed lab protocols for setting up and checking the spreadsheets. This is a known issue in financial circles...
"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.
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.
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.
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 <X> into the type <Y> in this sheet?"
At least for conversions where the original data is lost.
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).
https://www.youtube.com/watch?v=2Cdgew5zvI4
http://www.felienne.com/archives/tag/spreadsheets
[0] pronounced Fay-lee-nuh
http://www.bloomberg.com/news/articles/2013-04-18/faq-reinha...
If there aren't enough resources / skilled eyes to catch these simple errors, what are the chances they would catch errors in source code too?
(OT now:) If anyone thinks that's cheating them out of money - the choices are not "read for free" and "read for the appropriate price", the choices are "read for free" or "don't read". The reason I read is to procrastinate, so the value I get out of it is actually negative (same with HN...). Even their occasionally excellent articles (like their series on asset forfeiture) is stuff I'm at most mildly interested in (as a foreigner) to distract myself. That's my issue with today's media, I don't actually feel "informed" as in "it this is good for my life that I know these things". I can't do anything about 99.99% of the stuff I read about anyway, nor is it a representative sample of reality but consists almost solely on reporting the outliers.
Entertainment has value doesn't it? It's not a simple financial value like the simplistic use of opportunity cost as being the price you could bill those hours at, but it's a useful and functional part of being human.
I don't have a problem with you reading something people broadcast to the public internet though.
> Entertainment has value doesn't it?
If I delay my work (which I do, even now) the overall value of writing comments on HN or reading a WP article that talks about issues that don't directly concern me and that I cannot do anything about is negative, even to myself (and don't try to argue they may concern me indirectly because, well, everything does).
It's like being addicted to drugs: Sure you can argue if the drugs (and let's assume those especially crazy and destructive ones) had no value to the person taking them they would not take them, but a more appropriate model than high school economics would be the neuroscience of addiction. But even if you decide to stick to using an economic model you would have to take a very narrow view - like picking exactly the period where a stock was rising to show how great a pick that company is - to argue the person gets a positive value from taking those drugs.
Disagree. This conversation has a value, I [likely] can't derive anything financial from it, one might term it "entertaining" even [that wasn't supposed to sound quite so denigrating!]. The value is difficult to define, but it doesn't remove value from my life IMO. It possibly takes some time with which I can argue for opportunity cost, but I see the conversation as a generally positive thing.
I'm not sure if a mere conversation can be equated so easily to the value positions involved in drug addiction. However, I would say that it's a mixed bag. Some aspects that come out of drug addiction can have positive value - I'm thinking the progression of the arts: some great works of literature, paintings, dramatic performances, appear to have at least some relationship to the artists drug use [and in some cases addiction, it's hard to know where the divide is].
>"This is no more true than to say that Van Gogh was only Van Gogh because of his inner turmoil or than Jean-Michel Basquiat needed heroin to draw or paint. But it is also worth remembering that it killed them both." (http://www.worldcrunch.com/culture-society/under-the-influen...)
Similar ground with a greater focus on musicians - http://blogs.scientificamerican.com/mind-guest-blog/creativi....
> This conversation has a value
>I covered that!!! Do you actually READ the comments you respond to? //
Right back at your there - it has a value because I value it. Might seem a bit too self-referential but that's how value works.
>How do YOU know what I'm doing and what my time is worth? //
I don't. There's extrinsic and intrinsic values for sure - if you're chatting inanely to me on HN when you would normally be performing successful heart surgery on people who want to live longer then the opportunity cost [in terms of life enrichment for the people who would have been saved] is high, for sure, but that doesn't mean the intrinsic value is negative.
You appear to be arguing that because there is a potential for foregoing financial gain through having a conversation that the _value_ of the conversation -- the ability of it to enrich, educate, improve, entertain, etc. -- is negative. The true value can't be counted, you don't know it's effect on me and I don't know the effect on you (or others who are reading). Maybe an onlooker has read something in the conversation and that's inspired their PhD thesis on the teleology of communication.
IMO you appear to too readily decry the measurable negative aspect - potential for foregoing financial gain (opportunity cost) - whilst you under-estimate the potential for positive improvement, extrinsic value, and the like.
[FWIW Currently I'm suffering with mental health problems and this conversation has actually made me realise that I can be positive. I'm not saying this to try and shoe-horn in an extrinsic value, that's a genuine self-reflection.]
Happy to hear any further responses if you can steer away from declarations of "ridiculous!" and "annoying!" and illucidate why you feel it's ridiculous, how it conflicts with your value judgement in expanded terms?
32 points by pns 6 hours ago | flag | hide | past | --> web <-- | 17 comments | favorite
https://www.google.com/search?q=An%20alarming%20number%20of%...
Google Search usually gets around paywalls
Disclaimers: 1) Yes, scientific peer review needs improvement. 2) Yes, spreadsheets are not ideal for science... what makes business less important?