emacs-orgmode
[Top][All Lists]

## Re: [Orgmode] how to clear computed values

 From: Carsten Dominik Subject: Re: [Orgmode] how to clear computed values Date: Wed, 11 Jul 2007 08:15:25 +0200

```
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

```