Re: [O] table spreadsheet problem

[Nick Dokos]

Russell Adams <address@hidden> wrote:

> On Mon, Mar 19, 2012 at 04:01:42PM +0100, Martin Halder wrote:
> > Hi all,
> >
> > question about table calculation. Found in the documentation that I could 
> > refer to e.g. first line after second hline with @II+2.
> >
> > Would like to calculate sum = vsum(@address@hidden) and tax = sum * 0.08
> >
> > Am I doing sth wrong ?
> >
> > Thanks for help,
> > Martin
> >
> > | article |  price |
> > |---------+--------|
> > | item1   | 100.00 |
> > | item2   | 200.00 |
> > |---------+--------|
> > | sum     |        |
> > | tax     |        |
> > |---------+--------|
> > | sum     |      0 |
> > #+TBLFM: 
> > @II+1$2=vsum(@address@hidden);f2N::@address@hidden;f2N::@>$2=vsum(@address@hidden);f2N
> >
> >
> 
> | article |  price |
> |---------+--------|
> | item1   | 100.00 |
> | item2   | 200.00 |
> |---------+--------|
> | sum     | 300.00 |
> | tax     |  24.00 |
> |---------+--------|
> | sum     | 324.00 |
> #+TBLFM: 
> @4$2=vsum(@address@hidden);%.2f::@address@hidden;%.2f::@6$2=vsum(@address@hidden);%.2f
> 
> I often use negative headline references for total lines in order to
> sum backward from the current cell.
> 
> So vsum(@address@hidden);%.2f means sum the cells between the first headline
> above the current cell to the second headline above the current cell,
> and then format the result as a decimal with two decimal places
> (%.2f).
> 
> Looking at your formula, I think you manually wrote the TBLFM
> line. One thing I've found is that the TBLFM line doesn't support
> relative references for the cell specification, only in the formula.
> 
> I used C-u C-c = to set mine.
> 

Nowadays, you can use (both on the LHS and the RHS of the formula)
symbolic references relative to the first, second, third ... row like
this: @<, @<<, @<< ... and relative to the last, penultimate,
antepenultimate (is there such a word?) row like this: @>, @>>, @>>>
..., and similarly for columns, so you could write the formula, like
this:

#+TBLFM: 
@>>>$>=vsum(@address@hidden);%.2f::@>>$>=@>>>$>*0.08;%.2f::@>$>=vsum(@address@hidden);%.2f

This *does* require iterated evaluations, so C-u C-u C-c C-c is your
best bet, to make sure everything is updated properly.

Nick

> Good luck!
> 
> ------------------------------------------------------------------
> Russell Adams                            address@hidden
> 
> PGP Key ID:     0x1160DCB3           http://www.adamsinfoserv.com/
> 
> Fingerprint:    1723 D8CA 4280 1EC9 557F  66E8 1154 E018 1160 DCB3
>