how to clear computed values

how to clear computed values

[Cecil Westerhof]

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'.

-- 
Cecil Westerhof <CecilWesterhof@xs4all.nl>

Re: 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

Re: how to clear computed values

[Cecil Westerhof]

Op wo, 11-07-2007 te 08:15 +0200, schreef Carsten Dominik:
> 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.

That is a good tip. ;-}


> 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".

That explains my problems.


> 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

That works perfect.


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

That is also a nice tool yes.


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

That is also very usefull.


> Hope this helps.

It certainly did.

I made another improvement. It would be nice to have a notification when
there is a problem with kmstand.
I made the folowing:
        |---+------------+---------+-------------------+---------+--------+-------+----------+--------|
        |   |      datum | kmstand | km's              | prijs/l | liters | prijs | prijs/km |   km/l |
        |---+------------+---------+-------------------+---------+--------+-------+----------+--------|
        | # |            |  155111 | Verkeerde kmstand |         |        |       |          |        |
        | # | 2007-07-09 |  156146 | 1035              |   102.2 |  62.25 | 63.62 |    6.147 | 16.627 |
        | # |            |    6953 | Verkeerde kmstand |         |        |       |          |        |
        | # |            |         |                   |         |        |       |          |        |
        |---+------------+---------+-------------------+---------+--------+-------+----------+--------|
        #+TBLFM: $4='(if (and (nz @-1$3) (nz $3)) (if (> $3 @-1$3) (- $3 @-1$3) "Verkeerde kmstand") "");N::$7='(if (and (nz $5) (nz $6)) (format "%.2f" (/ (* $5 $6) 100)) "");N::$8='(if (and (nz $4) (nz $7)) (format "%.3f" (/ (* $7 100) $4)) "");N::$9='(if (and (nz $4) (nz $6)) (format "%.3f" (/ $4 $6)) "");N
In this example the first two digits of kmstand where not input at row 3
and you get a message. But you get also a message at the first row. Is
it possible to circumvent this?

Is it possible to define functions in an org-file? For example: it would
be nice to have more extensive checking on the values. Also the nz could
be defined. It is in my .emacs now, but when I transfer the file to
another computer, there is a risk that it doe not work anymore.

It would be nice if I did input kmstand that with tab you go to prijs/l.
(Skipping fields with formula's.) And when giving tab on the field
liters going to the field kmstand. Is that something that could be made?

-- 
Cecil Westerhof <CecilWesterhof@xs4all.nl>

Re: how to clear computed values

[Carsten Dominik]

On Jul 11, 2007, at 10:26, Cecil Westerhof wrote:
> In this example the first two digits of kmstand where not input at row 
> 3
> and you get a message. But you get also a message at the first row. Is
> it possible to circumvent this?

For example by leaving an empty row before the first data line.

>
> Is it possible to define functions in an org-file? For example: it 
> would
> be nice to have more extensive checking on the values. Also the nz 
> could
> be defined. It is in my .emacs now, but when I transfer the file to
> another computer, there is a risk that it doe not work anymore.

You can, by inserting something like

      # Local Variables:
      # eval: (defun nz (n) (not (= 0 n)))
      # eval: (defun nw (s) (string-match "\\S-" s))
      # End:

Check the variable `enable-local-eval', and beware that setting it to t
could be exploited to execute unwanted code in a file you get
from someone.

One problem would be that function definitions
are not buffer-local, so the same functions would be defined for all
buffers.  If you use functions with the same name, but different
definitions in different files, the result would depend on the order
of visiting the files.

> It would be nice if I did input kmstand that with tab you go to 
> prijs/l.
> (Skipping fields with formula's.) And when giving tab on the field
> liters going to the field kmstand. Is that something that could be 
> made?

I don't think this is necessary, and no spreadsheet behaves like this.
Just press TAB a few times.

- Carsten

Re: how to clear computed values

[Cecil Westerhof]

Op wo, 11-07-2007 te 10:49 +0200, schreef Carsten Dominik:
> On Jul 11, 2007, at 10:26, Cecil Westerhof wrote:
> > In this example the first two digits of kmstand where not input at row 
> > 3
> > and you get a message. But you get also a message at the first row. Is
> > it possible to circumvent this?
> 
> For example by leaving an empty row before the first data line.

That works. I hoped that it was possible to see you are on the first
line after a hline, but this is good enough.


> > Is it possible to define functions in an org-file? For example: it 
> > would
> > be nice to have more extensive checking on the values. Also the nz 
> > could
> > be defined. It is in my .emacs now, but when I transfer the file to
> > another computer, there is a risk that it doe not work anymore.
> 
> You can, by inserting something like
> 
>       # Local Variables:
>       # eval: (defun nz (n) (not (= 0 n)))
>       # eval: (defun nw (s) (string-match "\\S-" s))
>       # End:
> 
> Check the variable `enable-local-eval', and beware that setting it to t
> could be exploited to execute unwanted code in a file you get
> from someone.
> 
> One problem would be that function definitions
> are not buffer-local, so the same functions would be defined for all
> buffers.  If you use functions with the same name, but different
> definitions in different files, the result would depend on the order
> of visiting the files.

Better not then I think.


> > It would be nice if I did input kmstand that with tab you go to 
> > prijs/l.
> > (Skipping fields with formula's.) And when giving tab on the field
> > liters going to the field kmstand. Is that something that could be 
> > made?
> 
> I don't think this is necessary, and no spreadsheet behaves like this.
> Just press TAB a few times.

Cetrainly not. Just nice to have. Maybe something to try myself to
build.

-- 
Cecil Westerhof <CecilWesterhof@xs4all.nl>

Re: how to clear computed values

[Carsten Dominik]

On Jul 11, 2007, at 11:02, Cecil Westerhof wrote:

> Op wo, 11-07-2007 te 10:49 +0200, schreef Carsten Dominik:
>> On Jul 11, 2007, at 10:26, Cecil Westerhof wrote:
>>> In this example the first two digits of kmstand where not input at 
>>> row
>>> 3
>>> and you get a message. But you get also a message at the first row. 
>>> Is
>>> it possible to circumvent this?
>>
>> For example by leaving an empty row before the first data line.
>
> That works. I hoped that it was possible to see you are on the first
> line after a hline, but this is good enough.

Of course this is possible, you are using Lisp:

(defun check-if-after-hline ()
	(save-excursion
       (beginning-of-line 0)
       (looking-at org-table-hline-regexp)))

- Carsten

Re: how to clear computed values

[Cecil Westerhof]

Op wo, 11-07-2007 te 12:19 +0200, schreef Carsten Dominik:
> > That works. I hoped that it was possible to see you are on the first
> > line after a hline, but this is good enough.
> 
> Of course this is possible, you are using Lisp:
> 
> (defun check-if-after-hline ()
> 	(save-excursion
>        (beginning-of-line 0)
>        (looking-at org-table-hline-regexp)))

Okay, now it is:
        |---+------------+---------+-------------------+---------+--------+-------+----------+--------|
        |   |      datum | kmstand | km's              | prijs/l | liters | prijs | prijs/km |   km/l |
        |---+------------+---------+-------------------+---------+--------+-------+----------+--------|
        | # |            |  155111 |                   |         |        |       |          |        |
        | # | 2007-07-09 |  156146 | 1035              |   102.2 |  62.25 | 63.62 |    6.147 | 16.627 |
        | # | 2007-07-11 |    6953 | Verkeerde kmstand |         |        |       |          |        |
        | # |            |         |                   |         |        |       |          |        |
        |---+------------+---------+-------------------+---------+--------+-------+----------+--------|
        #+TBLFM: $4='(if (and (nz @-1$3) (nz $3)) (if (> $3 @-1$3) (- $3 @-1$3) (if (not (check-if-after-hline)) "Verkeerde kmstand" "")) "");N::$7='(if (and (nz $5) (nz $6)) (format "%.2f" (/ (* $5 $6) 100)) "");N::$8='(if (and (nz $4) (nz $7)) (format "%.3f" (/ (* $7 100) $4)) "");N::$9='(if (and (nz $4) (nz $6)) (format "%.3f" (/ $4 $6)) "");N
This looks perfect.

Just one more question. ;-}
I would like to display the days between rows. I found time-date.el, but
this only works with:
        The five data representations used are the following:
        
        date
                An RFC822 (or similar) date string. For instance: "Sat
                Sep 12 12:21:54 1998 +0200". 
                
        time
                An internal Emacs time. For instance: (13818 26466). 
                
        seconds
                A floating point representation of the internal Emacs
                time. For instance: 905595714.0. 
                
        days
                An integer number representing the number of days since
                00000101. For instance: 729644. 
                
        decoded time
                A list of decoded time. For instance: (54 21 12 12 9
                1998 6 t 7200).

Is there a way to work with dates like 2007-09-11?

-- 
Cecil Westerhof <CecilWesterhof@xs4all.nl>

Re: how to clear computed values

[Carsten Dominik]

>
> Just one more question. ;-}
> I would like to display the days between rows. I found time-date.el, 
> but
> this only works with:
>         The five data representations used are the following:
>
>         date
>                 An RFC822 (or similar) date string. For instance: "Sat
>                 Sep 12 12:21:54 1998 +0200".
>
>         time
>                 An internal Emacs time. For instance: (13818 26466).
>
>         seconds
>                 A floating point representation of the internal Emacs
>                 time. For instance: 905595714.0.
>
>         days
>                 An integer number representing the number of days since
>                 00000101. For instance: 729644.
>
>         decoded time
>                 A list of decoded time. For instance: (54 21 12 12 9
>                 1998 6 t 7200).
>
> Is there a way to work with dates like 2007-09-11?

You can use parse-time-string which can handle this.  It returns
a decoded time.

Even more general:
Org-mode has a very general time parsing function, org-read-date.  It
gobbles pretty much anything that looks like a date and/or date.
It normally reads from the user input, but you can also make it
read from a string:

(org-read-date t t "2007-02-03")

This will return an internal emacs time, time-date can take it from
there, for example use time-to-days to get an absolute day number.

- Carsten

Re: how to clear computed values

[Cecil Westerhof]

Op di, 10-07-2007 te 20:38 +0200, schreef Cecil Westerhof:
> 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'.

I have been playing a little bit more and made this:
        |------------+---------+--------+--------+--------+---------+---------+--------+-------+----------+--------|
        |      datum | kmstand |        |        |        |    km's | prijs/l | liters | prijs | prijs/km |   km/l |
        |------------+---------+--------+--------+--------+---------+---------+--------+-------+----------+--------|
        |            |  155111 | 155111 | 155111 | 155111 |       0 |         |        |  0.00 |   -0.000 |  0.000 |
        | 2007-07-09 |  156146 | 155111 | 156146 | 156146 |    1035 |   102.2 |  62.25 | 63.62 |    6.147 | 16.627 |
        |            |         | 156146 |      0 |      0 | -156146 |         |        |  0.00 |   -0.000 |  0.000 |
        |            |         |      0 |      0 |      0 |       0 |         |        |  0.00 |   -0.000 |  0.000 |
        |------------+---------+--------+--------+--------+---------+---------+--------+-------+----------+--------|
        #+TBLFM: $3=@-1$2;N::$4=$2::$5='(and @-1$2 $2);N::$6='(if (and @-1$2 $2) (- $2 @-1$2) (0));N::$9='(and $7 $8 (/ (* $7 $8) 100));N%.2f::$10='(and $6 $9 (/ (* $9 100) $6));N%.3f::$11='(and $6 $8 (/ $6 $8));N%.3f

If you look at the km's column, I would expect that in the thirth row 0
would be displayed. (The else part.) But the negative value of @-1$2 is
displayed. I think this is a bug?

Also when adding the fields between kmstand an km's in the km formula
@-1$-1 did not change. I would expect it to change to @-1$-4. Or is
there a reason for this behaviour?

-- 
Cecil Westerhof <CecilWesterhof@xs4all.nl>