And of course, Excel can flag cells that, according to its heuristics, seem to use inconsistent formulas, such as using empty cells in a summation, or having one cell in a column use a different formula.
Thanks a lot for the suggested syntax - it's really good to get some feedback. I need more time to think it through and work out exactly how this would work.
In the meantime, one solution to the problem, that I would like to implement soon, is cell mapping - in effect projecting native data structures onto grid cells. That way you can still display and modify your data in the spreadsheet but use a sensible formula like `mean(data$height)` instead of `mean(A2:A5)`. Would love comments on that idea - there's an issue on it https://github.com/stencila/stencila/issues/118
eg.
Warning: C10 has a range from A1:A9 but C9 only has B1:B8
Here's a test for the most common kind of error. If I add data in A5 and B5, will that data be represented in the chart? How about in the averages? Will I be able to even see that?
Here's the pivot: Break your data into regions, where regions have repeated elements. Something like this:
This could be used to generate: The dataset associated with that sub-block can be clearly annotated to an editing user as such, with simple tools for adding a row to that dataset, or sorting it without effecting the rest of the sheet. Within the dataset, there's no corruption of the formulas (the third row's C cant be different than the second's), you've still got your diffing (probably better because it's clear that data changed but formulas different) and it's extremely hard to make the calculated average come out wrong.Yeah, that exact representation isn't great. Maybe a "view" metaphor, so that headers and footers can be attached to the dataset instead of floating outside it. But once you've gone this direction, there's all sorts of amazing things possible by sharing datasets, linking them, transforming them, etc.