Re: Re: org-table formulas with missing values

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

Rainer M Krug <r.m.krug@gmail.com> writes:

> On Sat, Mar 5, 2011 at 11:55 AM, Thorsten
> <gruenderteam.berlin@googlemail.com> wrote:
>> 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
>
> NaN ia, as you state, NotANumber, but it is NOT a missing value, for
> which the abbreviation is NA (Not Vavailable). An example for NaN
> would be 1/0 - the value is there, but it is not a number. And,
> consequently, NaN and NA can be treated separately. In general, if you
> enter values, you use NA, as you usually do not enter values which are
> not a number...

It seems there is no special variable 'na' in calc, and anyway, 'nan' is
not recognized either, but calc-info agrees with you. 

nil does the job - but somehow interferes with the else-part of the
formula, that is not calculated anymore:


| day | tel- calls | change (%)                                      |
|-----+------------+-------------------------------------------------|
|   1 |          7 | 0                                               |
|   2 |          2 | 2 = nil ? nil : round(((2 - 7) / 7) 100, 0)     |
|   3 |          3 | 3 = nil ? nil : round(((3 - 2) / 2) 100, 0)     |
|   4 |        nil | nil                                             |
|   5 |          2 | 2 = nil ? nil : round(((2 - nil) / nil) 100, 0) |
|   6 |          0 | 0 = nil ? nil : round(((0 - 2) / 2) 100, 0)     |
|   7 |          3 | 3 = nil ? nil : round(((3 - 0) / 0) 100, 0)     |

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

But even if nil would work, my basic question remains: if I have for
example a dynamic report were one code-block in the org file retrieves
new data frequently and stores them in a table, which has some formula
applied to it, and 0 values as well as missing values are possible, how
can I address the empty cells in the formula without confusion with
regards to the cells with 0 values?

Thorsten