Precision in the spreadsheet

Precision in the spreadsheet

[Marcin Borkowski]

Hi all,

is it at all possible to set different precision for display and
calculations in Org's spreadsheet?  I mean, things like $1+$2;%.2f (as
in the manual) mean (IIUC) that (1) Org should show 2 decimal figures
and (2) further calculations which use this value also use this
approximation.  Am I right?  If yes, is there a way to change this
behavior?  (I suspect not, but I wanted to make sure.)

Best,

-- 
Marcin Borkowski
http://octd.wmi.amu.edu.pl/en/Marcin_Borkowski
Faculty of Mathematics and Computer Science
Adam Mickiewicz University

Re: Precision in the spreadsheet

[Nick Dokos]

Marcin Borkowski <mbork@mbork.pl> writes:

> Hi all,
>
> is it at all possible to set different precision for display and
> calculations in Org's spreadsheet?  I mean, things like $1+$2;%.2f (as
> in the manual) mean (IIUC) that (1) Org should show 2 decimal figures
> and (2) further calculations which use this value also use this
> approximation.  Am I right?  If yes, is there a way to change this
> behavior?  (I suspect not, but I wanted to make sure.)
>

I believe that internally, calculations are done in 12-decimal place
precision: the %.2f is for display only.

See org-calc-default-modes for details.

--
Nick

Re: Precision in the spreadsheet

[Eric S Fraga]

On Tuesday,  1 Mar 2016 at 11:59, Nick Dokos wrote:
> Marcin Borkowski <mbork@mbork.pl> writes:
>
>> Hi all,
>>
>> is it at all possible to set different precision for display and
>> calculations in Org's spreadsheet?  I mean, things like $1+$2;%.2f (as
>> in the manual) mean (IIUC) that (1) Org should show 2 decimal figures
>> and (2) further calculations which use this value also use this
>> approximation.  Am I right?  If yes, is there a way to change this
>> behavior?  (I suspect not, but I wanted to make sure.)
>>
>
> I believe that internally, calculations are done in 12-decimal place
> precision: the %.2f is for display only.

Actually, surprisingly (to me), it appears not.  This simple example
gives different results depending on the formatting of the first entry:

#+begin_src org
  ,* table
  | 0.1 |
  |  1. |
  | 10. |
  ,#+TBLFM: @1$1=1/8;%.1f::@2$1=@-1*10::@3$1=@-1*10
#+end_src

-- 
: Eric S Fraga (0xFFFCF67D), Emacs 25.0.91.1, Org release_8.3.4-626-gb62d55

Re: Precision in the spreadsheet

[Nick Dokos]

Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> On Tuesday,  1 Mar 2016 at 11:59, Nick Dokos wrote:
>> Marcin Borkowski <mbork@mbork.pl> writes:
>>
>>> Hi all,
>>>
>>> is it at all possible to set different precision for display and
>>> calculations in Org's spreadsheet?  I mean, things like $1+$2;%.2f (as
>>> in the manual) mean (IIUC) that (1) Org should show 2 decimal figures
>>> and (2) further calculations which use this value also use this
>>> approximation.  Am I right?  If yes, is there a way to change this
>>> behavior?  (I suspect not, but I wanted to make sure.)
>>>
>>
>> I believe that internally, calculations are done in 12-decimal place
>> precision: the %.2f is for display only.
>
> Actually, surprisingly (to me), it appears not.  This simple example
> gives different results depending on the formatting of the first entry:
>
> #+begin_src org
>   ,* table
>   | 0.1 |
>   |  1. |
>   | 10. |
>   ,#+TBLFM: @1$1=1/8;%.1f::@2$1=@-1*10::@3$1=@-1*10
> #+end_src

Yes, I got it wrong: internal calculations may be done in extended
precision, but once the result is in the table, that's that. Achim
explains the situation (and potential solutions) clearly in his
response.

--
Nick

Re: Precision in the spreadsheet

[Achim Gratz]

Nick Dokos writes:
> Marcin Borkowski <mbork@mbork.pl> writes:
>> is it at all possible to set different precision for display and
>> calculations in Org's spreadsheet?  I mean, things like $1+$2;%.2f (as
>> in the manual) mean (IIUC) that (1) Org should show 2 decimal figures
>> and (2) further calculations which use this value also use this
>> approximation.  Am I right?  If yes, is there a way to change this
>> behavior?  (I suspect not, but I wanted to make sure.)

I guess everything is somehow possible, but it either requires to store
the data with full precision somewhere or re-calculate the table with
full precision each time and only then apply the display rounding.  I've
had this idea for some time that tables should have a shadow that
determines formatting and other behaviour, there should probably be a
way to keep raw data in the shadow as well.  Meanwhile you could
experiment with putting the data in a table somewhere with full
precision and use Babel to display that data somewhere else the way you
want it.  If you wanted to be able to edit some data in that second
table it would be more bothersome.

> I believe that internally, calculations are done in 12-decimal place
> precision: the %.2f is for display only.

Sure, but as far as org-table is concerned, the data that is displayed
is the data that gets used:

* Table precision
|  1 | 0.00 |  0. |
|  2 | 0.00 |  0. |
|  3 | 0.00 |  0. |
|  4 | 0.00 |  0. |
|  5 | 0.01 | 10. |
|  6 | 0.01 | 10. |
|  7 | 0.01 | 10. |
|  8 | 0.01 | 10. |
|  9 | 0.01 | 10. |
| 10 | 0.01 | 10. |
| 11 | 0.01 | 10. |
| 12 | 0.01 | 10. |
| 13 | 0.01 | 10. |
| 14 | 0.01 | 10. |
| 15 | 0.01 | 10. |
#+TBLFM: $2=$1/1000;%.2f::$3=1000*$2

When calculating the third column, the formula to calculate the second
column doesn get re-evaluated to regain the precision lost while
displaying it.  Org also doesn't keep shadow data and computes the
display on the fly like most other spreadsheets do.


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

SD adaptations for Waldorf Q V3.00R3 and Q+ V3.54R2:
http://Synth.Stromeko.net/Downloads.html#WaldorfSDada