6 ms·

The Intensional Spreadsheet

79 points by herodotus 7y ago

jkaptur 7y ago
Very cool ideas. I'm surprised that the article didn't mention R1C1 mode [0], which seems to be a big step towards the "intensional" view of a formula. 0. https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm https://bettersolutions.com/excel/formulas/cell-references-a....
- gklitt 7y ago
  I had the same thought -- it seems like many of these ideas overlap heavily with R1C1 mode, which is already how Excel internally manages formula copy-paste. Joel Spolsky explaining how it works: https://youtu.be/0nbkaYsR94c?t=567 https://youtu.be/0nbkaYsR94c?t=567
- zokier 7y ago
  I was thinking that the article overall felt bit out of touch with what Excel does these days, then I checked the date on the date on the linked original IEEE article and its from 1990, 30 years ago. No wonder it is bit difficult to place in modern context. "A while back" indeed.
  - laumars 7y ago
    R1C1 is even older still. It was first released in 1982 as the the cell addressing for Microsoft Multiplan (so we are talking pre-Excel). Microsoft only later adopted A1 formulas to fall in line with VisCal and Lotus 1-2-3.
- maxerickson 7y ago
  Yeah. Here's the fibonacci example in a column: 0 1 =R[-2]C+R[-1]C =R[-2]C+R[-1]C =R[-2]C+R[-1]C =R[-2]C+R[-1]C (at least, how Libre Office Calc shows it)
- juped 7y ago
  Yes, spreadsheets use relative references and transform them to absolute references as a display format.
- montebicyclelo 7y ago
  Seems the author of the article has now addressed this: > It’s been pointed out that you can get something close to this with R1C1 notation. R[-2]C is the cell two columns to the left on the same row. This notation is not as general; for example we can write left(F+left F). I'd argue it's more readable too.
refrigerator 7y ago
Love it — a step towards letting people do algebra instead of arithmetic (going up one level of abstraction, basically). We've implemented almost precisely this in Causal (https://causal.app https://causal.app) — models come with the 'time' dimension as a first-class citizen [1], letting you write a single formula like Revenue[t] = Revenue[t-1] * (1 + Growth Rate) to calculate the Revenue at all points in time. And you can do vectorised operations along the time dimension by writing formulas like this: Profit = Revenue - Costs where Revenue and Costs, and therefore Profit, vary over time. --- [1]: You can specify the time dimension yourself, e.g. "Monthly, Jan 2018 – Jan 2023" or "Yearly, 2020 – 2030"
- vchak1 7y ago
  Very interesting. I believe that Bret Victor does the time part really well (visually) here: http://worrydream.com/MediaForThinkingTheUnthinkable/ http://worrydream.com/MediaForThinkingTheUnthinkable/ (in demo 4). Also, Mesh apreadsheet is very interesting in this regard, especially with K (Array based language) embedded in it. You can then write a bunch of expressions as {function} scan over initial values, both time based or otherwise. http://mesh-spreadsheet.com/ http://mesh-spreadsheet.com/
- qubex 7y ago
  That reminds me a lot of Lotus Improv (somewhat before my time) and one of my favourite tools, Quantrix Modeler. There are some more modern alternatives out there that take a similar approach — I just saved a model I’m editing on my iPad in XCubes, for example.
- Lukas1994 7y ago
  I'm working with @refrigerator on Causal. We also came up with a similar way of nesting spreadsheets (here’s a demo video: https://www.loom.com/embed/0d7a784308c345e08e72451ed854c590 https://www.loom.com/embed/0d7a784308c345e08e72451ed854c590). In Causal any cell (we call them variables) can be computed by another spreadsheet (we call them models). To make this more powerful a model can have input parameters like functions in programming languages. So a model can be composed of reusable submodels. There’s a bunch of spreadsheet templates floating around the internet but there’s no way to compose them or update them. Wouldn’t it be great to have something like npm for spreadsheet models?
quantified 7y ago
The multidimensional spreadsheet has been around for a long time. The first product I worked on was one, that competed with others like Lotus Improv, CA-Compete, and one for the Mac that I believe was named FreeBase. That Computer Associates and Lotus were selling them tells you something right away. I believe it was IRI that sold Javelin, a spreadsheet with some time-series capabilities. There are/were many others too. OLAP planning product (of which I’ve worked on multiple) tend to provide a very spreadsheet-ish outlook on their UI and modeling capabilities, coupled with real data management.
- Anonymous4C54D6 7y ago
  So, what happened to all of these? I feel like a multidimensional spreadsheet would be a great alternative to things like MS Access.
  - TeMPOraL 7y ago
    I've been looking into this space a bit (after one day realizing that what I'm doing at work is essentially reinventing OLAP abstractions). The way I see it, multidimensional spreadsheeting has evolved into expensive, proprietary ERP and BI software, while the ideas from it end up being repeatedly reinvented in various "excel killer"/"more complicated CRUD" startups without ever reaching their full potential (I'm guessing that would kill the business value). The middle doesn't seem to exist (EDIT or maybe it does?[1]) - I don't know of any actual multidimensional spreadsheet that's accessible to regular people. There was Egeria[0] featured on HN some years ago, but they've seem to have reverted to a closed beta since then. -- [0] - https://news.ycombinator.com/item?id=18656746 https://news.ycombinator.com/item?id=18656746 [1] - https://news.ycombinator.com/item?id=22927875 https://news.ycombinator.com/item?id=22927875 points out https://www.xcubes.net/ https://www.xcubes.net/, which seems like the "middle" I was looking for.
    - quantified 7y ago
      The multidimensional spreadsheet is a more complex piece of software than the 2-D, and invites more data that needs to be managed and processed. So it trends to a more database-like system. There might be a niche for small and even-more-complex modeling. It’s hard to open-source something that takes the required investment.

79 points by herodotus 7y ago

Very cool ideas. I'm surprised that the article didn't mention R1C1 mode [0], which seems to be a big step towards the "intensional" view of a formula. 0. https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm https://bettersolutions.com/excel/formulas/cell-references-a....

gklitt 7y ago
I had the same thought -- it seems like many of these ideas overlap heavily with R1C1 mode, which is already how Excel internally manages formula copy-paste. Joel Spolsky explaining how it works: https://youtu.be/0nbkaYsR94c?t=567 https://youtu.be/0nbkaYsR94c?t=567
zokier 7y ago
I was thinking that the article overall felt bit out of touch with what Excel does these days, then I checked the date on the date on the linked original IEEE article and its from 1990, 30 years ago. No wonder it is bit difficult to place in modern context. "A while back" indeed.
- laumars 7y ago
  R1C1 is even older still. It was first released in 1982 as the the cell addressing for Microsoft Multiplan (so we are talking pre-Excel). Microsoft only later adopted A1 formulas to fall in line with VisCal and Lotus 1-2-3.
maxerickson 7y ago
Yeah. Here's the fibonacci example in a column: 0 1 =R[-2]C+R[-1]C =R[-2]C+R[-1]C =R[-2]C+R[-1]C =R[-2]C+R[-1]C (at least, how Libre Office Calc shows it)
juped 7y ago
Yes, spreadsheets use relative references and transform them to absolute references as a display format.
montebicyclelo 7y ago
Seems the author of the article has now addressed this: > It’s been pointed out that you can get something close to this with R1C1 notation. R[-2]C is the cell two columns to the left on the same row. This notation is not as general; for example we can write left(F+left F). I'd argue it's more readable too.

refrigerator 7y ago

Love it — a step towards letting people do algebra instead of arithmetic (going up one level of abstraction, basically). We've implemented almost precisely this in Causal (https://causal.app https://causal.app) — models come with the 'time' dimension as a first-class citizen [1], letting you write a single formula like Revenue[t] = Revenue[t-1] * (1 + Growth Rate) to calculate the Revenue at all points in time. And you can do vectorised operations along the time dimension by writing formulas like this: Profit = Revenue - Costs where Revenue and Costs, and therefore Profit, vary over time. --- [1]: You can specify the time dimension yourself, e.g. "Monthly, Jan 2018 – Jan 2023" or "Yearly, 2020 – 2030"

vchak1 7y ago
Very interesting. I believe that Bret Victor does the time part really well (visually) here: http://worrydream.com/MediaForThinkingTheUnthinkable/ http://worrydream.com/MediaForThinkingTheUnthinkable/ (in demo 4). Also, Mesh apreadsheet is very interesting in this regard, especially with K (Array based language) embedded in it. You can then write a bunch of expressions as {function} scan over initial values, both time based or otherwise. http://mesh-spreadsheet.com/ http://mesh-spreadsheet.com/
qubex 7y ago
That reminds me a lot of Lotus Improv (somewhat before my time) and one of my favourite tools, Quantrix Modeler. There are some more modern alternatives out there that take a similar approach — I just saved a model I’m editing on my iPad in XCubes, for example.
Lukas1994 7y ago
I'm working with @refrigerator on Causal. We also came up with a similar way of nesting spreadsheets (here’s a demo video: https://www.loom.com/embed/0d7a784308c345e08e72451ed854c590 https://www.loom.com/embed/0d7a784308c345e08e72451ed854c590). In Causal any cell (we call them variables) can be computed by another spreadsheet (we call them models). To make this more powerful a model can have input parameters like functions in programming languages. So a model can be composed of reusable submodels. There’s a bunch of spreadsheet templates floating around the internet but there’s no way to compose them or update them. Wouldn’t it be great to have something like npm for spreadsheet models?

quantified 7y ago

The multidimensional spreadsheet has been around for a long time. The first product I worked on was one, that competed with others like Lotus Improv, CA-Compete, and one for the Mac that I believe was named FreeBase. That Computer Associates and Lotus were selling them tells you something right away. I believe it was IRI that sold Javelin, a spreadsheet with some time-series capabilities. There are/were many others too. OLAP planning product (of which I’ve worked on multiple) tend to provide a very spreadsheet-ish outlook on their UI and modeling capabilities, coupled with real data management.

Anonymous4C54D6 7y ago
So, what happened to all of these? I feel like a multidimensional spreadsheet would be a great alternative to things like MS Access.
- TeMPOraL 7y ago
  I've been looking into this space a bit (after one day realizing that what I'm doing at work is essentially reinventing OLAP abstractions). The way I see it, multidimensional spreadsheeting has evolved into expensive, proprietary ERP and BI software, while the ideas from it end up being repeatedly reinvented in various "excel killer"/"more complicated CRUD" startups without ever reaching their full potential (I'm guessing that would kill the business value). The middle doesn't seem to exist (EDIT or maybe it does?[1]) - I don't know of any actual multidimensional spreadsheet that's accessible to regular people. There was Egeria[0] featured on HN some years ago, but they've seem to have reverted to a closed beta since then. -- [0] - https://news.ycombinator.com/item?id=18656746 https://news.ycombinator.com/item?id=18656746 [1] - https://news.ycombinator.com/item?id=22927875 https://news.ycombinator.com/item?id=22927875 points out https://www.xcubes.net/ https://www.xcubes.net/, which seems like the "middle" I was looking for.
  - quantified 7y ago
    The multidimensional spreadsheet is a more complex piece of software than the 2-D, and invites more data that needs to be managed and processed. So it trends to a more database-like system. There might be a niche for small and even-more-complex modeling. It’s hard to open-source something that takes the required investment.