Author Topic: Biologists rename our own biology to work around Excel "bug"  (Read 5947 times)

0 Members and 1 Guest are viewing this topic.

Offline NiHaoMikeTopic starter

  • Super Contributor
  • ***
  • Posts: 8951
  • Country: us
  • "Don't turn it on - Take it apart!"
    • Facebook Page
Cryptocurrency has taught me to love math and at the same time be baffled by it.

Cryptocurrency lesson 0: Altcoins and Bitcoin are not the same thing.
 
The following users thanked this post: MMMarco

Online SiliconWizard

  • Super Contributor
  • ***
  • Posts: 14230
  • Country: fr
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #1 on: May 25, 2023, 04:43:03 am »
Good one! :-DD
 

Offline Infraviolet

  • Super Contributor
  • ***
  • Posts: 1001
  • Country: gb
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #2 on: May 25, 2023, 07:00:17 pm »
They call themelves "scientists" and yet inist on using M$ excel ! /s

Seriously though, you'd think they'd try working in things like R or Python which give one a more straightforward way to define operations to be performed on inputted data, rather than clicking around in spreadsheet menus and fighting with defaults which cause these sort of bugs. I've known far too many academics who tried to use excel for almost everything.

 

Online SiliconWizard

  • Super Contributor
  • ***
  • Posts: 14230
  • Country: fr
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #3 on: May 25, 2023, 07:31:52 pm »
They call themelves "scientists" and yet inist on using M$ excel ! /s

Seriously though, you'd think they'd try working in things like R or Python which give one a more straightforward way to define operations to be performed on inputted data, rather than clicking around in spreadsheet menus and fighting with defaults which cause these sort of bugs. I've known far too many academics who tried to use excel for almost everything.

Agreed. (Or Julia, or...)

Then there is Powerpoint too, which is kind of a cancer.
https://www.anti-powerpoint-party.com/

 

Offline aeberbach

  • Regular Contributor
  • *
  • Posts: 185
  • Country: au
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #4 on: May 25, 2023, 08:30:38 pm »
They call themelves "scientists" and yet inist on using M$ excel ! /s

The vast majority do.
Software guy studying B.Eng.
 

Offline xrunner

  • Super Contributor
  • ***
  • Posts: 7485
  • Country: us
  • hp>Agilent>Keysight>???
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #5 on: May 25, 2023, 09:23:28 pm »
Simple to turn off in Open Office -

Click “Format Cells…” Click on the tab “Numbers” Under the category select box click “Text” Click “Ok”. Your text should no longer be interpreted as date/decimal/currency etc for the selected cells.


Just tried it, it leaves these types of inputs as they are:

OCT4
MARCH1

etc ...
I told my friends I could teach them to be funny, but they all just laughed at me.
 

Online newbrain

  • Super Contributor
  • ***
  • Posts: 1706
  • Country: se
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #6 on: May 26, 2023, 08:31:08 am »
Simple to turn off in Open Office -
Exactly as in MS Excel.
And one can turn off datatype detection when importing CSV.

Sloppy usage of available imperfect tools.
Nandemo wa shiranai wa yo, shitteru koto dake.
 

Online SiliconWizard

  • Super Contributor
  • ***
  • Posts: 14230
  • Country: fr
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #7 on: May 26, 2023, 07:31:46 pm »
Well, using Excel (or any basic spreadsheet) for this kind of task would be questionable enough as Infraviolet said, but not even knowing how to change the format of cells and leaving it to default is mind-boggling.
Fortunately, ChatGPT will solve it all. :-DD
 

Offline Infraviolet

  • Super Contributor
  • ***
  • Posts: 1001
  • Country: gb
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #8 on: May 26, 2023, 08:07:06 pm »
"not even knowing how to change the format of cells"
And that's part of the reason why the likes of Excel are so unsuitable, you have to know them intimately, or hunt around in multiple menus, to turn unwanted "features" off. Handling these sot of things with actual programming languages ensures you only get the features you ask for where you specifically call them.
 

Online SiliconWizard

  • Super Contributor
  • ***
  • Posts: 14230
  • Country: fr
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #9 on: May 26, 2023, 08:55:09 pm »
Uh? Intimately? It's just a right click away. :-DD

While there are much better tools than spreadsheets for scientific data analysis (and people doing scientific research should master at least one of those), there is absolutely no excuse there. This is nothing intimate. Setting the format of cells is one of the most basic tasks in any spreadsheet even before knowing how to use formulas, even kids know how to do this these days.
 

Online The Soulman

  • Frequent Contributor
  • **
  • Posts: 948
  • Country: nl
  • The sky is the limit!
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #10 on: May 26, 2023, 10:06:04 pm »
It's just a right click away. :-DD

Have you tried?
 

Offline Infraviolet

  • Super Contributor
  • ***
  • Posts: 1001
  • Country: gb
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #11 on: May 28, 2023, 03:39:36 pm »
"Intimately", I'll admit I dont know much about excel's interface any more, I forgot it all when I learnt to program, especially once I'd found R/RStudio could serve for most of my data handlng needs... But I do remember it was getting ever worse and more confusing every time M$ released a new version with a more graphically stylish (but ever less obvious) interface.

I don't understand why anyone, especially scientsists who surely already know some amount of programming, would put themselves through the torture of Excel for these sort of things.
 

Offline Whales

  • Super Contributor
  • ***
  • Posts: 1898
  • Country: au
    • Halestrom
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #12 on: May 28, 2023, 03:55:55 pm »
Without first using excel I would have never had what is needed to understand other languages like R or concepts like arrays.

Excel and spreadsheet software are a standard: they're full of warts, but the compromises mean everyone can play rather than just a select few.  There are many times I wanted to use something more advanced, but I couldn't because I knew the software would not be something my recipients and co-workers could get working. 

Online tszaboo

  • Super Contributor
  • ***
  • Posts: 7282
  • Country: nl
  • Current job: ATEX product design
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #13 on: May 28, 2023, 04:36:05 pm »
Simple to turn off in Open Office -

Click “Format Cells…” Click on the tab “Numbers” Under the category select box click “Text” Click “Ok”. Your text should no longer be interpreted as date/decimal/currency etc for the selected cells.


Just tried it, it leaves these types of inputs as they are:

OCT4
MARCH1

etc ...
Not even that much. Just put an ' in front of it. ' tells excel to leave it alone.
 

Offline IanB

  • Super Contributor
  • ***
  • Posts: 11771
  • Country: us
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #14 on: May 28, 2023, 04:55:50 pm »
Simple to turn off in Open Office -
Exactly as in MS Excel.

According to Microsoft, you cannot turn it off in Excel. This is what I would call a bug, and what Microsoft calls "do what we want, not what you want".

Quote
Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

https://support.microsoft.com/en-us/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8
 

Online Zero999

  • Super Contributor
  • ***
  • Posts: 19285
  • Country: gb
  • 0999
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #15 on: May 28, 2023, 05:00:05 pm »
Many scientists used Gnumeric for a long time because it supported larger spreadsheets than Excel.

I'm surprised more scientists haven't dumped Excel. I suppose the problem is it's difficult to transfer some features, such as scripting, but most programming languages can work with data from spreadsheets, even though it's often less convenient.
 
The following users thanked this post: SeanB

Online coppercone2

  • Super Contributor
  • ***
  • Posts: 9171
  • Country: us
  • $
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #16 on: May 28, 2023, 05:06:46 pm »
I think its stupid for microsoft not to fix this because it makes big corporations that like hiring the best people for the job (not the people with programming skills) think twice about using their products.

that is a blessing for some crap that started out in accounting made it over to molecular biology gene splicing shit and they are being really neglectful
 

Offline IanB

  • Super Contributor
  • ***
  • Posts: 11771
  • Country: us
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #17 on: May 28, 2023, 05:11:52 pm »
The appeal of spreadsheets is that they have a visual, parallel UI, which matches the naturally parallel perception of the human brain, and that they provide a no code or low code solution.

Programming languages like R or Python force you to do more work to use them, they force you to write code (even if high level and abstract), and they force your brain to work in a code-oriented manner.

I am more than capable of using programming tools, but I will always go first to a spreadsheet for simple computational tasks.
 

Offline Infraviolet

  • Super Contributor
  • ***
  • Posts: 1001
  • Country: gb
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #18 on: May 28, 2023, 11:45:17 pm »
Personally I've always found programming easier than spreadsheets, ever since I learnt programming that is. Is this unusual? Writing a series of commands and defining variables always feels much more like the algebraic underlying operations than clicking around in a spreadsheet does, and whilst people are (regrettably, as it was with my case) often taught spreadsheets before encountering programming languages, they are taught the basic mechanics of what a programmed series of lines does (the whole concept of maths, and of following recipes) earlier than they encounter spreadsheets. And I struggle to believe anyone "at the coal face"* in biology thesedays wouldn't have been taught a programming language during their degree or early when starting work in the field.

As for "more work" in the likes of R or python, when you're doing one of the common data handling tasks you can always resort to the "copy it from stackoverflow" answr and modify it accordingly to specifics of your individual use case, still better than Excel. And as for collaborating with people who can't program, they can still run a programmed script you give them, and can probably do so with greater confidence than they can try to fiddle in a spreadsheet. Program code in R or Python is all there on show, trying to reverse engineer a spreadshet attempt to handle data is a much more miserable task.

*sure, in biology the older professors might not have learnt programming, but they're also not the ones who'd be handling these demon spreadsheets, they're sitting back having high concept ideas not slogging away at the data directly
« Last Edit: May 28, 2023, 11:50:36 pm by Infraviolet »
 

Offline Someone

  • Super Contributor
  • ***
  • Posts: 4493
  • Country: au
    • send complaints here
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #19 on: May 29, 2023, 03:50:59 am »
as for collaborating with people who can't program, they can still run a programmed script you give them, and can probably do so with greater confidence than they can try to fiddle in a spreadsheet. Program code in R or Python is all there on show, trying to reverse engineer a spreadshet attempt to handle data is a much more miserable task.
How do you propose that end user install the appropriate environment and learn how to execute the code?

The point of excel is that within an organisation it is usually forcibly installed on every computer, so zero friction to share the data/analysis/tool. That then places an expectation on collaborators that they also have that (possibly same version) installed and so the Microsoft virus continues. Open formats were supposed to eliminate that but just try asking for an open format from those people who don't know anything beyond word/excel/power point.
 

Offline NiHaoMikeTopic starter

  • Super Contributor
  • ***
  • Posts: 8951
  • Country: us
  • "Don't turn it on - Take it apart!"
    • Facebook Page
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #20 on: May 30, 2023, 04:23:29 am »
Many scientists used Gnumeric for a long time because it supported larger spreadsheets than Excel.
When I had to plot data at work far too large for Excel to handle, I used Veusz. Best thing was that I could set up templates for various incoming data formats and have the graphs done very quickly after importing the data.
Cryptocurrency has taught me to love math and at the same time be baffled by it.

Cryptocurrency lesson 0: Altcoins and Bitcoin are not the same thing.
 

Online Halcyon

  • Global Moderator
  • *****
  • Posts: 5614
  • Country: au
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #21 on: June 05, 2023, 01:01:42 pm »
Most scientists and medical researchers don't use Excel. There are far better tools for that, especially when dealing with large datasets.

Even the latest version of Excel has several hard limits, one of which I've encountered when examining log files. Forget more than 1,048,576 rows of information, Excel says no.
 

Online Zero999

  • Super Contributor
  • ***
  • Posts: 19285
  • Country: gb
  • 0999
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #22 on: June 05, 2023, 05:03:11 pm »
Gnumeric can handle 224 rows.
 

Offline IanB

  • Super Contributor
  • ***
  • Posts: 11771
  • Country: us
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #23 on: June 05, 2023, 07:10:23 pm »
Most scientists and medical researchers don't use Excel.

I don't think I could agree with that as a blanket statement. Some don't use Excel, some do. Not all datasets are huge, and sometimes Excel might the the most convenient tool for a job. Almost every university I have seen has their students using Microsoft Office as the standard for reports and other work. When I submit papers for meetings or conferences, the publisher usually provides a Word template to conform to.
 
The following users thanked this post: Someone

Offline Infraviolet

  • Super Contributor
  • ***
  • Posts: 1001
  • Country: gb
Re: Biologists rename our own biology to work around Excel "bug"
« Reply #24 on: June 06, 2023, 04:07:15 pm »
Usually academic conferences/journals use a LaTeX template, Word templates are more the thing for business/governmental documents.

Despite being in LaTeX though one so often sees papers where the graphs were clearly done in Excel before being loaded as images. Halcyon is optimistic, researchers shouldn't use Excel but far too many do, and use it for things it really wasn't designed for (the only use of spreadsheets is maintaining lists, as soon as you want to process data you need programming lanugages if you are to do it sensibly).
 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf