Re: org-table formulas with missing values

[Thorsten <gruenderteam.berlin@googlemail.com>]

Bastien <bzg@altern.org> writes:

> Hi Thorsten,
>
> Thorsten <gruenderteam.berlin@googlemail.com> writes:
>
>> Ok, trial and error suggests that missing values in numeric columns can
>> be represented as 0 in formulas, in string columns as "". Is there
>> something like NaN in calc/org-table? 
>
> I don't know what is NaN.  Can you give an example of what you're trying
> to achieve?

NaN stands for NotaNumber and is usually used in statistic programs to
denote missing numerical values (that deserve special treatment, since
including them in the calculations as 0 values would not be wise due to
introduced bias or confusion with true measurements of a value=0).

Fot example:

| day | tel- calls |          change (%) |
|-----+------------+---------------------|
|   1 |          7 |                   0 |
|   2 |          2 |                 -71 |
|   3 |          3 |                  50 |
|   4 |            |                   0 |
|   5 |          2 | round((2/0) 100, 0) |
|   6 |          0 |                   0 |
|   7 |          3 | round((3/0) 100, 0) |

#+TBLFM: $3=if(@$-1=0,0,round((((@$-1-@-1$-1) / @-1$-1) * 100),
 0)::@2$3=@2$2-@2$2


Here one measurement (day 4) was missed, one day had 0 tel-calls (day
6). 
The change (%) is : 
((calls day x) - (calls day x-1)) / (calls day x-1) * 100. 

The column-formula treats measurment and zero value the same, although
it would be better to replace the missing value with the average (but
how to adress it in the formula if not with 0 ?), and it treats the zero
measurement like a missing value here. Even if there would be no zero
value here, the missing value would cause a 'divide by zero' error.

I could write 'nan' instead of leaving a blank, and use the string in
the formula, but this causes errors.

But calc does know the concept of nan: "The variables `inf', `uinf', and
`nan' stand for infinite or indeterminate values.  It's best not to use
them as regular variables, since Calc uses special algebraic rules when
it manipulates them.  Calc displays a warning message if you store a
value into any of these special variables."

I'm not sure how to use that nan variable in tables and formulas - it
seems not to be recognized. 

Thorsten