5 ms·

Gene name errors can be introduced inadvertently when using Excel in bioinformatics

38 points by soundsop 18y ago

deleted 18y ago
[deleted]
spolsky 18y ago
Excel has a cell format called "TEXT" that prevents all of these problems. If you plan to use a range of your spreadsheet as text (and not as numbers or dates), format it as Text and excel will never misinterpret what you type. You can also start the cell entry with a single quote to make a single cell store text.
mixmax 18y ago
A few years ago I had an office at an incubator, and my neighbour company made a better spreadsheet. He had worked in the financial sector before, and had conducted research that showed that around 90% (Don't remember the exact number) of excel files in the financial sector had mistakes in them. Scary stuff..
- trapper 18y ago
  If you are doing any sort of data analysis you really need to avoid spreadsheets. Unfortunately, most data analysis is just as bad. I have worked with SAS & R code, thousands of lines long, all without a single test. Basically a big series of hacks. Mostly, statisticians I have worked with have no concept of testing, or even why it's necessary. I shudder to think of the gross error in research results from all I have seen!
  - adsyoung 18y ago
    Yeah...we do most aircraft structural analysis in excel. It scares the hell out of me.
    - trapper 18y ago
      That is just scary.
      - adsyoung 18y ago
        Well the thing to remember is that everything is checked twice and there's a 1.5 safety factor on top to protect against mistakes but the state of the industry is poor. Engineers often get away with creating monstrous spreadsheets that are difficult to follow: - scattered logic - methodology obsficated with complex excel formulas - magic numbers that you have a hell of a time tracking down the source of - links all over the place. Are they still valid or have they gone bad due to all the copy/pasting? etc etc As a programmer who has to write something that has logic and no ambiguity for a computer to understand it, it makes you want to beat your head against a wall trying to understand and check someones work. Until recently (victim of the downturn) I was in a small dev team looking to automate and improve things. We had some good wins but it's a struggle. If someone like toyota starts making aircraft seriously and develops more standardized methods rather than simply employing piles of engineers to punch numbers wildly into excel they may be able to crush everyone else in time to market.
        gruseom 18y ago
        magic numbers that you have a hell of a time tracking down the source of - links all over the place. Are they still valid or have they gone bad due to all the copy/pasting? etc etc Would it help to have a sheet of centralized assumptions (constants, magic numbers, etc.) that people could link to from any spreadsheet? Would it help to be able to reuse formulas from another sheet without copy/pasting them? i.e. link in such a way that you can fill in your own numbers, but keep the original logic? Would it help to be able to "lock down" certain models that people could use to do their analysis, but not make arbitrary changes to? Would it help to be able to build some computations in a spreadsheet and wrap them up as a function that other spreadsheets could call (passing in their own arguments)? I'm interested in what can be done to raise the level of abstraction (and consistency) without giving up the spreadsheet UI which is so productive for many users.
  - time_management 18y ago
    Trying to do real programming in SAS is a nightmare. The language sucks. Also, it's proprietary and expensive. It's surprising that statisticians would fail to understand the importance of testing, given that a lot of the root issues in statistics (e.g. the Bayesian/frequentist dispute over "what is a probability?") are epistemological.
    - trapper 18y ago
      Agreed entirely. I don't understand why they don't get testing either. Not one I have met realises just how error ridden their scripts probably are. Still, a lot of programmers don't understand the value of testing yet.
    - thwarted 18y ago
      One of my undergrad classes was for SAS, and they mixed MIS, CS, math, and statistics people in this class. It took weeks for those on the CS track to get that when your code says "proc average", you're _calling_ a procedure, not defining a function. We had no idea where the code was supposed to go. There was a serious disconnect between the students and the professor in that class, who, I later realized would have just needed to say "this calls the procedure average" and things would have been fine. We had no idea, initially, how we were getting results. Of course, we had to run all this on VMS and print it out on a fan-fold line printer, and it wasn't very interactive. SAS products seem to be more visual and interactive now.
albertcardona 18y ago
Beyond the floating-point problems, there's also the automatic name capitalization, the automatic replacement of "teh" for "the", the automatic conversions of numbers into dates, and many more. Everyday practice gets me a bit more and more annoyed at all the automatisms that software office packages come with. And they are nearly all on by default! It takes a skilled person about 10 minutes to disable nearly all automatisms in MSWord or in OpenOffice Write, in several menus. In MSExcel, I don't even know where to look myself (having switched to Gnumerics long ago). We would all appreciate one single button that says: "Don't do anything unless I told you to."
- jgrahamc 18y ago
  "Don't do anything unless I told you to." For a moment there you made me have a bad flashback to Microsoft Frontpage circa 1997.
ars 18y ago
This paper is from 2004, any updates? Did microsoft add an option to disable the conversion? Are public databases cleaner?
jodrellblank 18y ago
This is the sort of error that worries me a lot about "programmable" business systems. That people who don't know the domain and tools well enough, and don't have the pernickety edge-case worrying mindset will introduce errors like this. Writing a report that doesn't take into account a new database table, editing permissions and leaving a big hole in them, etc. What I'm not sure about though, is whether it really matters. Whether the tradeoff with only allowing certain people to make changes saves enough trouble that it's worth the limitations. Businesses with people are very good at surviving errors, after all...
jherdman 18y ago
I can't get into specifics per confidentiality at my job, but I'll tell you this: if you saw some of the things being done in the financial sector with Excel that I have you'd cry and go into a fit of rage.
- ConradHex 18y ago
  Whenever I think about the financial sector at all, I cry and/or go into a fit of rage already. So it's probably for the best that I don't know.
- gruseom 18y ago
  If you can't go into specifics, can you at least go into generalities? What sorts of things about Excel give rise to the worst stuff you've observed?
time_management 18y ago
I worked at a hedge fund where one of the phone-screen brainteasers was "How many zeros are at the end of 35!?" The very not-smart people would try to use Excel for this, and get hilariously (and obviously) incorrect results due to rounding errors.
- bd 18y ago
  Took me a few seconds to parse the question (that it was about "35 factorial" and not about some binary representation of "35" ended with interrobang "?!") One possible solution in Python: import operator fact = str(reduce(operator.mul, xrange(2,36))) print len(fact) - len(fact.rstrip('0')) >>> 8 Please forgive me for using strings for numerical operation, it was for a brevity's sake (I still remember Leah Culver getting chastised here on HN).
- sireat 18y ago
  Took me a while to figure out where the "extra" 8th zero came out.... namely 25. That there are at least 7 zeros is evident, since 5 and 2 is needed for each 0, and 5s are more scarce than 2s in 35!
  - bd 18y ago
    Aha, so that's the elegant way how this was supposed to be solved (in your head and on the spot).
    - time_management 18y ago
      It's basically an honesty test, since the interviewer explicitly says not to use paper or a calculator (which would include Excel). Quants are given harder questions, and I doubt that a trader would be dinged for not knowing the correct approach, since it's not a brainteaser that would correlate highly to trading acumen. But the Excel answer (14, I believe) is a very wrong one.
      - bd 18y ago
        Interesting, 35! according to various tools: 10333147966386100000000000000000000000000 (Open Office Calc) 10333147966386100000000000000000000000000 (Excel) 10333147966386144000000000000000000000000 (Gnumeric) 10333147966386145000000000000000000000000 (Windows Calculator) 10333147966386144929666651337523200000000 (Python)
biohacker42 18y ago
Excel shouldn't be used for science. Or for that matter anything important and numerically intensive. But there's silly people everywhere, even in science.
- deleted 18y ago
  [deleted]

38 points by soundsop 18y ago

deleted 18y ago

[deleted]

Excel has a cell format called "TEXT" that prevents all of these problems. If you plan to use a range of your spreadsheet as text (and not as numbers or dates), format it as Text and excel will never misinterpret what you type. You can also start the cell entry with a single quote to make a single cell store text.

mixmax 18y ago

A few years ago I had an office at an incubator, and my neighbour company made a better spreadsheet. He had worked in the financial sector before, and had conducted research that showed that around 90% (Don't remember the exact number) of excel files in the financial sector had mistakes in them. Scary stuff..

trapper 18y ago
If you are doing any sort of data analysis you really need to avoid spreadsheets. Unfortunately, most data analysis is just as bad. I have worked with SAS & R code, thousands of lines long, all without a single test. Basically a big series of hacks. Mostly, statisticians I have worked with have no concept of testing, or even why it's necessary. I shudder to think of the gross error in research results from all I have seen!
- adsyoung 18y ago
  Yeah...we do most aircraft structural analysis in excel. It scares the hell out of me.
  - trapper 18y ago
    That is just scary.
    - adsyoung 18y ago
      Well the thing to remember is that everything is checked twice and there's a 1.5 safety factor on top to protect against mistakes but the state of the industry is poor. Engineers often get away with creating monstrous spreadsheets that are difficult to follow: - scattered logic - methodology obsficated with complex excel formulas - magic numbers that you have a hell of a time tracking down the source of - links all over the place. Are they still valid or have they gone bad due to all the copy/pasting? etc etc As a programmer who has to write something that has logic and no ambiguity for a computer to understand it, it makes you want to beat your head against a wall trying to understand and check someones work. Until recently (victim of the downturn) I was in a small dev team looking to automate and improve things. We had some good wins but it's a struggle. If someone like toyota starts making aircraft seriously and develops more standardized methods rather than simply employing piles of engineers to punch numbers wildly into excel they may be able to crush everyone else in time to market.
      - gruseom 18y ago
        magic numbers that you have a hell of a time tracking down the source of - links all over the place. Are they still valid or have they gone bad due to all the copy/pasting? etc etc Would it help to have a sheet of centralized assumptions (constants, magic numbers, etc.) that people could link to from any spreadsheet? Would it help to be able to reuse formulas from another sheet without copy/pasting them? i.e. link in such a way that you can fill in your own numbers, but keep the original logic? Would it help to be able to "lock down" certain models that people could use to do their analysis, but not make arbitrary changes to? Would it help to be able to build some computations in a spreadsheet and wrap them up as a function that other spreadsheets could call (passing in their own arguments)? I'm interested in what can be done to raise the level of abstraction (and consistency) without giving up the spreadsheet UI which is so productive for many users.
- time_management 18y ago
  Trying to do real programming in SAS is a nightmare. The language sucks. Also, it's proprietary and expensive. It's surprising that statisticians would fail to understand the importance of testing, given that a lot of the root issues in statistics (e.g. the Bayesian/frequentist dispute over "what is a probability?") are epistemological.
  - trapper 18y ago
    Agreed entirely. I don't understand why they don't get testing either. Not one I have met realises just how error ridden their scripts probably are. Still, a lot of programmers don't understand the value of testing yet.
  - thwarted 18y ago
    One of my undergrad classes was for SAS, and they mixed MIS, CS, math, and statistics people in this class. It took weeks for those on the CS track to get that when your code says "proc average", you're _calling_ a procedure, not defining a function. We had no idea where the code was supposed to go. There was a serious disconnect between the students and the professor in that class, who, I later realized would have just needed to say "this calls the procedure average" and things would have been fine. We had no idea, initially, how we were getting results. Of course, we had to run all this on VMS and print it out on a fan-fold line printer, and it wasn't very interactive. SAS products seem to be more visual and interactive now.

albertcardona 18y ago

Beyond the floating-point problems, there's also the automatic name capitalization, the automatic replacement of "teh" for "the", the automatic conversions of numbers into dates, and many more. Everyday practice gets me a bit more and more annoyed at all the automatisms that software office packages come with. And they are nearly all on by default! It takes a skilled person about 10 minutes to disable nearly all automatisms in MSWord or in OpenOffice Write, in several menus. In MSExcel, I don't even know where to look myself (having switched to Gnumerics long ago). We would all appreciate one single button that says: "Don't do anything unless I told you to."

jgrahamc 18y ago
"Don't do anything unless I told you to." For a moment there you made me have a bad flashback to Microsoft Frontpage circa 1997.

ars 18y ago

This paper is from 2004, any updates? Did microsoft add an option to disable the conversion? Are public databases cleaner?

jodrellblank 18y ago

This is the sort of error that worries me a lot about "programmable" business systems. That people who don't know the domain and tools well enough, and don't have the pernickety edge-case worrying mindset will introduce errors like this. Writing a report that doesn't take into account a new database table, editing permissions and leaving a big hole in them, etc. What I'm not sure about though, is whether it really matters. Whether the tradeoff with only allowing certain people to make changes saves enough trouble that it's worth the limitations. Businesses with people are very good at surviving errors, after all...

jherdman 18y ago

I can't get into specifics per confidentiality at my job, but I'll tell you this: if you saw some of the things being done in the financial sector with Excel that I have you'd cry and go into a fit of rage.

ConradHex 18y ago
Whenever I think about the financial sector at all, I cry and/or go into a fit of rage already. So it's probably for the best that I don't know.
gruseom 18y ago
If you can't go into specifics, can you at least go into generalities? What sorts of things about Excel give rise to the worst stuff you've observed?

time_management 18y ago

I worked at a hedge fund where one of the phone-screen brainteasers was "How many zeros are at the end of 35!?" The very not-smart people would try to use Excel for this, and get hilariously (and obviously) incorrect results due to rounding errors.

bd 18y ago
Took me a few seconds to parse the question (that it was about "35 factorial" and not about some binary representation of "35" ended with interrobang "?!") One possible solution in Python: import operator fact = str(reduce(operator.mul, xrange(2,36))) print len(fact) - len(fact.rstrip('0')) >>> 8 Please forgive me for using strings for numerical operation, it was for a brevity's sake (I still remember Leah Culver getting chastised here on HN).
sireat 18y ago
Took me a while to figure out where the "extra" 8th zero came out.... namely 25. That there are at least 7 zeros is evident, since 5 and 2 is needed for each 0, and 5s are more scarce than 2s in 35!
- bd 18y ago
  Aha, so that's the elegant way how this was supposed to be solved (in your head and on the spot).
  - time_management 18y ago
    It's basically an honesty test, since the interviewer explicitly says not to use paper or a calculator (which would include Excel). Quants are given harder questions, and I doubt that a trader would be dinged for not knowing the correct approach, since it's not a brainteaser that would correlate highly to trading acumen. But the Excel answer (14, I believe) is a very wrong one.
    - bd 18y ago
      Interesting, 35! according to various tools: 10333147966386100000000000000000000000000 (Open Office Calc) 10333147966386100000000000000000000000000 (Excel) 10333147966386144000000000000000000000000 (Gnumeric) 10333147966386145000000000000000000000000 (Windows Calculator) 10333147966386144929666651337523200000000 (Python)

biohacker42 18y ago

Excel shouldn't be used for science. Or for that matter anything important and numerically intensive. But there's silly people everywhere, even in science.

deleted 18y ago
[deleted]