5 ms·
Spreadsheets would be nicer if they had a coherent dataflow model. Some spreadsheet programs do this automatically, but it could be a great place for a visual p
by drostie 15y ago
Spreadsheets would be nicer if they had a coherent dataflow model. Some spreadsheet programs do this automatically, but it could be a great place for a visual programming language too.
One ugly pattern that I'm often doing is to set a column with values by the function "A3=A2" and dragging that down. This gives a column of constant values which are tweakable by tweaking just the first element. That's what a scalar variable looks like in a spreadsheet. I'd like a separate calculation area where I could refer to these. Also when I select a column I might right-click and choose "Reduce → Mean", and the mean of that column could appear in the scalar area too. With some visual tinkering you might even have a sort of "iframe" containing the columns and the scalars at the bottom of the page, and have the mean appear underneath the column to remind me what it's the mean of -- but it should float; I shouldn't have to move it when I want to drag-down the calculation to enter in a few more rows.
With spreadsheet logic you have a couple of atomic operations like, "this is a fixed column specified by me," or "this is a column containing every half-int from 12.5 through 28.5" and so on. You have reductions of those list types, like Mean and Sum and Length. You have transformations of the data too, like NormalDist(x, mean, stdev). And some of them are cached-recursive, e.g. the running-sum function you might use to find a balance given a transaction history:
balance[-1] = 0
balance[i] = change[i] + balance[i - 1]
Finally you've got a wide variety of visualizations of that data, which might also be linked from the "scalar area" -- in fact it might be nice to develop ultrasmall "thumbnail versions" that update dynamically as the data updates.
I think those elements are sort of the "core" of a spreadsheet and are handled woefully inadequately by Excel, which was not originally designed for the popular usage case it has become.
- gruseom 15y agoSpreadsheets would be nicer if they had a coherent dataflow model. What do you mean by "coherent dataflow model"? (I'm working on these problems - hence all the questions.) This gives a column of constant values which are tweakable by tweaking just the first element. That's what a scalar variable looks like in a spreadsheet. Why not just put the value in a cell and reference that cell absolutely? With some visual tinkering you might even have a sort of "iframe" [...] but it should float It seems that the general solution here would be (a) make it much easier to decompose a problem across multiple sheets (in your example, scalars and reductions could go in a different sheet), and (b) allowing sheets to "float" if you want them to, rather than always being in a different tab that you're forced to switch to. Does that make sense? balance[i] = change[i] + balance[i - 1] It's interesting that you single out this kind of recurrent calculation, where a later value of a column depends on an earlier value. It doesn't get mentioned very often. But it's fundamental to what spreadsheets do computationally and is the reason why parallelizing them is a lot harder than at first appears.
- andrewflnr 15y agoMaybe by "coherent dataflow model" he means some separation between what's an input to any given operation, so you can see what data is going where and how instead of it being scattered all over the place. That's what I'd like, anyway. What it looks like to me is more like a program than a table, but with really good list/table entry and flow arrangement tools. That may just be because I'm a programmer. Can you share anything more about what you're working on? These are interesting problems to me, too.
- gruseom 15y agoI'd be happy to discuss it offline if you want to email me (address in profile).
- drostie 15y agoThere's an aspect of that, too. I'm especially influenced by the circuit diagrams in G, the language which you program in when using LabVIEW. Basically most of the system, except for loops, looks like one big circuit. (Since loops are ugly because they're nonfunctional, I have wasted some time trying to think of what the Haskell version of G would look like, how you would embed monads in the circuit elements, and so forth.)
- gruseom 15y agoCan you point to an example of a nontrivial program in G that does something people would normally do in a spreadsheet?
- drostie 15y agoThe best reference I can show for the general programming model was a blog post which is not in G, but was called "Why Concatenative Programming Matters": http://evincarofautumn.blogspot.com/2012/02/why-concatenative-programming-matters.html http://evincarofautumn.blogspot.com/2012/02/why-concatenativ... which shows you how graphical diagrams can make sense out of concatenative programs. If you really want an example, then I will give you this with some caution: http://team358.org/files/programming/ControlSystem2009-/LabVIEW/MovingAverage.jpg http://team358.org/files/programming/ControlSystem2009-/LabV... The caution is that LabVIEW views a data source as a sort of continuous input stream, so that it is manipulating individual values at any given time. I would rather view vectors as more fundamental in a spreadsheet language.