Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The Tyranny of Spreadsheets

timharford.com

Post reply on HN
Print view View original

181–190 of 282 posts

Re: The Tyranny of Spreadsheets

#181

Post by lordnacho »

gcthomas wrote: post #4

Spreadsheets are a hammer so if that's all you know then all problems look like the nail. Really, many power users of Excel ought to be looking at more capable, testable and readable solutions from professional data analysts and scientists. Python would be a good start, but there are many options better than Excel for critical systems.

I think of Excel as the modern back of the envelope. You can prototype some things, proof of concept etc.

But if you're running things in prod via excel you haven't actually thought enough about it to call it prod.

Re: The Tyranny of Spreadsheets

#182

Post by quietbritishjim »

catherd wrote: post #148

The thing that's always baffled me about Excel is why you must always work in "minified" mode when composing formulas. It seems like just adding the ability to spread a calculation out over multiple lines and add some indentation would make the bugs everyone complains about go down by... a lot.

You can just spread the formula over multiple cells. Instead of

  A3: IF(, , )
where each of the three parameters are complex formulae, you can do:

  A3: IF(B3, C3, D3)
  B3: 
  C3: 
  D3:
Not only is the formula now broken down into simpler chunks, you also get to inspect the component results (like watches in a breakpoint! sorta...). Then you can just hide the relevant columns if you like (B,C,D in this case). You can even use a separate sheet and hide the whole sheet if you wish.

Re: The Tyranny of Spreadsheets

#183

Post by catherd »

HPsquared wrote: post #169
FriedrichN wrote: post #164

Earlier quoted context omitted.

You could, but when you finally press enter it's all compressed back to one line.

It doesn't get compressed back, I often break complex formulas into multiple lines with indentation. The only disadvantage is if someone else isn't expecting the formulas to be like this, then gets confused when they can only see the first line.

Huh. Well, LibreOffice still can't preserve the formatting. Not sure if the last version of real-Excel I tried could.

Having the compose box fit itself to the formula size or give other indication that there is more to see is still a head-scratcher why they didn't do it.

Re: The Tyranny of Spreadsheets

#184

Post by beefield »

max46 wrote: post #143

I think I big mistake people make here is that they think they are far better at everything on a computer than a non-programmer. They don't realize how much you can do in Excel and how much they suck at it. If you can't use it without a mouse and/or if you don't know pivot tables, you are just as much of a beginner as a C++ programmer who doesn't know what a pointer is. It's only after trying to convert an excel shee…

Opposing anecdote: I have converted excel spreadsheets built over years to SQL, and typically it did not take weeks to do that.

( Any organization that has a smallest reason to care about their data should remove save button from excel and start educating their personnel. Using excel in any important role should be seen as making the eventual mistakes on purpose and someone should be kept responsible.)

Re: The Tyranny of Spreadsheets

#185

Post by monocasa »

dgudkov wrote: post #177
narush wrote: post #20

Fantastic blog post. I highly recommend reading it in full, and also checking out the work of the European Spreadsheet Risks Group, and Felienne Hermans specifically (referenced in the original post). I've been working on a spreadsheet startup [1] for the past 8 months or so and those folks have a large amount of absolutely upsetting-but-helpful research on spreadsheet usage/errors. After the past few years working w…

>Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists I don't think spreadsheets are a good way to program data transformations at all. Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation…

> Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation process and inspect each step for correctness.

They absolutely give you that ability; you break the transformation across many cells. Spreadsheets are probably the most successful functional reactive programming paradigm set of languages out there.

Re: The Tyranny of Spreadsheets

#187

Post by andrew_jef »

kwyjobojoe wrote: post #170
markus_zhang wrote: post #141

Earlier quoted context omitted.

Now that you mentioned, we didn't have access to SQL Server dwh until 6 months before I left for good. Can you try sqlite? It's usually enough for simple things I think.

But then we need something to interface with the db. Getting any programming language installed isn't going to happen in government.

Nocodb is trying to solving the issues of implementing a spreadsheet-like frontend on top of a database backend https://www.nocodb.com/

Re: The Tyranny of Spreadsheets

#188

Post by tomnipotent »

dgudkov wrote: post #177
narush wrote: post #20

Fantastic blog post. I highly recommend reading it in full, and also checking out the work of the European Spreadsheet Risks Group, and Felienne Hermans specifically (referenced in the original post). I've been working on a spreadsheet startup [1] for the past 8 months or so and those folks have a large amount of absolutely upsetting-but-helpful research on spreadsheet usage/errors. After the past few years working w…

>Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists I don't think spreadsheets are a good way to program data transformations at all. Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation…

> good way

But it is an accessible way. Your average Excel user can perform any number of useful transformations with simple formulas, and then throw that into a pivot table for useful aggregations (which is also a transformation).

> tables are an afterthought to spreadsheets

Excel power users have been solving this with VLOOKUPs for decades.

> therefore even trivial joins are always a pain in Excel

Trivial to a trained user. Excel has more advanced data modeling capabilities, which includes implicit joins when creating PivotTables from tables with relationships.

https://support.microsoft.com/en-us/office/create-a-data-mod...

Re: The Tyranny of Spreadsheets

#189

Post by zoomastigophore »

Andy_G11 wrote: post #174

'000s of rows of data in a spreadsheet - sigh. But say 'database' to some people and they hear 'The Devil'. Excel is a glorious tool which welcomes all, the savvy and the unskilled but imaginative newbies alike. There is something about all those little cells that presents an itch everyone wants to scratch, and you just know that for some that scratching is going to produce something akin to a spreadsheet version of…

I think one of the main problem with databases is how do you populate them.

I wish there were simple-as-Excel frontends for databases where a normal user could input his data like in preformated Excel table without having to deal with the database mechanics.

Re: The Tyranny of Spreadsheets

#190

Post by timvdalen »

Jaepa wrote: post #5

This was adapted from Tim Harharford's podcast Cautionary Tales eps Wrong Tools Cost Lives https://timharford.com/2021/05/cautionary-tales-wrong-tools-... If you have not listened to the series I highly recommend it. His episode "LaLa Land: Galileo’s Warning" is by far one of my favorite pieces of media. In brief it is why redundant tightly coupled fail safes will often lead to cascading failures. https://timharford.…

I really like this series and I can wholeheartedly recommend it to anyone that is on HN.
Post reply on HN