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

251–260 of 282 posts

Re: The Tyranny of Spreadsheets

#251

Post by dgudkov »

hcarvalhoalves wrote: post #242
dgudkov wrote: post #177

Earlier quoted context omitted.

>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…

Spreadsheets provide a spatial mapping (cells) to represent data transformations over time. Users are more comfortable working w/ spatial paradigms, as humans have a better sense of location in space than in time. Thinking in terms of "transformations over time" is difficult to reason about, specially in programming.

The point about spatial representation is spot on. The problem is that in Excel calculations tend to be presented as huge unreadable formulas with no intermediate steps. Thus the spatial representation actually only covers the first and the last step, but rarely something in between.

Re: The Tyranny of Spreadsheets

#252

Post by dgudkov »

tomnipotent wrote: post #188
dgudkov wrote: post #177

Earlier quoted context omitted.

>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 trai…

The link leads to an article on PowerQuery which is a Power BI product. PowerQuery can surely do joins, but it doesn't operate within Excel's data model - cells and sheets. It's an external tool from the Power BI family from which integrates with Excel to a certain extent.

In a similar fashion you could've posted a link to running an SQL join query from Excel (which is surely doable for a trained user). Technically it's possible, but it would be wrong to pretend like the join is done in Excel.

Re: The Tyranny of Spreadsheets

#253

Post by Ntrails »

stjohnswarts wrote: post #241
dgudkov wrote: post #177

Earlier quoted context omitted.

>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…

sometimes all you have is a hammer and a screw though. Not everyone is a computer scientist.

Sometimes I want to do something quickly, and I dump 20,000 rows from a db query into csv/excel and fiddle. Tableau is often nicer for pure visualisations.

The downside is that something useful you make keeps getting extended and added on to. It is hard to identify when you should switch and do that extra work to move it. And then, you know, tech debt forevs

Re: The Tyranny of Spreadsheets

#254

Post by tomnipotent »

dgudkov wrote: post #252
tomnipotent wrote: post #188

Earlier quoted context omitted.

> 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 trai…

The link leads to an article on PowerQuery which is a Power BI product. PowerQuery can surely do joins, but it doesn't operate within Excel's data model - cells and sheets. It's an external tool from the Power BI family from which integrates with Excel to a certain extent. In a similar fashion you could've posted a link to running an SQL join query from Excel (which is surely doable for a trained user). Technically i…

> but it doesn't operate within Excel's data model

Power Query is an Excel add-on, and has been around much longer than Power BI and has nothing to do with it. This has been possible for almost a decade, since Excel integrated a robust in-memory columnar database capable of dealing with millions of rows.

> it would be wrong to pretend like the join is done in Excel

The join is done in Excel, I don't need to pretend. That you think Excel is just a bunch of sheets and cells is your own mistaken mental model of what the program is and can do.

Here's another reference:

"Now that Excel has a built-in Data Model"

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

Re: The Tyranny of Spreadsheets

#255

Post by dgudkov »

monocasa wrote: post #185
dgudkov wrote: post #177

Earlier quoted context omitted.

>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 lang…

>you break the transformation across many cells

In theory, yes. But the reality, nobody does that unless an error is apparent and needs to be investigated. The vast majority of calculations in Excel spreadsheets are done using cumbersome, multi-line, unreadable formulas that try to pack as much logic as possible into one expression. Such obscure formulas are the main reason why lots of business-critical Excel spreadsheets contain grave errors, many of which are are never discovered.

Re: The Tyranny of Spreadsheets

#256

Post by dgudkov »

tomnipotent wrote: post #254
dgudkov wrote: post #252

Earlier quoted context omitted.

The link leads to an article on PowerQuery which is a Power BI product. PowerQuery can surely do joins, but it doesn't operate within Excel's data model - cells and sheets. It's an external tool from the Power BI family from which integrates with Excel to a certain extent. In a similar fashion you could've posted a link to running an SQL join query from Excel (which is surely doable for a trained user). Technically i…

> but it doesn't operate within Excel's data model Power Query is an Excel add-on, and has been around much longer than Power BI and has nothing to do with it. This has been possible for almost a decade, since Excel integrated a robust in-memory columnar database capable of dealing with millions of rows. > it would be wrong to pretend like the join is done in Excel The join is done in Excel, I don't need to pretend.…

Can you point me to an article that shows a simple way how to do a simple SQL-like join (no aggregation) of two regions in the same spreadsheet? Two source tables, and one result table - all in the same spreadsheet.

Something that is as simple as this: https://www.youtube.com/watch?v=RYCtoRTEk84

Re: The Tyranny of Spreadsheets

#257

Post by tomnipotent »

dgudkov wrote: post #256
tomnipotent wrote: post #254

Earlier quoted context omitted.

> but it doesn't operate within Excel's data model Power Query is an Excel add-on, and has been around much longer than Power BI and has nothing to do with it. This has been possible for almost a decade, since Excel integrated a robust in-memory columnar database capable of dealing with millions of rows. > it would be wrong to pretend like the join is done in Excel The join is done in Excel, I don't need to pretend.…

Can you point me to an article that shows a simple way how to do a simple SQL-like join (no aggregation) of two regions in the same spreadsheet? Two source tables, and one result table - all in the same spreadsheet. Something that is as simple as this: https://www.youtube.com/watch?v=RYCtoRTEk84

I sent you two links with information, including images that show the process.

Here's a video of the process:

https://support.microsoft.com/en-us/office/merge-queries-and...

Here's another video:

https://www.youtube.com/watch?v=BV3srtI20Bo

Re: The Tyranny of Spreadsheets

#258

Post by CRConrad »

nicoffeine wrote: post #125
asdff wrote: post #39

Earlier quoted context omitted.

I don't know why we think the user is some child and try to abstract away any and all complexity from their job, which ends up leading to some proprietary solution with huge inefficiencies somewhere compared to a flat file and a script in python or R. Software like excel is often seen as a way to do stuff you could do in R but without having to write code. Imo that thinking is wrong because it makes this assumption t…

> I'd guess it would be up to business programs and accounting programs and all these other college majors to actually teach classes in python and R, rather than what they do now which is teach classes in excel. Imo the entrenchment in excel is rooted in ignorance to other (often simpler) options available with python or R, than in any actual critical proprietary features offered by excel. Every time a business user…

> Microsoft (and I am no fan) has the best tool for data manipulation that exists for most of humanity.

Sigh... "Microsoft has". Time was, spreadsheets was a generic category of software, made by lots of different software makers. Nowadays, it feels like people not only don't know this, but can hardly even conceive of the possibility that "spreadsheet" could mean something other than "Excel". At least I think that more people are still able to think of, say, word processors or presentation software as not necessarily Microsoft products; the spreadsheets battle seems to have been their most crushing victory in the "Office" or "productivity software" wars.

It's sad not only for the IT world in general or from a free-market perspective, but for spreadsheets in specific, too: Who knows what stuff like Quattro Pro or Lotus Improv would be capable of now, if they were still around?

Re: The Tyranny of Spreadsheets

#259

Post by CRConrad »

dsr_ wrote: post #205
zoomastigophore wrote: post #189

Earlier quoted context omitted.

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.

What do we know about most database projects? They need CRUD plus queries. What's the easiest way to generate a CRUD form? HyperCard. We need a ubiquitous HyperCard equivalent that talks to the Real Database(TM) of your choice on the backend -- sqlite, postgresql, mysql/mariadb, Oracle, MS-SQL, whatever -- with easy functions for "get a sequence number", "get a unique identifier cookie" and "do this block atomically"…

Delphi has been around (and better than HyperCard) for twenty-five years. Free Pascal for at least as long, but it has only had the visual component aspect provided by Lazarus for ten or fifteen years.

Re: The Tyranny of Spreadsheets

#260

Post by CRConrad »

catherd wrote: post #183
HPsquared wrote: post #169

Earlier quoted context omitted.

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.

> real-Excel

???

:-(

Post reply on HN