6 ms·

Excel is a great case study. Turns out, if you give people an accessible and widely available Touring complete environment, they will manage to do pretty much a

Excel is a great case study. Turns out, if you give people an accessible and widely available Touring complete environment, they will manage to do pretty much anything with it. On another note, it's interesting to observe that the problems spreadsheets have are the same problems that plague poorly designed software everywhere - lack of documentation, lack of testing, lack of input validation, among others.

hermitcrab 6y ago
One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpreting them as dates.
- askvictor 6y ago
  Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.
  - sciurus 6y ago
    Dates can be weird just about anywhere. I recently hit a bug where all data was being checked to see if it was a datetime, and python interpreted `st1` as September 1st of the current year. https://github.com/mozilla/frost/pull/330 https://github.com/mozilla/frost/pull/330
    - ptx 6y ago
      Not really Python as such, it seems, but rather some third party library[1] which promises "generic parsing of dates in almost any string format". This is impossible to do correctly (what is 01/02/03?) and usually a bad idea to attempt. [1] https://pypi.org/project/python-dateutil/ https://pypi.org/project/python-dateutil/
      - Moru 6y ago
        There is actually some standards here at least. 01/02/03 has one order, 01-02-03 another. The character between tells you how to read them. Not that most people know how though...
        fauigerzigerk 6y ago
        >01/02/03 has one order It has one order in the U.S and a different one in most other countries.
    - icebraining 6y ago
      Can't say I blame python or even the library; why is the code even trying to convert every string in an object, instead of just the keys that are supposed to contain dates?! Seems like a poor hack, imho.
  - Sharlin 6y ago
    Excel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.
    - 2fast4you 6y ago
      Speaking of quirks, Excel still incorrectly assumes the year 1900 is a leap year. Even Excel has to try to be compatible with Excel. https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
      - eru 6y ago
        Microsoft even tried to get that quirk into the official standard.
      - phonon 6y ago
        You think that's bad...try to use a date function with a year before 1900...
  - tijsvd 6y ago
    The age-old workaround for this used to be to type an apostrophe in front of the value, which forces Excel to interpret as text. IIRC that used to work in Google Sheets as well. Doesn't that trick work anymore?
    - qayxc 6y ago
      It does, but people are uneducated and think that just because something runs in the browser, it doesn't require training and knowledge to use properly...
  - watwut 6y ago
    You can set the format of the cell.
- johnrgrace 6y ago
  A well designed high complexity spreadsheets are pretty easy to tell what's going on. The problem is most people don't put thought into how they design their spreadsheets. That's not their fault because there are very few resources that teach people how to design a complex spreadsheet well. If you have not seen a spreadsheet with say dedicated data input tabs, calculation tabs where you can see and audit the work being done, output tabs, revision notes, summary sheet you haven't seen a good spreadsheet.
  - onlyrealcuzzo 6y ago
    I have literally never seen this. Do you have any links?
    - setr 6y ago
      I've seen quite a few of these, but only in the context of video game equipment/leveling optimization sheets...
- setr 6y ago
  More problematic tbh is that excel doesn't really let you configure it for your setting: ideally you should be able to set it up with defaults suitable for genetic scientists, and then spread that configuration throughout the group. Or if it does have this feature, no one knows how to access it
munk-a 6y ago
This is pretty much the same reason that PHP is much maligned - it certainly has gotchas but they're not so much dangerous than, say, the C++ gotchas... When everyone is able to "have a go" at a tool they'll all abuse it in their special way to get things done and good on them for doing it since if every data problem required an engineering team to solve everyone's business would grind to a halt.
- eru 6y ago
  You are right about PHP vs C++. But C++ is too easy a target. I dislike PHP just as much as the next guy, but I am quite impressed with how Hack turned out. It's Facebook's improved PHP. See eg https://hacklang.org/ https://hacklang.org/ It's not that Hack is a good language in absolute terms. But I think it's about the best language they could have come up with when starting from PHP. (For a better comparison instead of C++, but still in similar spirit to C++, perhaps take D.)
ianhorn 6y ago
It’s an extreme end of the spectrum where static, restrictive, but harder to screw up is one end, and weak, dynamic, permissive, but easy to screw up is on the other. For what it’s good for, it’s great. I love it because you can do many things faster with it than anything else, and the immediate feedback and visual data flow aren’t matched in any language’s IDE I’ve seen. But like all systems, when they grow they need checks. Like the trope about when your code grows to too much complexity, you start needing static types.
Fej 6y ago
It's not even just that Excel is Turing-complete. It's that Excel is widely used and is its own programming language with a comprehensive standard library, and I don't mean VBA - entering the = character in a cell opens up a massive library of functions which can be used in a fashion sort of similar to functional languages, since everything is nested functions. There's branching. There's Boolean operations. There's variables (cells, which is ultimately the point of the entire exercise). There's even a switch construct. There's even really simple web requests! All of these are of course functions. The only thing that's missing is function definitions (which can be done with VBA, but I excluded that specifically for the sake of this exercise). It's not particularly pretty, but is usable by almost anyone, has good documentation, and a huge community of like-minded power users. As you mentioned, Excel doesn't have any of the constructs which help developers for other languages to avoid mistakes. The simple syntax, users which are not necessarily aware of good practices, and a lack of methods to implement such practices produces a perfect storm of factors to introduce bugs.

hermitcrab 6y ago

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpreting them as dates.

askvictor 6y ago
Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.
- sciurus 6y ago
  Dates can be weird just about anywhere. I recently hit a bug where all data was being checked to see if it was a datetime, and python interpreted `st1` as September 1st of the current year. https://github.com/mozilla/frost/pull/330 https://github.com/mozilla/frost/pull/330
  - ptx 6y ago
    Not really Python as such, it seems, but rather some third party library[1] which promises "generic parsing of dates in almost any string format". This is impossible to do correctly (what is 01/02/03?) and usually a bad idea to attempt. [1] https://pypi.org/project/python-dateutil/ https://pypi.org/project/python-dateutil/
    - Moru 6y ago
      There is actually some standards here at least. 01/02/03 has one order, 01-02-03 another. The character between tells you how to read them. Not that most people know how though...
      - fauigerzigerk 6y ago
        >01/02/03 has one order It has one order in the U.S and a different one in most other countries.
  - icebraining 6y ago
    Can't say I blame python or even the library; why is the code even trying to convert every string in an object, instead of just the keys that are supposed to contain dates?! Seems like a poor hack, imho.
- Sharlin 6y ago
  Excel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.
  - 2fast4you 6y ago
    Speaking of quirks, Excel still incorrectly assumes the year 1900 is a leap year. Even Excel has to try to be compatible with Excel. https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
    - eru 6y ago
      Microsoft even tried to get that quirk into the official standard.
    - phonon 6y ago
      You think that's bad...try to use a date function with a year before 1900...
- tijsvd 6y ago
  The age-old workaround for this used to be to type an apostrophe in front of the value, which forces Excel to interpret as text. IIRC that used to work in Google Sheets as well. Doesn't that trick work anymore?
  - qayxc 6y ago
    It does, but people are uneducated and think that just because something runs in the browser, it doesn't require training and knowledge to use properly...
- watwut 6y ago
  You can set the format of the cell.
johnrgrace 6y ago
A well designed high complexity spreadsheets are pretty easy to tell what's going on. The problem is most people don't put thought into how they design their spreadsheets. That's not their fault because there are very few resources that teach people how to design a complex spreadsheet well. If you have not seen a spreadsheet with say dedicated data input tabs, calculation tabs where you can see and audit the work being done, output tabs, revision notes, summary sheet you haven't seen a good spreadsheet.
- onlyrealcuzzo 6y ago
  I have literally never seen this. Do you have any links?
  - setr 6y ago
    I've seen quite a few of these, but only in the context of video game equipment/leveling optimization sheets...
setr 6y ago
More problematic tbh is that excel doesn't really let you configure it for your setting: ideally you should be able to set it up with defaults suitable for genetic scientists, and then spread that configuration throughout the group. Or if it does have this feature, no one knows how to access it

munk-a 6y ago

This is pretty much the same reason that PHP is much maligned - it certainly has gotchas but they're not so much dangerous than, say, the C++ gotchas... When everyone is able to "have a go" at a tool they'll all abuse it in their special way to get things done and good on them for doing it since if every data problem required an engineering team to solve everyone's business would grind to a halt.

eru 6y ago
You are right about PHP vs C++. But C++ is too easy a target. I dislike PHP just as much as the next guy, but I am quite impressed with how Hack turned out. It's Facebook's improved PHP. See eg https://hacklang.org/ https://hacklang.org/ It's not that Hack is a good language in absolute terms. But I think it's about the best language they could have come up with when starting from PHP. (For a better comparison instead of C++, but still in similar spirit to C++, perhaps take D.)

ianhorn 6y ago

It’s an extreme end of the spectrum where static, restrictive, but harder to screw up is one end, and weak, dynamic, permissive, but easy to screw up is on the other. For what it’s good for, it’s great. I love it because you can do many things faster with it than anything else, and the immediate feedback and visual data flow aren’t matched in any language’s IDE I’ve seen. But like all systems, when they grow they need checks. Like the trope about when your code grows to too much complexity, you start needing static types.

Fej 6y ago

It's not even just that Excel is Turing-complete. It's that Excel is widely used and is its own programming language with a comprehensive standard library, and I don't mean VBA - entering the = character in a cell opens up a massive library of functions which can be used in a fashion sort of similar to functional languages, since everything is nested functions. There's branching. There's Boolean operations. There's variables (cells, which is ultimately the point of the entire exercise). There's even a switch construct. There's even really simple web requests! All of these are of course functions. The only thing that's missing is function definitions (which can be done with VBA, but I excluded that specifically for the sake of this exercise). It's not particularly pretty, but is usable by almost anyone, has good documentation, and a huge community of like-minded power users. As you mentioned, Excel doesn't have any of the constructs which help developers for other languages to avoid mistakes. The simple syntax, users which are not necessarily aware of good practices, and a lack of methods to implement such practices produces a perfect storm of factors to introduce bugs.