There have been multiple accounts created with the sole purpose of posting advertisement posts or replies containing unsolicited advertising.

Accounts which solely post advertisements, or persistently post them may be terminated.

cabron_offsets ,

FFS, scientists should know how to handle data, and Excel ain’t it.

Etterra ,

Office Libre is free, and modern MS Office UIs looks like dog dookie. OL can also save in Excel format if you want.

Hey look at that, I found a solution that didn’t require they change their entire process or have to wait for Microsloughed to get their act together.

Moneo ,

Libre calc is one of the worst UXs I have ever had the displeasure of using. I can’t imagine anyone recommending it is using it as their main work application.

Kodemystic ,
@Kodemystic@lemmy.kodemystic.dev avatar

how about fuck MS?

macrocephalic ,

Now if only it would stop dropping leading zeros unless you ask it, and we got rid of the MM/DD/yyyy date format entirely.

theparadox ,

Now if only it would stop dropping leading zeros unless you ask it

That appears to actually be a feature. https://lemmy.world/pictrs/image/fe3a72e7-2422-4b40-9b22-a9dee9023c26.jpeg

macrocephalic ,

Holy shit! Now I just need to talk to some sysadmins and get some group policies set.

theparadox , (edited )

Apparently our typical installer for Visio 2016 and our 365 license use “incompatible installers” so it is going to be a pain in the ass for me to have both installed at the same time. Thankfully I’m trusted by IT so I might be able to just do it myself.

Edit: Looks like I’ll need IT after all. …microsoft.com/…/use-the-office-deployment-tool-t…

Etterra ,

MM/DD/YYYY is the correct format here in America.

EngineerGaming ,
@EngineerGaming@feddit.nl avatar

I think the point was that the format itself is odd. I am European and it’s weird to me: logically it should be either from greatest to smallest, or from smallest to greatest, not a weird in-between.

atzanteol ,

Logically it would be milliseconds since 1970.

macrocephalic ,

Not very good for birthdates.

MonkderZweite ,

20 years after the problem was first reported.

Meaning there’s still hope for XDG support in Firefox?

LaggyKar ,
@LaggyKar@programming.dev avatar

Even before this, you could just set the format of the column as text

CatLikeLemming ,
@CatLikeLemming@lemmy.blahaj.zone avatar

This isn’t a fix. Excel wasn’t meant for this. While I do understand it’s convenient as a database, unless you’re doing something unimportant and small you just really should use something proper. And even now that this “problem” is gone, I am certain there are still more things that cause trouble. You can not satisfy everyone and Excel was just… not made for gene info storage.

Even if you don’t want to use stuff that isn’t Microsoft Office, that comes with Microsoft Access, which is a proper database management system. It’s literally in the same software package, so why do people refuse to use it?

magikmw ,

Convinient arbitrary table software goes brrrr.

zalgotext ,

Why would you need a full blown (shitty) relational database management system to store gene info? Excel should be just fine for storing data in arbitrary tables. It shouldn’t make assumptions about your data by default, and changing values that look like they’re in a specific format should be opt-in, not default behavior.

SirQuackTheDuck ,

It shouldn’t make assumptions about your data by default, and changing values that look like they’re in a specific format should be opt-in, not default behavior

But that’s exactly what made the “auto” data type of Excel such a powerful tool when introduced. If you’re storing text, make the datatype “text”, problem solved.

Nowadays, when making stuff like Excel from scratch, you could opt for a “these look like dates, change the type from ‘none’ to ‘date’?” but with middle management being conditioned on the data type being ‘auto’, that’s something that’s hard to change.

CatLikeLemming ,
@CatLikeLemming@lemmy.blahaj.zone avatar

Honestly, I’d say you shouldn’t do that prompt method. The auto type is genuinely great for the use cases which Excel is supposed to be used for, from someone managing their household finances to charting the growth of a business.

By all means, it absolutely should make assumptions about your data by default, as that’s incredibly convenient for the average user. You can always change the type of a cell afterwards if what you’re doing is special.

schnurrito ,

Optimist: The glass is half full.

Pessimist: The glass is half empty.

Realist: The glass is twice as big as necessary.

Excel: The glass is the 2nd of January.

CatLikeLemming ,
@CatLikeLemming@lemmy.blahaj.zone avatar

That is not what it was made for. It was made to do shenanigans with values like doing math on them and plotting graphs. If you merely want data storage, use a table. I agree, a database is overkill for most things, but that doesn’t change the fact that Excel is the wrong tool for the job. Maybe if they added a table mode where it’s basically just a frontend for a csv it’d work, but right now I’d still say it’s better to use a scalpel than a hammer, even if scissors do the trick just fine.

meowMix2525 ,

Agree. Excel is terribly inefficient if your goal is just storing data.

Hawk ,

Sqlite and duckdb are great, I don’t know about shitty.

You don’t get the visual feedback but the query language, reliability and python interface are all top notch.

mcc ,

Do you really don’t know why, or are you being sarcastic?

CatLikeLemming ,
@CatLikeLemming@lemmy.blahaj.zone avatar

I’ve never used Access personally, so I don’t know if it’s any good or not, I’m just frustrated by people using spreadsheets for data storage.

Evotech ,

It’s been years since I used it tbh. But “access bad” is a meme for a reason

echodot ,

I’m so sick of people using Excel for things it’s not supposed to be used for.

As a general rule if you’re not actually making use of the formula tool, you probably don’t need to be using Excel.

detalferous ,

From the article:

The problem of Excel software (Microsoft Corp., Redmond, WA, USA) inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004 [1]. For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively. Furthermore, RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession ‘2310009E13’ to ‘2.31E+13’). Since that report, we have uncovered further instances where gene symbols were converted to dates in supplementary data of recently published papers (e.g. ‘SEPT2’ converted to ‘2006/09/02’). This suggests that gene name errors continue to be a problem in supplementary files accompanying articles.

Artyom ,

The idea that any scientist is doing data analysis in Excel is honestly terrifying on every level.

kootepe ,

You don’t want to know…

Evotech ,

Excel is excellent at data analysis… Python integrations and everything

Artyom ,

As an alternative, maybe just Python?

filcuk , (edited )

Because every scientist is also a programmer?
Especially if they struggle to use Excel properly, no chance.

cabron_offsets ,

I’d be embarrassed to call myself a scientist if I didn’t know how to at least script basic shit and effortlessly reproduce data analyses. The bar for entry into every single stage of academic science is too fucking low. 95% of this literature is irreproducible shit, in part because fuckwits don’t know how to code. Scientists don’t need to be software engineers, but yes, they need to be able to program. It wasn’t this way 20 years ago, but it most certainly is nowadays.

cabron_offsets ,

Excel sucks open ass. At storing data, at displaying data, at analyzing data. Scientists, of all people, should understand how to use an RDBMS and a data processing framework like R.

Evotech ,

I believe that accessibility is what makes Excel so good. And the world agrees.

UnspecificGravity ,

And is so bad at it that they can’t work around this issue.

Wooshock ,

What the hell else is there? Good luck getting universities using OpenOffice

asdfasdfasdf ,

Scientists should be using programming languages like R or Python. They are both extremely popular in this field, much more than Excel.

Maalus ,

Shoulda coulda. Not everyone is a programmer.

isles ,

Research projects almost exclusively have more than one person working on them.

Hawk ,

Except every scientist and analyst. Stats, data sci and ML is done in R and Python, be it astro, health data or genomics.

If someone has been taught stats in spreadsheet software, they have have been taught wrong, period.

Also, programming is a very strong term. we’re talking about stats in a scripting language, not software development in CPP.

atzanteol ,

They should be if they’re doing data analysis like this.

Maalus ,

If it works, it works. Programming is a tough thing to learn.

atzanteol ,

Programming in R or Python isn’t a lot harder than learning how to get Excel to do what you want. I’d wager it’s easier since you don’t have to fight your tools.

Excel has its place for simple quick calculations. But at some point it’s simply the wrong tool.

griffinsklow ,

I remember when a biologist asked us for help - Excel crashed on processing his 700MB tables. Took some time and Chatgpt to convince him to do the analysis in R. It worked out in the end and he is now recommending this solution to his colleagues, which is nice.

Blackmist ,

Flashback to the time the UK government lost 16,000 positive COVID patients because Excel has a 1 million row limit.

If only there were better ways of storing large amounts of records with a fixed structure. Maybe the future will provide such technology…

cabron_offsets ,

I’ve seen idiots doing “bioinformatics” with excel & vba. FFS.

maniel ,
@maniel@lemmy.ml avatar

What about text selection knowing better what I want to select?

Kethal ,

Microsoft fixes one of the Excel features that wreck scientific data.

JoBo ,

It’s no good having this as part of the user options. It should be a sheet characteristic and the default should be “keep cells exactly as entered regardless of data type”.

kalleboo , (edited )

Changing the default will break the workflows of tens of thousands in the business industry

Scientists should be using something like MATLAB, not Excel.

JoBo ,

They’re not doing their analysis in Excel. MATLAB solves no problems here?

emergencyfood ,

Matlab is used, if at all, by physicists.

We’re talking about molecular biologists.

RheingoldRiver ,

You could make a new filetype, default new versions to it, & not break compatibility. Wouldn't do anything for existing workbooks, and keep xlsx an option, but "it would break compatibility" is not a be-all end-all argument against this.

chepox ,

"Microsoft’s blog adds caveats, such as that Excel avoids the conversion by saving the data as text, which means the data may not work for calculations later. There’s also a known issue where you can’t disable the conversions when running macros. "

This sounds very half assed…

Deebster , (edited )
@Deebster@programming.dev avatar

It’s too late though, scientists already had to rename the genes. Although of course there are other things that can trigger it, not just in science.

ElectroNeutrino ,

How about just not auto-convert everything and keep the integrity of the data unless specifically asked to? Is that so hard?

Chais ,
@Chais@sh.itjust.works avatar

Microsoft assumes their users are complete idiots, even when they (the users) are actively trying to convince them (Microsoft) otherwise. No matter how advanced the feature may be, they’ll assume you found instructions somewhere to do something entirely unrelated and they constantly have to save you from yourself. As a result you constantly have to fight the OS for access and control to get it to do what you want.
If you’re even a bit of a power user that is, of course.

But more often than not Microsoft’s assumption is probably spot on.

WhatAmLemmy ,

That assumption is perfectly good for a default. Not a mandatory feature that power users have to live with.

Chais ,
@Chais@sh.itjust.works avatar

As a default, sure. Should be one that’s easily changed, though. Repeatedly fighting the machine that’s supposed to do your bidding and make your life easier gets old rather quickly. A machine you own and administrate, let’s not forget that.

Black616Angel ,

Excel is inherently flawed in its design.

The thing is, that excel already has half the means of what would be necessary to really fix this bug. That is a field for each cell where the original text can stay.

An excel sheet is just a bunch of XML files zipped in a specific structure. You can unpack a file and look for yourself.
Each worksheet is it’s own file and each cell is subdivided into the value and the formula, that generated this value (or nothing, if there is no formula).
Excel could easily fix this issue by adding another possible cell attribute like “original” or “plain” that, when set, allows you to roll back any conversion.

But no, they go a half assed way as always and screw up even more.

RunningInRVA ,

In order to do that I think they would first have to ratify a standards change to the Excel format, which is open.

Black616Angel ,

Uh, I mean kinda…

Excel implements two Microsoft file format standards:

  • ECMA-376
  • ISO 29500

Those are not the same and even incompatible in parts. It is correct, that Microsoft tries to use ISO 29500 more, but most files (2007) still are ECMA-376.

But yes, they kinda would have to change their shitty, ISO-incompatible ISO “standard” to fix this issue this way.

Or use the formula field, idk. 😅

sndrtj ,

Excel is never ever going to break backwards compatability. In fact, quite some “features” in Excel are just there to stay bug-for-bug compatible with existing systems.

Example: Excel stores dates internally as a float - called the serial date, you can view it by running DATEVALUE on any cell that contains a date. It is supposed to be the number of days since 1 January 1900. However, since early Excel versions had to be compatible with Lotus1-2-3, Excel had to be compatible with a bug in Lotus123: they had erroneously assumed 1900 to be a leap year. In addition, the indexing is off by one. So the actual 0 epoch of an Excel serial date is 30 December 1899 for all dates starting 1 March 1900.

  • All
  • Subscribed
  • Moderated
  • Favorites
  • •
  • [email protected]
  • random
  • lifeLocal
  • goranko
  • All magazines
    •