5 ms·

You jest, but people really do underestimate Excel. Yes, it sucks - but there is a reason that despite all its flaws, people keep trying to use it for things. (

You jest, but people really do underestimate Excel. Yes, it sucks - but there is a reason that despite all its flaws, people keep trying to use it for things. (And no, that reason is not "they're too stupid to learn how to code") And I'm not sure the things that make Excel so crappy could really be fixed without sacrificing the things that make it such a flexible and useful tool. For instance, adding the ability to recurse or iterate sanely would remove the transparency of having every iteration of a computation clearly visualized cell-by-copy-pasted-cell.

skriticos2 8y ago
For me it usually is "I would like to use some sensible tools, but corporate policy requires me to not do that (because they are not available) - so I have to (ab)use Excel/Office/VBA to get things done." It's not pretty, stable, efficient or really maintainable, but if you are forced to use a hammer.. ps. Excel does some things OK, like drawing pretty graphs on smallish datasets and visualize quick analysis with pivot tables. It just lacks a sensible scripting language and is generally very brittle once the data is getting nontrivial and more than one person is involved.
- MrEldritch 8y ago
  It's also really quite good for automating numerical calculations; a sort of poor-man's-Matlab. Having every intermediate value in a multi-step computation both immediately visualized (and thus easily sanity-checked) and accessible as a variable, without having to keep track of names, is very powerful.
- TeMPOraL 8y ago
  Excel-based workflows are definitely brittle, but people learn to be careful with it (you have to, if you want to keep your job). One big problem it solves for regular people (i.e. not programmers) is this: "our workflow and/or reality of our business changes much faster than the IT department/outside contractors can keep up with". (The other big, and related, problem is: "our IT department/outside contractors have no clue and don't really even care much about what we actually do or need".)
- sjy 8y ago
  I’ve been solving this problem with PowerShell for the last year or so. It’s usually available on locked-down corporate Windows boxes, and you can use the entire .NET Framework, as well as third-party .NET libraries, without admin rights. I’m using it with System.Data.SQLite to build a WPF application at the moment, which will hopefully replace a whole bunch of messy and hard-to-maintain Excel sheets and Access databases.
  - eggy 8y ago
    I love PowerShell for this reason. I had a 100 CSV files, and I needed the first two rows removed to put them into a signal viewing app, and it took only a few lines of PowerShell to do this on my corporate machine. I would normally do this in another PL, but strangely PowerShell's solution further pushed me to look into it for other uses on that machine. I wish there were a good book for this, since I actually prefer that than just Googling quick solutions when trying to actually learn something.
    - sjy 8y ago
      Yep, I could have learned much faster with a good book. Most PS tutorials are targeted at sysadmins with minimal programming experience. I would have preferred something that assumed programming experience and focused on what makes PS different from other scripting languages.
    - rejschaap 8y ago
      I work in a Microsoft shop, but I always have a bash shell running. It prevents me from really diving into PowerShell. I will end up running a quick sed '2,3d' on the csv files and move on with my life.
      - eggy 8y ago
        I have PowerShell on my local company machine, and without Admin priveleges I can't even sideload bash through a Git install. We're not a software company, so requesting Git is also out of the standard operational procedures as a user. IT knows my coding background (since 1978), but they can't change policy.
dandelany 8y ago
I work for NASA JPL... I think most people would be amazed if they knew how much “rocket science” was done in Excel.
- chris_wot 8y ago
  Given the problems with statistical functions in Excel, perhaps we should be a tad worried?
  - eggy 8y ago
    A lot of Excel's issues are well-known because of how pervasive it is. Python and kin rely on C libraries and other layers of imported packages for numerical work that can lead to all sorts of numerical mischief. Even doing: round (2.575, 2) in Python leads to 2.57 instead of rounding up to 2.58. Everything in our engineering firm is checked by another engineer.
    - zokier 8y ago
      In [7]: round(2.575, 2) Out[7]: 2.58 Umm?
      - EForEndeavour 8y ago
        From the Python 3.7 docs (https://docs.python.org/3/library/functions.html#round https://docs.python.org/3/library/functions.html#round): The behavior of round() for floats can be surprising: for example, round(2.675, 2) gives 2.67 instead of the expected 2.68. This is not a bug: it’s a result of the fact that most decimal fractions can’t be represented exactly as a float.
        zokier 8y ago
        Well that is just normal float fun :) In [17]: Decimal(2.675) Out[17]: Decimal('2.67499999999999982236431605997495353221893310546875') So sort of round() is working as expected, but the number you are not inputting is not the one you are expecting.
ta_egdhs 8y ago
you can recurse in excel. its disabled by default but you can set the max depth on circular references in some menu. I haven't used it since the 2007 version
dragonwriter 8y ago
> Yes, it sucks - but there is a reason that despite all its flaws, people keep trying to use it for things Yes, but the main one is that institutional IT policies frequently dictate that people whose aren't employed specifically in software development roles aren't allowed software development tools, but everyone in the org tends to be allowed core Office apps like Excel. When it's literally the only remotely applicable tool most people are allowed to have, it shouldn't be surprising that it gets used for a lot, independent of merit.

skriticos2 8y ago

For me it usually is "I would like to use some sensible tools, but corporate policy requires me to not do that (because they are not available) - so I have to (ab)use Excel/Office/VBA to get things done." It's not pretty, stable, efficient or really maintainable, but if you are forced to use a hammer.. ps. Excel does some things OK, like drawing pretty graphs on smallish datasets and visualize quick analysis with pivot tables. It just lacks a sensible scripting language and is generally very brittle once the data is getting nontrivial and more than one person is involved.

MrEldritch 8y ago
It's also really quite good for automating numerical calculations; a sort of poor-man's-Matlab. Having every intermediate value in a multi-step computation both immediately visualized (and thus easily sanity-checked) and accessible as a variable, without having to keep track of names, is very powerful.
TeMPOraL 8y ago
Excel-based workflows are definitely brittle, but people learn to be careful with it (you have to, if you want to keep your job). One big problem it solves for regular people (i.e. not programmers) is this: "our workflow and/or reality of our business changes much faster than the IT department/outside contractors can keep up with". (The other big, and related, problem is: "our IT department/outside contractors have no clue and don't really even care much about what we actually do or need".)
sjy 8y ago
I’ve been solving this problem with PowerShell for the last year or so. It’s usually available on locked-down corporate Windows boxes, and you can use the entire .NET Framework, as well as third-party .NET libraries, without admin rights. I’m using it with System.Data.SQLite to build a WPF application at the moment, which will hopefully replace a whole bunch of messy and hard-to-maintain Excel sheets and Access databases.
- eggy 8y ago
  I love PowerShell for this reason. I had a 100 CSV files, and I needed the first two rows removed to put them into a signal viewing app, and it took only a few lines of PowerShell to do this on my corporate machine. I would normally do this in another PL, but strangely PowerShell's solution further pushed me to look into it for other uses on that machine. I wish there were a good book for this, since I actually prefer that than just Googling quick solutions when trying to actually learn something.
  - sjy 8y ago
    Yep, I could have learned much faster with a good book. Most PS tutorials are targeted at sysadmins with minimal programming experience. I would have preferred something that assumed programming experience and focused on what makes PS different from other scripting languages.
  - rejschaap 8y ago
    I work in a Microsoft shop, but I always have a bash shell running. It prevents me from really diving into PowerShell. I will end up running a quick sed '2,3d' on the csv files and move on with my life.
    - eggy 8y ago
      I have PowerShell on my local company machine, and without Admin priveleges I can't even sideload bash through a Git install. We're not a software company, so requesting Git is also out of the standard operational procedures as a user. IT knows my coding background (since 1978), but they can't change policy.

dandelany 8y ago

I work for NASA JPL... I think most people would be amazed if they knew how much “rocket science” was done in Excel.

chris_wot 8y ago
Given the problems with statistical functions in Excel, perhaps we should be a tad worried?
- eggy 8y ago
  A lot of Excel's issues are well-known because of how pervasive it is. Python and kin rely on C libraries and other layers of imported packages for numerical work that can lead to all sorts of numerical mischief. Even doing: round (2.575, 2) in Python leads to 2.57 instead of rounding up to 2.58. Everything in our engineering firm is checked by another engineer.
  - zokier 8y ago
    In [7]: round(2.575, 2) Out[7]: 2.58 Umm?
    - EForEndeavour 8y ago
      From the Python 3.7 docs (https://docs.python.org/3/library/functions.html#round https://docs.python.org/3/library/functions.html#round): The behavior of round() for floats can be surprising: for example, round(2.675, 2) gives 2.67 instead of the expected 2.68. This is not a bug: it’s a result of the fact that most decimal fractions can’t be represented exactly as a float.
      - zokier 8y ago
        Well that is just normal float fun :) In [17]: Decimal(2.675) Out[17]: Decimal('2.67499999999999982236431605997495353221893310546875') So sort of round() is working as expected, but the number you are not inputting is not the one you are expecting.

ta_egdhs 8y ago

you can recurse in excel. its disabled by default but you can set the max depth on circular references in some menu. I haven't used it since the 2007 version

dragonwriter 8y ago

> Yes, it sucks - but there is a reason that despite all its flaws, people keep trying to use it for things Yes, but the main one is that institutional IT policies frequently dictate that people whose aren't employed specifically in software development roles aren't allowed software development tools, but everyone in the org tends to be allowed core Office apps like Excel. When it's literally the only remotely applicable tool most people are allowed to have, it shouldn't be surprising that it gets used for a lot, independent of merit.