RE: [office-collab] CT and Spreadsheet tables

["Dennis E. Hamilton" <dennis.hamilton@acm.org>]

@Rob, two good points for discussion.

 1. That one can explicitly use a relative notation that is not tied to the letters-numerals coordinate scheme is valuable.   That increases the prospect of getting it right because the formula author can express something closer to the notation.  (I'm not sure the example notation works, but I get the idea.)  I also sense, from my limited head-scratching on the OpenFormula section 3.3 on non-Scalar Evaluation that there are ways where what is stored in the formula of a cell need not necessarily be the form entered or displayed, but a kind of relatively-treated form such that the evaluator will adjust what the determined scalar references are based on contextual information.

Of course, we never know that are algorithms for tracking-changes and for the consequences of changes have gotten it right, and we rely on the author of the document to notice when the result is unintended and to perform the necessary tweaking.  (And since I've never gotten my brain around 3.3, I have no idea how to factor in what tracked-changes would do there.)

 2. I think the trade-off about where the intelligence goes is more complicated.  However that trade-off is made, it has to be something that users operating at the conceptual-document level (not the XML level) can tell that the change they made is the change that is tracked and have some basis for confidence that a recipient of the result will be able to ascertain the same about what was done to the document.   It may simply not be relevant that there is a scheme that works at the XML, atomic level, when there is no way to reconcile that for what users are able to observe and manipulate.  I assert that it does not matter whether the consumer is a limited one (e.g., in a Mobile app) or in a power-house desktop system or distributed authoring system mediated by hosts in the cloud.  I suspect that auditability is a factor here, as is whatever it means to authenticate that the change-tracking I am shown is what the authors saw themselves accomplishing.  (If digital signatures come into this discussion, it gets even more challenging if there is not fidelity between producer and consumer.)

Perhaps the question here might be what are the ways that consumers can safely limit what they support or can make out of whatever some producer throws at them.  I fancy that the current system, of defaulting non-support/-understanding to an effortless treatment of the document as if all changes are accepted may be the best we can hope for as a stable condition.  That is, one can always produce a safe result by being able to simply ignore the not-understood tracked-change information and not attempt to make it visible nor offer to reject it.  It seems to me that principle, reflected in the current ODF 1.x specifications, should be clung to wholeheartedly.

 - Dennis

-----Original Message-----
From: robert_weir@us.ibm.com [mailto:robert_weir@us.ibm.com] 
Sent: Friday, April 08, 2011 10:01
To: dennis.hamilton@acm.org
Cc: 'Michael Brauer'; office-collab@lists.oasis-open.org
Subject: RE: [office-collab] CT and Spreadsheet tables

R1C1 notation helps in the case where you have a block of cells where the relative references are contained in that block, and then you insert rows or columns to the left or above that block, causing everything to shift.

So you have a column of 10,000 cells giving quantity in column A, 10,000 parallel cells in B giving item cost, and 10,000 more in column C multiplying the two.  You insert a new column before A, and all of the formulas are rewritten in C, if you used traditional notation.  So what was =A1*B1 now becomes =B1*C1.  But with R1C1 notation these relative references would remain the same = RC[-2]*RC[-1].

However, changes within the block are going to be messy with either notation.  But even then R1C1 usually reduces the number of changes.  And I can't think of any case where it would make it worse.

In any case, my takeaway is that one aspect of the generic proposal is that a single user operation that results in a large number of markup-level changes can be verbose when encoding the change track. Search & replace was the other example given.

On the other hand, the generic approach requires much less intelligence from the application which wishes to process the changes.  With a generic approach, you could have a simple application apply such changes consistently, without having a lot of spreadsheet domain know-how.  This is interesting when you consider the realm of applications beyond traditional heavyweight desktop editors, say mobile editors, toolkits, etc.

I don't think any of the two proposals are necessary incorrect.  It comes down to a question: Where do you put the intelligence in the system?  Do you assume intelligent writers who produce documents that have the change tracking state recorded in detail and then are processable generically by relatively simple readers?  Or do you allow document writers to encode the intent of a change track at a high level and then assume a more powerful reading application that has the intelligence to interpret and apply high-level application notations of tracked changes?  However you do it, some code -- either the reader or the writer -- needs to do the heavy lifting.  You can't avoid that.

-Rob



"Dennis E. Hamilton" <dennis.hamilton@acm.org> wrote on 04/08/2011
12:27:12 PM:

> 
> RE: [office-collab] CT and Spreadsheet tables
> 
> My impression is that the RiCj notation as currently used is subject 
> to exactly the same sort of rewriting when the formula moves 
> (relativistically speaking), with $ and additional rules determining 
> how things work when entire (blocks of) rows and columns are inserted, 
> deleted or moved.
> 
> It seems that the advantage of RiCj is that the forms can be 
> synthesized more easily in deriving references from indexes of cells, 
> rows, and columns.  Of course, one also needs a way to prevent 
> ambiguity with the existing letters-digits form in the case that the 
> row or the column part is omitted, since Ri and Cj alone are 
> problematic.
> 
> -----Original Message-----
> From: robert_weir@us.ibm.com [mailto:robert_weir@us.ibm.com]
> Sent: Friday, April 08, 2011 05:51
> To: Michael Brauer
> Cc: office-collab@lists.oasis-open.org
> Subject: Re: [office-collab] CT and Spreadsheet tables
> 
> Michael Brauer <michael.brauer@oracle.com> wrote on 04/08/2011 
> 01:54:33
> AM:
> 
> > 
> > If you insert a row or column into a speadsheet, office applications 
> > update the formulas in the cells so that they still refer to the 
> > original ones. For instance, if you have a formula referencing B1 
> > and insert a column as first column, then that reference becomes C1 
> > because columns B becomes column C. In the worst case, all formulas 
> > are adapted.
> > 
> > But maybe this could also be resolved by omitting the formulas in 
> > the recalculated cells.
> > 
> 
> Another solution would be for ODF to allow the use of R1C1 cell 
> address notation. That is out of scope for this SC, but if the TC 
> adopted R1C1 notation, then insertions like this would leave most cell 
> addresses unchanged.
> 
> See:  
> http://smurfonspreadsheets.wordpress.com/2007/11/12/r1c1-notation/
> 
> This might be a general topic for ODF 1.3 -- are there things we can 
> do to enable more efficient storage of large tables, especially now 
> that we see million-row spreadsheet documents.
> 
> -Rob
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe from this mail list, you must leave the OASIS TC that 
> generates this mail.  Follow this link to all your TCs in OASIS at:
> https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php
> 


---------------------------------------------------------------------
To unsubscribe from this mail list, you must leave the OASIS TC that generates this mail.  Follow this link to all your TCs in OASIS at:
https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php