I've been tasked to rewrite a spreadsheet as a dedicated app.

Most of what is in TFA is correct but it only begins to describe the problem.

My takeaway is very simple: the world runs on broken spreadsheets that are full of bugs and wrong assumptions. And the PowerPoint presentations are shown to business people taking decisions based on the wrong numbers coming from those spreadsheets. And it's the world we live in.

> It was common for the data to be split over two or more sheets.

And when the sheet are separated, for example, by years, that's even more bugs. Stuff is counted twice. Others not at all. The concept of when an entry should be "closed" when it's opened on year X and closed on year "X + 1" is something that bewilders spreadsheets users.

> and errors as the spreadsheets grew or were adapted to new data

Errors in spreadsheets are the big one: spreadsheets are full of errors.

> Another problem with spreadsheets is that the amount of data they can contain is more limited than when you use other data analysis workflows.

Another gigantic issue is that the notion of time is broken in dynamic spreadsheets: not because a spreadsheet cannot be written to correctly deal with it. But because the spreadsheet users don't know how to properly model how values relates to varying time (typically the spreadsheet shall work, for the cell that fetches the value, for the time value of "now" and that's it).

> I know there are lots of people who love using spreadsheets.

It's because you can cosplay being an actual programmer when you're not. The result, sadly, is exactly what you'd expect: buggy spreadsheets full of broken assumptions.

Which are then sent to those meaning real business to be rewritten as dedicated apps...

P.S: as of now I'm working on finance stuff... Same old story: a spreadsheet that has overstayed its welcome, it became gigantic. It's a pure mess of fetching values (and fetching way too many values, which creates technical issues) and bogus little things left and right. So what are we doing, again? Porting that spreadsheet to a proper dedicated app that can, correctly, deal with a proper amount of data, while fixing all the little glitches and gotchas too complicated to fix in a spreadsheet.

▲

EvanAnderson 2 days ago | parent [-]

There are spreadsheet authors who would have been programmers in "another life" and it's usually possible to get thru to them re: the kinds of issues that plague spreadsheets. Those people are a pleasure to work with. I remember showing a junior finance guy how to use "JOIN" to overcome performance problems with his ad-hoc VLOOKUP-based re-implementation. He "got it" and things got a lot better.

OTOH, there are people who have no background in computational thinking or software development churning out spreadsheet nightmares w/ no regard to accuracy or maintainability. Anecdotally, they seem to be the people most attracted to glitzy formatting features, "no code" automation tools, and frightening nightmares of "linked" spreadsheets.

▲

f30e3dfed1c9 2 days ago | parent [-]

"OTOH, there are people who have no background in computational thinking or software development churning out spreadsheet nightmares w/ no regard to accuracy or maintainability."

FWIW, I think this should be seen as a failure of the software industry more than as a failure of those people. Pretty obviously, the vast majority of people have "no background in computational thinking or software development" and so they use what they can and in many cases, that's a spreadsheet.

If the software industry saw fit to offer something that was technically better while still being something they could actually use, they'd use it. But it hasn't.

	▲	EvanAnderson 2 days ago \| parent [-]
		I certainly didn't mean to sound over pejorative about the "non-programmer" spreadsheet authors. They're people trying to get stuff done using IT automation and that's a laudable goal to me. Sometimes their best intentions end up making nasty work for others later. Their hearts are in the right place. I have mixed feeling about spreadsheet nightmares as a failure of the software industry. I don't think any amount of well-designed, technically better software is going to attract the particular stripe of people who see the ends w/o concern for the means. I feel like that kind of person is usually unwilling to learn anything that doesn't appear immediately in service of their goal (even if that learning would end up helping them in the future). Short of AGI that to dictate their requirements to I don't think there's a technically better solution for some people. Even then, AGI would have to ask clarifying questions which, in my experience, this type of person regards as tiresome and needless. Trying to get these people to discuss corner cases, hard-and-fast business logic, and even basic workflow is painful.