There was a similar piece of research done on spreadsheets generated by financial bods. They found something like 10% of spreadsheets contained errors affecting the calculations.
Excel hell messes up ~20 per cent of genetic science papers
Scientific literature often mis-names genes and boffins say Microsoft Excel is partly to blame. “Automatic conversion of gene symbols to dates and floating-point numbers is a problematic feature of Excel software,” wrote Mark Ziemann, Yotam Eren and Assam El-OstaEmai, of the Baker IDI Heart & Diabetes Institute in Australia, …
-
-
Thursday 25th August 2016 09:33 GMT Anonymous Coward
Yes, but in that case the act of doing this on Excel was deliberate - it's called plausible deniability..
They wouldn't have that using LibreOffice.
Hmm, as ODF is the UK government document standard, maybe that's an extra argument to mandate LO? I would love to be a fly on the wall at Microsoft HQ if anyone close to government would publicly float that idea :)
Yes, I'm an evil man. Why?
-
-
Thursday 25th August 2016 06:36 GMT Anonymous Coward
My pet gripe is
That the list of custom formats does not include
YYYY-MM-DD'T'HH:mm:ss
or
YYYY-MM-DD HH:mm:ss
Makes working with time data a PITA. The standard ones don't have the seconds part.
I am sure that many others have their own ones.
Fighing Excel and its wierdness in a company that seems to use it for all sorts of things (incluing many wrong ones) is something that I have to endure on a daily basis.
-
Thursday 25th August 2016 08:20 GMT DaLo
Re: My pet gripe is
"That the list of custom formats does not include..."
Are you being serious? If you are, do you not realise that you can type whatever format you want into it, that's why it's called 'Custom'. The lists in the box are just suggestions and examples and custom ones you have already created for that workbook.
You can also save that workbook with all your commonly used custom format as a template for subsequent spreadsheets.
-
Thursday 25th August 2016 09:48 GMT Robin
Re: My pet gripe is
Are you being serious? If you are, do you not realise that you can type whatever format you want into it, that's why it's called 'Custom'. The lists in the box are just suggestions and examples and custom ones you have already created for that workbook.
A condescending reply on a tech forum? That must be a first.
But anyway, another +1 here for the format the OP mentioned. I was surprised when they let me use it in a reporting function I made for an admin tool recently.
-
Thursday 25th August 2016 11:12 GMT DaLo
Re: My pet gripe is
Not meant to be condescending, just didn't know if the OP was being serious or sarcastic and didn't want to be trolled. Maybe it's bad UX but the OP seemed to know what format they required, just didn't realise that custom formats is designed to use any custom format and you can add your own.
-
Thursday 25th August 2016 12:51 GMT imanidiot
Re: My pet gripe is
What surprises me most is that YYYY-MM-DD still isn't the commonly used/accepted format. I work in a company that does a lot of bussiness with US companies. I don't know how many times DD-MM-YYv. MM-DD-YY format F(*^ups have resulted in problems but it has to be quite often. And even now there are many forms and procedures where DD-MM-YY is the only accepted format...
-
-
-
Friday 26th August 2016 12:39 GMT jimbo60
Re: My pet gripe is
Well, how about this then: If you type in a custom date / time format as suggested, it completely fails to work when creating pivot tables from that data. You have to use one of the non-custom fields for the pivot table to correctly separate the timestamps. Not cool when I'm analyzing data timestamped to fractions of seconds.
-
-
Thursday 25th August 2016 12:54 GMT Zippy's Sausage Factory
Re: My pet gripe is
I'm guessing the problem the OP is having here is other people. Namely that there is a specific need to format the dates and times in this format. The fact that Excel doesn't contain the format with seconds means people are using the format that contains only minutes and then there are support calls when they don't understand why the data doesn't look right.
In other words, Excel is making people take a shortcut down the wrong route.
(As an aside, if I had all the money I've seen wasted thanks to Excel diverted to my bank account, I'd be able to own my own private island, which I could fly to and from every day in a helicopter made of solid gold piloted by a hired supermodel.)
-
Saturday 27th August 2016 06:41 GMT Black Betty
Re: My pet gripe is
Cosmetics. And as pointed out by others, easily fixed by creating a formatting template.
If working with time data is your thing, then shouldn't decent time and date arithmetic be topping your wishlist? Not to mention a proper time storage format. FFS compacting them into a REAL to save space stopped making sense the day computer memories breached Bill Gate's infamous 640K limit.
-
-
-
Thursday 25th August 2016 08:25 GMT Martin an gof
problematic feature of Excel software
Reformatting affects all sorts of software, such that I usually find myself turning off nearly all 'autocorrect' features, just to stop the annoyances of having to re-type stuff. The one that's annoying me at the moment is at work, where Outlook insists on autocorrecting (in formatted emails) our postcode, which ends 3RD, to 3rd
And don't talk about the problems of writing emails with a mixture of langauges - in my case Welsh and English. Best just turn off all correction features.
M.
-
-
Thursday 25th August 2016 11:16 GMT Martin an gof
"formatted emails": there's your problem, right there ;)
Well yes. When I originate an email it's plain text, but when replying to an email it's invariably formatted, and getting rid of the formatting can muck up the "history".
You know, I still can't get used to top-posting and the way Outlook mangles / ignores signature separators...
M.
-
Thursday 25th August 2016 13:52 GMT VinceH
"Well yes. When I originate an email it's plain text, but when replying to an email it's invariably formatted, and getting rid of the formatting can muck up the "history"."
When I originate an email it's plain text - and it's also plain text when I reply to one, no matter what format the original came in!
I also interleave my replies, which did lead to one colleague asking me why I sent his email back to him without replying (his "preview pane" was small enough that he couldn't see my reply, which he expected to be at the top).
-
-
Thursday 25th August 2016 13:45 GMT Wensleydale Cheese
"Except, of course, any software which starts out attempting to be too clever by half ends up not being clever enough."
I've recently run into series of supplier invoice numbers which start with 118, and the "intelligent" pattern recognition system in OS X tries to interpret them as phone numbers.
In my country, 118 is the number for the Fire Brigade.
Oops.
-
-
-
Thursday 25th August 2016 11:21 GMT Just Enough
Everything is potentially a US formatted date
Using Excel in a chain of data transference always results in these kind of errors. Usual one is telephone numbers being rendered in scientific notation. Or the old favourite of a DD/MM/YYYY formatted date becoming a "DD/MM/YYYY" string, which then is interpreted as a MM/DD/YYYY formatted date. Excel will attempt to turn just about any code with forward slashes into a guessed date.
I also struggled with one that insisted in converting the user name June1 into a date.
-
Thursday 25th August 2016 11:26 GMT Omgwtfbbqtime
Re: Everything is potentially a US formatted date
I found the best way to deal with this was set your region to USian and leave it there. I don't get the mismatched date formats on the initial import (.CSV from a USian server) and once saved to .xlsx it keeps the dates the way they are meant to be for the other users.
-
-
Thursday 25th August 2016 11:43 GMT Anonymous Coward
"Automatic conversion of any user input to dates and floating-point numbers is a problematic feature of Excel software"
Hear hear.
Another example: exported lists of usernames and passwords sent as an Excel spreadsheet.
The passwords were 8-character random hex strings. It worked fine for 99.9% of the time - except for ones which happened to be like "123456e8" which Excel had munged into floating-point numbers.
Stripping leading zeros off telephone numbers is another problem.
-
Friday 26th August 2016 04:34 GMT gzuckier
"Automatic conversion of
gene symbolsany user input to dates and floating-point numbers is a problematic feature of Excel software"As in, 60,000 distinct 14 digit ID numbers pasted into excel all coming up as 5.42342E14 and when reformatted as number, all coming up as 54234200000000? You mean that's not what was desired?
-
-
-
-
Thursday 25th August 2016 11:22 GMT Anonymous Coward
Re: Storing semi text data in a tool designed to process numbers automatically.
You know there's a very simple way to insert data as plain strings, right? IIRC it dates back as far as 1-2-3, but I never used Multiplan or Visicalc.
Any tool you don't really learn to use will bite your back sooner or later.
-
-
-
-
-
Thursday 25th August 2016 09:47 GMT Doctor Syntax
Re: but...
" Nanny Microsoft knows more about what you are trying to do than you do."
It's not just Microsoft. LibreOffice Calc has similar problems - although it might be that they're trying to be bug-compatible with Excel. The real problem is with any dev who decides to try to double-guess what the user's going to do has put a foot on a slope which is far slippier than they ever imagined.
-
Thursday 25th August 2016 09:57 GMT m0rt
Re: but...
i also deal with a lot of phone numbers, and a lot or reporting that is invariably created as a CSV file.
Excel is the bane of my life. I personally use Libreoffice, but clients use Excel. They then complain when they open their CSV reports in excel, and excel subsequently craps all over the data.
Don't even get me started on phone numbers that are international format with a '+' at the beginning. Excel helpfully starts to see that as a formula.
Whereas Libreoffice will always show you csv options before pulling in the sheet, Excel just thinks you are too stupid and opens it in the way it thinks you want your data.
Clippy is dead, to come back and haunt us as a spreadsheet poltergeist.
-
Thursday 25th August 2016 10:15 GMT Anonymous Coward
Re: but...
This week I had someone from Sales asking me to "crack the password" on an Excel sheet. Turned out it wasn't password protected, just corrupt. Well, only Excel reported it corrupt* . LibreOffice opened it fine, and when I re-saved it Excel was happy with it too. Madness.
* I'm not saying it wasn't corrupt - it came from our Sales department after all.
-
Thursday 25th August 2016 11:29 GMT Anonymous Coward
Re: but...
for one part of my job I commonly have to deal with .CSV files, because that's the only format that a piece of software we rely on for certain important jobs is pretty much guaranteed to eb fien with.Oh, it's gotten better at trying to import.xls files, but it still can;t manage them as reliably as .csv's.
So, I receive .xls files from user. I look at what the job entails. Hmmn... I can merge those two together before using the combined file with this other file to get the desired end result.So - save out first file as .csv - all OK. Save out second fiel as .csv - seemed to go ok. Reopen first file so I can tack it on end of secon done - 'cannot understand file format' or some such nonsense from Excel. BUT YOU CREATED IT JUST A COUPLE OF MINUTES AGO! Grrrr... - I had about fifteen months of that kind of nonsense before Excel finallyseemed to become capable of reliably reading .csv's it had created itself.
And I'm miffed that Excel doesn't use BigEndian as the standard date format nor does it appear to do anything sensible with columns of dates in datafiles I receive, so I have to manually format columns with dates every damned time.
This on top of teh travesty that is Outlook. And people wonder why I've gone off all things IT in this last few years.
-
Friday 26th August 2016 04:42 GMT gzuckier
Re: but...
There is/was a weird bug in a recent version of Excel that would prevent it opening perfectly fine spreadsheets at pseudorandom; the same spreadsheet format with varying daily data would work 9 days out of 10 but the tenth day it wouldn't open. Has something to do with the new file format, zipped xml, in some way I assume.
I miss the older version.
-
-
-
-
Thursday 25th August 2016 07:08 GMT dan1980
Among the things Excel does to gene names include changing “SEPT2”, the name of a gene thought to have a role in proper formation of cell structure, to the date “2-Sep”.
And after the 'auto-formatting', it once again has a role in proper formation of a cell structure - as a date in an spreadsheet cell.
-
Thursday 25th August 2016 07:17 GMT Millennia
Doesn't happen with Google
It is the constant thing with all M$ products that they think they know what you want better than you and force change it. This has now crept into Apple with it's bloody autocorrect changing what you want to say, sometimes without even giving you an option to reject the change, so you have to go back over and over again or risk sending a non sensical message.
Why don't the devs behind this just piss off with their auto-corrects? By all means underline a word, or highlight a cell that may be wrong and give the user chance to correct or ignore the warning, but quit the auto-correct shit as it actually makes us waste time going back and forcing what WE want to actually say! </rant>
-
Thursday 25th August 2016 07:22 GMT james 68
Another issue is the inverse square law of intelligence, where the more specific intelligence in a given field is displayed by an individual, the less generalised intelligence/common sense is displayed by that individual.
In essence, trying to get a geneticist to understand excel is like trying to get Kim Kardashian to understand humility.
Biting the hand that feeds IT
