Source: SO works in a field where they handle other people's money and payroll. Not a small mom & pop shop either.
The employees use Excel sheets handed over from "someone" and just plug in the numbers.
In Britain they messed up the covid numbers because they used the wrong format to store their infection numbers and lost all lines after 65534...
> This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.
The people are stubborn and many have had experiences with subpar enterprise software. If they are billing clients, any additional step in software is akin to a national tragedy to them. They will complain something is "broken" if it doesn't fit their mental model of what a software should be before trying anything new.
Another thing to consider: we are conditioned to believe that the web browser is a "universal runtime", i.e. it's the thing that everyone has, so you should target it. But actually, it's not the only one. When considering Windows computers (which is all that matters in offices), Excel is a kind of dark-matter universal runtime, for dark-matter IT[1]. Better, it's one that doesn't require a persistent server, or logins, and it's peer-to-peer, in a sense. Everyone has Excel installed, everyone has an org email account, therefore everyone can email xlsm files back and forth, no extra setup required (try doing that with exes). No SaaS offering has that kind of caveman convenience. And you might think -- that's horrifying. But email is the way communication/collaboration actually happens in the median bureaucracy, so Excel's ubiquity and file-orientedness is an insurmountable strength.
If these tools need any type of user interaction they won't use it.
How many people still right align text per spacebar or tab if they are pros?
The main problem here is that Excel is so very flexible, and everything else is not (generalizing here, but it gets the point across). So the after the third time you are using Excel to handle the exceptional cases, you start to wonder why you are not using it everywhere else.
Of course the counter-problem is that it is so flexible that it accommodates your mistakes without pointing them out.
no they don't. Not even for billion dollar scale accounting
> because it sounds like an easy product to sell for lots of money.
Is it Excel though? Because if not, then you're going to have the mother of all uphill battles trying to get it sold.
It would be akin to trying to convince a giant company with hundreds of millions of lines of Java code to rewrite into golang.
There were low ranking people who recognized how stupid this all was, and pointed it out. But hardly anyone with decisionmaking power even understood the problem. So nothing changed. It was the dark ages, and a major factor in why I quit.
You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming.
Excel is a program that "gives power to the people" so there are tons of crappy sheets, that often mix data with calculations and so on. But you can make good sheets if you try. And know how to.
Other comments are amateur hour.
I'm one of the other "amateur" commenters, and I know about all of those things you mentioned:
>array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection).
Problem with all of these is some combination of:
- they suck
- boomers don't know how to use them
- you can't use them for compatibility reasons
Array formulae: boomers don't use them, and the good ones aren't backwards compatible with older Excel versions or LibreOffice/Sheets (major problem when dealing with ppl outside the org)
Formula inspector: I've never found this to be useful
Excel marks inconsistent formulae ... inconsistently. It fights you when you're trying to do something slightly non-standard but otherwise perfectly legit, because it's just pattern-matching. Leads to alarm fatigue. And boomers ignore it.
Data validation for selectors: boomers don't do this (are you seeing a pattern?). More importantly, they don't interact properly with macros.
Pivot tables for summaries: boomers actually do know these, but the problem is they're not reactive. They don't refresh when the data source changes.
Locking cells: there's no convenient way to tell at a glance which cells are locked and which aren't (yes I know about the "Find All" way, that's not convenient).
Named cells: inconvenient, no namespacing, you can have duplicates of the same name that refer to different things, or dead ones that point to nothing, and there's no good way to debug it.
Tables: these are great, but sadly aren't compatible with LibreOffice/Sheets. Column names cannot be dynamic. Also they will fill down a formula even when you don't want them to, there isn't a way to turn that off afaik.
>You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
People can and will fuck up even the best-made sheets in irrecoverable ways, that are difficult/impossible to detect automatically within Excel. Yes, we locked all the cells except for inputs, password-protected the sheets, used data validation, used tables, used named references, we did all that stuff you're meant to do and more. It wasn't enough: we'd get back the filled-in sheets and there would be broken references every-fucking-where. I've forgotten exactly what they were doing, but whatever it was, I had to write Python scripts to diagnose it by looping through all the cells and comparing the formulae and sheet layout against a known good copy.
(Yeah I know I know, it's "source: trust me bro". I'm racking my brain to remember what exactly they were doing to cause the corruption, and it's eluding me. But I'm not making it up.)
Also it is common knowledge that MS tries to get rid of open spreadsheets - only allowed competition are google sheets. If your Excel doesnt have array formulas - please get a newer version. Even 2003 had them.
I dont claim that Excel doesnt have problems, also it could be better - but there are ways to make more robust calculations.
On a side note Excel and the dreaded (obsolete?) VBA are one of the last democratic things that allow non-programmers to build things.
Personally I hate tables, you can in fact add some random crap in one of the random cells.
Lots of your criticism are valid, Excel could definitely be better. But now we dont have better.
As someone trying to learn SQL, the syntax of typical queries(one page long, not some basic ones) is horrible - the language is completely not designed for tasks when you glue 20 tables together... are those queries easy to understand / inspect? IMHO no.
it's the other side of the coin for "last democratic thing that allows non-programmers to build things" (which I sympathize with). you can't just ignore the capabilities of the userbase demographics.
>Also it is common knowledge that MS tries to get rid of open spreadsheets - only allowed competition are google sheets.
it's not microsoft's fault LibreOffice doesn't have tables. OnlyOffice has them. there's a feature request thread in the LibreOffice archives, someone asked for excel-style tables to be implemented. the LO devs didn't even know what they were(!), people had to explain it to them. dev response was lukewarm. there doesn't seem to be any great push to implement it.
https://bugs.documentfoundation.org/show_bug.cgi?id=132780
>If your Excel doesnt have array formulas - please get a newer version. Even 2003 had them.
I mean the new ones. the dynamic array formulae with the spill behaviour. SEQUENCE, FILTER, SORT, UNIQUE, etc. those are only in office 365.
>As someone trying to learn SQL, the syntax of typical queries(one page long, not some basic ones) is horrible - the language is completely not designed for tasks when you glue 20 tables together... are those queries easy to understand / inspect? IMHO no.
no disagreement there, SQL is not a good language. have you tried M?
Also you act as if there werent any programming horror stories. Very often programmers want to rewrite the code they got from othera.
**
I am not sure which one is M and which one is DAX. Those are the PowerBI ones. In my opinion they are horrible. The idea of "steps" makes sense, but the gui that breaks steps is horrible. Also the syntax is horrible.
**
MS at some point had pivot tables, pivot tables with data model, power pivot, powerBI stand-alone app, "old" sql query editor. Now they try to add powerBI standalone back to Excel.. partially (on a side note I hate that poweBI exports from stand alone app to Excel are so horrible), and they got rid of the old SQL editor. If you want to write SQL in Excel you might as well use notepad..
PowerBI was supposed to be a step forward. OK it allows to build things, but is in my opinion horrible. M/DAX are horrible to use for typical operarions such as;
Changing source
Combining two sources
Joins
Joins on joins on joins
Joins
Syntax is supposed to be explicit, but it is horrible to write and horrible to read
This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.