RE: [office-collab] CT and Spreadsheet tables

[robert_weir@us.ibm.com]

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 
>