Re: [Orgmode] how to clear computed values

[Carsten Dominik]

On Jul 10, 2007, at 20:38, Cecil Westerhof wrote:

> I have a table like:
>         
> |---------+---------+---------+--------+-------+----------+--------|
>         | kmstand |    km's | prijs/l | liters | prijs | prijs/km |   
> km/l |
>         
> |---------+---------+---------+--------+-------+----------+--------|
>         |  155111 |       0 |         |        |  0.00 |   -0.000 |  
> 0.000 |
>         |  156146 |    1035 |   102.2 |  62.25 | 63.62 |    6.147 | 
> 16.627 |
>         |         | -156146 |         |        |  0.00 |   -0.000 |  
> 0.000 |
>         
> |---------+---------+---------+--------+-------+----------+--------|
>         #+TBLFM: $2='(and @-1$-1 $1 (- $1 @-1$-1));N::$5='(and $3 $4 
> (/ (* $3 $4) 100));N%.2f::$6='(and $2 $5 (/ (* $5 100) 
> $2));N%.3f::$7='(and $2 $4 (/ $2 $4));N%.3f
>
> When there is nothing to display, there is now a '0' displayed, but I
> would prefer it to be empty. Is this possible?
> Also, why is prijs/km displayed as '-0.000'?
> And why is the last field of km's displayed as  '-156146'? I expected
> '0'.

If you want to understand why things come out the way they
do, use formular debugging (this is an option in the menu, but
cou can also toggle it with `C-C {').  When you then execute
calculations in the table (for example with `C-u C-c C-c')
you will get detailed information about variable substitution,
and you will see the final expression that gets evaluated.

For example, you are trying things like

  (and $1 $2 ...

but of course both the empty string and the number 0 are
"true" in Lisp, the only thing false in Lisp is "nil".

If you want to fix it, you can, by *really* taking control.
For example, define the following function that tests
if a value is not zero

(defun nz (n) (not (= n 0.)))

and then write your table like this:

  |---------+------+---------+--------+-------+----------+--------|
  | kmstand | km's | prijs/l | liters | prijs | prijs/km |   km/l |
  |---------+------+---------+--------+-------+----------+--------|
  |  155111 |      |         |        |       |          |        |
  |  156146 | 1035 |   102.2 |  62.25 | 63.62 |    6.147 | 16.627 |
  |         |      |         |        |       |          |        |
  |---------+------+---------+--------+-------+----------+--------|
#+TBLFM: $2='(if (and (nz @-1$-1) (nz $1) (> $1 @-1$-1)) (- $1 @-1$-1) 
"");N::$5='(if (and (nz $3) (nz $4)) (format "%.2f" (/ (* $3 $4) 100)) 
"");N::$6='(if (and (nz $2) (nz $5)) (format "%.3f" (/ (* $5 100) $2)) 
"");N::$7='(if (and (nz $2) (nz $4)) (format "%.3f" (/ $2 $4)) "");N

Yes, those equations look longish - use the formula
editor to edit them.

Also, consider to put the "#" marker in the first column,
to get this table to recompute automatically while
you step through with TAB.  Use C-# in the first data line
to insert the "#" if you want to make sure that your
existing formulas are modified to reflect the new
column numbers.  The result will be this:

  |---+---------+------+---------+--------+-------+----------+--------|
  |   | kmstand | km's | prijs/l | liters | prijs | prijs/km |   km/l |
  |---+---------+------+---------+--------+-------+----------+--------|
  | # |  155111 |      |         |        |       |          |        |
  | # |  156146 | 1035 |   102.2 |  62.25 | 63.62 |    6.147 | 16.627 |
  | # |         |      |         |        |       |          |        |
  |---+---------+------+---------+--------+-------+----------+--------|
#+TBLFM: $3='(if (and (nz @-1$-1) (nz $2) (> $2 @-1$-1)) (- $2 @-1$-1) 
"");N::$6='(if (and (nz $4) (nz $5)) (format "%.2f" (/ (* $4 $5) 100)) 
"");N::$7='(if (and (nz $3) (nz $6)) (format "%.3f" (/ (* $6 100) $3)) 
"");N::$8='(if (and (nz $3) (nz $5)) (format "%.3f" (/ $3 $5)) "");N

Hope this helps.

- Carsten

--
Carsten Dominik
Sterrenkundig Instituut "Anton Pannekoek"
Universiteit van Amsterdam
Kruislaan 403
NL-1098SJ Amsterdam
phone: +31 20 525 7477