[rQUOTEr]An alarming number of scientific papers contain Excel errors A surprisingly high number of scientific papers in the field of genetics contain errors introduced by Microsoft Excel, according to an analysis recently published in the journal Genome Biology. A team of Australian researchers analyzed nearly 3,600 genetics papers published in a number of leading scientific journals — like Nature, Science and PLoS One. As is common practice in the field, these papers all came with supplementary files containing lists of genes used in the research. The Australian researchers found that roughly 1 in 5 of these papers included errors in their gene lists that were due to Excel automatically converting gene names to things like calendar dates or random numbers. ...[/rQUOTEr]
That's user error. And it's harmless anyhow, because it's just converting text to date. It's unlikely a text abbreviation for a gene is used in a formula, so seems it would only mess up up labeling. The data columns and formulas would still be accurate. And even if the text abbr for the gene is used somehow in a conditional, the code should catch unknowns, or, again, that's bad code/user error. I'm pretty sure it wouldn't even break a Pivot Table either, as the number replacement would still be a unique key. Same goes with macros that might use the gene name as a Dictionary key.
Also, the article says you can't change the default column type from General to something else, but have to manually change it before entering data. That's not true if the data is imported into Excel. The data import tools allow you to override the General column type to be Text. So that's a really bad error on the user's part if they imported the data into Excel, which we can assume they did vs entered the data manually. LOL Scientists
Doesn't surprise me at all. In my experience, the majority of researchers are incredibly inefficient when it comes to excel.
Nick Van Exel was quoted again today after nearly a decade of bitterness toward the spreadsheet software, "See, I told u."
I've worked for companies (and have worked with people) that default to using Excel as a database/analytical-tool/scratchpad. Get a database for storing data. Get an analytical tool for running analysis. Essentially, stop being cheap and get a real program, and avoid Excel as much as possible. Please, don't embrace it is the end-all-be-all tool some people treat it as. I just thought this would be a fun opportunity for people to bash Excel. My bad.
Excel is great for a quick scratch pad or an quick and easy tool for manipulation, but using it for a database drives me nuts. Its even worse when its used as a collaborative tool.
Excel is most likely used by these researches to share their data in a single file. Not as a primary research tool or collaboration tool, but just to share their results. No other database/analytics tool is as shareable in that way as Excel. I'm guessing they do in fact have an external database and a sophisticated data analysis tool designed, marketed and sold directly to gene research scientists. Excel, and BI tools like Tableau, are probably not the primary data analysis tools they use to create the results and charts to put into a research paper. However, when they submit these papers for peer-review, they export some data to a shareable medium ... thus choose Excel for that attachment. And the attachment is probably only the high-level data metrics exported by their other tools...and not the complete dataset. make sense?
Did you understand what I said? Their research tools are probably a highly-sophisticated database/analysis suite of hardware and software sold specifically for gene research. I'm sure their datasets are in the terabytes and beyond. I'm guessing rather than presenting only written papers with printed data tables of results, researchers provide a data file as an attachment, for convenience of peer-review. The article mentions Excel files accompanying research papers, after all. If that's what they are doing, then there really is no reason to use anything other than Excel as the data results attachment to the written paper. Surely, they are not providing proprietary source data, but they would supply the high-level data metrics presented in the papers.
Both Excel and Word drive me nutty with their assumptions on what I want formatting-wise, I rarely use either rather than for quick scratch notes. That said, there is an aspect of know your tool. It does the same thing every time (eh, I might actually argue with myself on some of Word's autoformatting), so it's on the user to use it correctly.
add in sharepoint and it's fun! wait you overwrote my changes! no..i didnt. yes you did. I had it checked out. wait why did it save it with the filters on and zoomed in...? what? who?
That's kind of how it goes. In the end, someone probably does a quick little query and exports into CSV, puts it into Excel for a little more readability, and doesn't double check it. This is the equivalent of having typos in the actual writing.
I'm with heyp on this one -- probably didn't impact research, might be a slight annoyance for peer review and replicating a study. It seems easily correctable though.