People shouldn't do important work in Excel. If it is important, people should be involved who have invested the time in learning something more powerful. Indeed, we could ask they aspire all the way to good practice and store their data in a database and their code in git. But there needs to be a process to verify model correctness no matter what tool is being used and bugs will exist in R as well as in Excel.
Calling Excel easily inspectable is laughably wrong imho. Just the opposite.
Sounds more like an issue with the skill of average excel users than a feature gap.
To me, R seems more easily inspectable, as all the logic of a program is visible just by looking at text files, where in Excel it's hidden "under the surface", you have to click on cells, look at what's there, go click on other cells that relate to it, remember what you were looking at in the first one that's now invisible, etc.
For anything very complicated though, I'd prefer R, as Excel eventually gets unwieldy. Although, I'm saying that from the perspective of being a reasonably experienced coder. The majority of the population should just use Excel, especially in a work context in non-technical teams. No matter how much you push for R, other people in the team aren't going to see the value and aren't going to go along with it, and your R code will be useless after you've left.
R has a beautiful functional ability based on S-expressions, allowing some clever stuff to be done (i.e. tidyverse), incredibly fast (data.table is faster than Python, Julia, Matlab etc).
And as a side note, I believe it's moving down the rankings in the h2o benchmarks [1].
Hold on a second while I go shut down the global economy for two years so we can teach everyone finance person how to program.
"The 7 Biggest Excel Mistakes of All Time"
https://www.teampay.co/insights/biggest-excel-mistakes-of-al...
"The financial fails and business risks of spreadsheets"
https://www.webexpenses.com/2020/10/financial-fails-business...
"Nightmare on spreadsheet: take Excel use seriously"
https://www.icaew.com/insights/viewpoints-on-the-news/2020/o...
"Excel – The Dirty Secret"
https://tax.thomsonreuters.co.uk/blog/excel-the-dirty-secret...
"8 Challenges When Using Excel For Accounting"
https://www.senacea.co.uk/post/excel-for-accounting-challeng...
https://theconversation.com/economists-an-excel-error-and-th...
Not that this is Excel's fault, but researchers should definitely either seriously learn how to use computer stuff, or just don't.
A person can't drive on the highway without a license, the same rigor should be applied here, especially in academic circles.
Regarding the graphing ability , R may have more power but plotting the graphs in Excel is so much more WYSIWYG.
Excel is the closest we’ve come as an industry to building a tool that enables non-programmers to program. If it disappeared tomorrow, as some arrogant posters apparently wish it would, tremendous value would be destroyed. Not just in terms of existing workflows but in terms of new workflows that would not be done in R but instead would be done by hand or not at all.
The problem with "things that look like dates being interpreted as dates" comes from not specifying that a column has type "text".
Happened to me multiple times, even when i set each cell as a text.
Seems like copypasting a tab separated values resets the cells to their default state.
Just want to know to avoid future gotchas.
https://docs.microsoft.com/en-us/office/troubleshoot/excel/f...
"Align numerical precision Excel 2013 and R"
https://stackoverflow.com/questions/39531655/align-numerical...
"Numeric precision in Microsoft Excel"
https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft...
IEEE 754 just has unintuitive properties.
Kahan (the "father of IEEE 754") has a rant (among many others) about Excel as well, and how it tries to hide some of the floating point complexities more or less successfully:
Floating-Point Arithmetic Besieged by “Business Decisions”
https://carolomeetsbarolo.wordpress.com/2012/07/20/catastrop...
Or:
"OOPS XL Did It Again"
https://carolomeetsbarolo.wordpress.com/2014/06/22/oops-xl-d...
From the Wikipedia article:
"Although Excel can display 30 decimal places, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to five issues: round off,truncation, and binary storage, accumulation of the deviations of the operands in calculations, and worst: cancellation at subtractions resp. 'Catastrophic cancellation' at subtraction of values with similar magnitude."
julia> 1e20 + 1000 - 1e20
0.0
julia> 1e20 + 10000 - 1e20
16384.0
Python 3.9.6 (default, Jun 28 2021, 19:24:41)
>>> 1e20 + 1000 - 1e20
0.0
>>> 1e20 + 10000 - 1e20
16384.0I've used perl, python, and R for scientific data for a really long time but have always made a concerted effort to avoid Excel. My reasoning feels the same as when people say Java is the best language because it can be run on any device, which is like saying anal sex is the best sex because you can do it with any animal.
Maybe I'm missing out on a great experience, but the notion has always made me uncomfortable.
Please tell me you have said this to someone in a work meeting. This is hilarious.
Several other software tools also mess up leading 0s including R if used in a naive way without specifying extra options. My previous comment about this: https://news.ycombinator.com/item?id=25017116
Like R, MS Excel can also preserve leading 0s -- if you specify the option on import. (Click on Excel 2019 Data tab and import via "From Text/CSV" button on the ribbon menu and a dialog pops up that provides option "Do not detect data types" (Earlier version of Excel has different verbiage to interpret numbers as text))
Zip codes aren't numbers, they are strings that happen to contain only numeric characters
If you can learn a language, you could constrain type conversions easily once you have for knowledge.
Its not like there aren’t painful gotchas in other tools- it’s an issue if you aren’t aware of them and if they impact your work.
If it’s big, unusually complex, you probably want a DB before analysis.
If it’s repetitive, or advanced modeling/ml: python/r
The problem described in the article isn't an Excel issue. It is an issue of the geneticist failure to learn the basics of how their tools work.
This is easily falsifiable. In a "general" cell, when I enter 0002, it gets changed into 2, not just in display, but in actual content. When I change the cell type to text, it'll still be 2. Only if I enter 0002 after changing the type to text is the content kept.
Similar when I want to have the text 3/17 or SEPT1 in a cell, I have to format it before typing or the data does get altered. If you try setting it to "text" after you typed it, you'll get some number that's not very useful to you.
this is not just a workaround - it's recommended by Microsoft. because you literally cannot turn this functionality off.
how is that not an "Excel issue"?
https://www.theverge.com/2020/8/6/21355674/human-genes-renam... https://stackoverflow.com/questions/165042/stop-excel-from-a...