398
Microsoft fixes the Excel feature that was wrecking scientific data
(www.theverge.com)
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 21 Oct 2023
398 points (97.8% liked)
Technology
59366 readers
1272 users here now
This is a most excellent place for technology news and articles.
Our Rules
- Follow the lemmy.world rules.
- Only tech related content.
- Be excellent to each another!
- Mod approved content bots can post up to 10 articles per day.
- Threads asking for personal tech support may be deleted.
- Politics threads may be removed.
- No memes allowed as posts, OK to post as comments.
- Only approved bots from the list below, to ask if your bot can be added please contact us.
- Check for duplicates before posting, duplicates may be removed
Approved Bots
founded 1 year ago
MODERATORS
Many scientists are based out of corporations or universities who contract with Microsoft, so Excel would be the default solution for working with spreadsheets.
Also, when it comes to “office” applications, there is no real substitute for Excel. Word processing, presentations, email, notes; there are many open and closed source alternatives that will do the same if not better than MS Office applications. Excel, however, is the exception.
LibreOffice Calc, G-Sheets, Apple’s Numbers, or the myriad of competitor office solutions have never matched Excel for in-depth analyses or overall function. For just basic features, one could limp by with most alternatives, but doing real analytical work within spreadsheets requires Excel.
"Real analytical work" shouldn't be done in spreadsheets at all. You should use a database. Basic spreadsheet features are all you should ever use spreadsheet software to do anyway.
While you will commonly hear that you shouldn’t use Excel as a database, it happens all the time.
Excel is generally more accessible than something like Access or other proprietary database applications, and given that a lot of initial data originally lives in a spreadsheet, it’s the simplest solution that doesn’t require something like SQL coding knowledge to access.
It depends on what you mean when you say “basic”. A spreadsheet with filters or maybe some pivot tables? A spreadsheet connecting to 12 others with refreshes created using VBA code so that end users just need to click a button and see their data? A spreadsheet that connects to a database, runs several queries, and spits out data in an easy to read form? There are folks who consider pivot tables and the use of any code to be “advanced” use of Excel. There are also folks who have made full-on applications with Excel and consider those to be made with only “intermediate” grade knowledge.
I’ve found that the more you know about an application like Excel, the more you realize what you don’t know.
Excel does 1000 different things, and for 998 of them, there's at least one better option.
The two things Excel does best: 1) be accessible to everyone from the greenest high schooler to the most senior IT admin. 2) do those 1000 different things at least somewhat competently.
Exactly. Like personally I’d rather do libreoffice for data entry, spit out a csv, and slap that into an R based analyzer, that’s because I have an irrational hate for excel’s graphs compared to ggplot2. I do use excel a lot though in my job because fuck it it just works for basically everything
“Real analytical work” (I will take that to mean work people actually care about and may even pay good money for), is done with whatever does the job, on the given timeframe, and the analyst, researcher, or team are comfortable with. That may well be Excel. Or not. Depending on the task and people. But your audience will always care more for the appropriateness of your analytical approach for the given audience and task, and of course your results, rather than the tools you used to get there. Of course spreadsheets have limitations and one will do well to know them.
I have already seen data having to be thrown away because the researcher copied and pasted it incorrectly from multiple spreadsheets and no one could tell what the correct data was anymore. No one should be doing this if they are responsibly doing "real analytical work".
As a user you don't always have access to the database. It's much easier to work out of Excel than to find the right person to ask in the corporate hierarchy just for them to say no.
Gnumeric is superior for numerical evaluation.
Also any analysis on scale will use some proper programming language often in C or Fortran since Excel is simply far too slow.
No one does real analytical work with excel... If one is using excel, they are doing basic analytical work that can be done pretty much by every spreadsheet software.
It is just habit. People are used to excel, and are not competent enough to use more advanced tools to do real analytical work. And that's fine. If one is good in a lab doesn't necessarily need be good in data science
"Real analytical work" is the ultimate power-tools-injure scenario with excel, and that's why this article exists.
Programmers using actual databases and crafting custom analysis do not have this problem. There is a time and a place for excel, and this ain't it; leave it to secretaries and people trying to copy data into word documents. I like a pivot table as much as the next guy, but JFC, learn to program, learn git, write in latex, publish science.
I’m as much of an R fangirl as the next lady, but still scientists come from any number of technical skill sets. Hardcore analytics is probably gonna flounder in excel, but if you can’t convince IT to let you have something better you can throw together some chi square test or an anova to get an analysis of your data. And often that will be enough.
Excel just isn't a database (evidenced by the fact that Microsoft also has Access) and it also just isn't a one stop shop for analytics. Having spent 17 years in academia, I'm well aware that people are resistant to learning new things and also aware that sometimes you NEED to.
Sure you can do some line fitting and sensitivity analysis stuff and that is great for preliminary work, but excel is just not the one stop shop people want it to be. PowerPoint is also turing complete, but just because you can doesn't mean you should program with it.
The fact that the month rename problem is killing scientific data is just a smell related to the fact that sometimes you've got to stop and ask yourself "what am I trying to do" and "what tool should I be using to do it".
IMO excel should be left to the MBAs and management: if you are smart enough to do set up analysis of variance or run a t-test or have an intelligent discussion about p values, you SHOULD NOT be dependent on excel.