Re: making flexible table formulas

[Nick Dokos <nicholas.dokos@hp.com>]

Rustom Mody <rustompmody@gmail.com> wrote:

> Nick Dokos wrote:
> 
> > Of course it's reasonable - and of course, org implements it
> > #+TBLFM: $LR2=vsum(@1..@-1)
> 
> Thanks Nick I can use that. But I dont understand it.  What's the -1?
> The manual says -- relative to 'current' column.
> What determines 'current?' There must be some obvious POV which I am missing...
> 

The formula calculates $LR2: the cell in the last row, column 2.
That's what determines 'current'. Since there is no column spec
on the right-hand side, column 2 is implied. IOW, the formula
is equivalent to this:

#+TBLFM: $LR2=vsum(@1$2..@-1$2)

The vsum on the RHS ranges then from row 1, column 2 (@1$2)
to the row above the last one, still on column 2 (@-1$2).

BTW, if you have a header you should start at row 2 (separator
lines don't count):

|     A |  B |
|-------+----|
|     1 |  1 |
|     2 |  4 |
|     3 |  9 |
| Total | 14 |
#+TBLFM: $LR2=vsum(@2..@-1)

or use the alternative syntax that Luke Crook suggested [fn:1]

#+TBLFM: $LR2=vsum(@I..@-1)

which goes from the first separator (@I) to the penultimate row
(@-1) - and remember that separator lines are ignored in the
calculation.

HTH, [fn:2]
Nick

Footnotes:
[fn:1] Luke suggested the following formula:

#+TBLFM: @8$2=vsum(@I..@II)

with the assumption that there will be two separator lines,
one after the header and one just before the last row,
in effect demarcating the useful part of the table:


|     A |  B |
|-------+----|
|     1 |  1 |
|     2 |  4 |
|     3 |  9 |
|-------+----|
| Total | 14 |
#+TBLFM: $LR2=vsum(@I..@II)

[Luke's LHS fixes the row to 8 which is not correct - I corrected
that in the above formula.]

The only problem with this is if you decide to insert more separators in
the table or you don't want separators at all; but assuming that you can
live with the two (and only those two), it's a good solution.

[fn:2] BTW, you can turn on formula debugging from the Tbl menu or with C-c {
and see what it is calculating: I used a slighty different table - added a few 
more rows with S-RET in column 1 and I also had a formula to calculate
the squares in column 2, so the table looked like this:

|     a | b |
|-------+---|
|     1 |   |
|     2 |   |
|     3 |   |
|     4 |   |
|     5 |   |
|     6 |   |
|-------+---|
| Total |   |

#+TBLFM: $LR2=vsum(@2..@-1)::$2 = pow($1, 2)

Then pressing C-c C-c on the #+TBLFM line and saying yes a few times,
you finally get this 

|     a |  b |
|-------+----|
|     1 |  1 |
|     2 |  4 |
|     3 |  9 |
|     4 | 16 |
|     5 | 25 |
|     6 | 36 |
|-------+----|
| Total | 91 |

#+TBLFM: $LR2 = vsum(@2..@-1)::$2 = pow($1, 2)

with the following in the formula debugging buffer:

,----
| Substitution history of formula
| Orig:   vsum(@2..@-1)
| $xyz->  vsum(@2..@-1)
| @r$c->  vsum([1,4,9,16,25,36])
| $1->    vsum([1,4,9,16,25,36])
| Result: 91
| Format: NONE
| Final:  91
`----