Hi, I found out about org-table-sum yesterday, but I quickly ran into a problem, where the result is not right. For example: | 171.00 | | 4.07 | | 4.44 | | 2.61 | | 12.21 | | 6.69 | | 19.72 | | 23.09 | | 6.23 | | 15.28 | | 250.00 | | 250.00 | | 250.00 | | 78.85 | | | If point is at the last cell, org-table-sum evaluates to 1094.1899999999998. I did not find a way to reproduce this with other numbers, but the order seems to matter. Best wishes
I can reproduce this with org 9.3.1. I imagine the decimal numbers
have precision problems when converted to binary floating point.
A simpler table to reproduce:
| 83.6 |
| .1 |
|-------------------|
| 83.69999999999999 |
Kein Test <soetwasaberauch@gmail.com> writes:
> Hi,
>
> I found out about org-table-sum yesterday, but I quickly ran into a
> problem, where the result is not right.
>
> For example:
>
> | 171.00 |
> | 4.07 |
> | 4.44 |
> | 2.61 |
> | 12.21 |
> | 6.69 |
> | 19.72 |
> | 23.09 |
> | 6.23 |
> | 15.28 |
> | 250.00 |
> | 250.00 |
> | 250.00 |
> | 78.85 |
> | |
>
> If point is at the last cell, org-table-sum evaluates to 1094.1899999999998.
>
> I did not find a way to reproduce this with other numbers, but the
> order seems to matter.
>
> Best wishes
Kein Test writes: > Hi, > > I found out about org-table-sum yesterday, but I quickly ran into a > problem, where the result is not right. > > For example: > > | 171.00 | > | 4.07 | > | 4.44 | > | 2.61 | > | 12.21 | > | 6.69 | > | 19.72 | > | 23.09 | > | 6.23 | > | 15.28 | > | 250.00 | > | 250.00 | > | 250.00 | > | 78.85 | > | | > > If point is at the last cell, org-table-sum evaluates to 1094.1899999999998. Here's what org-table-sum does underneath: (apply #'+ (list 171.0 4.07 4.44 2.61 12.21 6.69 19.72 23.09 6.23 15.28 78.85)) ;; => 1094.1899999999998 > I did not find a way to reproduce this with other numbers, but the > order seems to matter. See <https://floating-point-gui.de/basic/>.
Kyle Meyer writes:
> Here's what org-table-sum does underneath:
>
> (apply #'+
> (list 171.0 4.07 4.44 2.61 12.21 6.69 19.72 23.09 6.23 15.28 78.85))
> ;; => 1094.1899999999998
Sorry, I pasted the wrong snippet (that one above has some numbers
pruned and returns "344.18999999999994"). Here's the snippet that would
match the your table:
(apply #'+ (list 171.0 4.07 4.44 2.61 12.21 6.69 19.72 23.09 6.23
15.28 250.0 250.0 250.0 78.85))
On Thursday, 24 Sep 2020 at 21:38, Kein Test wrote:
> I found out about org-table-sum yesterday, but I quickly ran into a
> problem, where the result is not right.
It is right and due to the use of floating point arithmetic in the
computer. If you ask org/calc to display the number with 2 decimal
places (;f2), you will have the result you expect.
--
: Eric S Fraga via Emacs 28.0.50, Org release_9.4-29-gbc9664
>>>>> On Thu, 24 Sep 2020 16:48:14 -0400, Kyle Meyer <kyle@kyleam.com> said: >> I did not find a way to reproduce this with other numbers, but the >> order seems to matter. Kyle> See <https://floating-point-gui.de/basic/>. Exactly. Which is why you should use 'calc' with floating point numbers, it handles them correctly. i.e. | 171.00 | | 4.07 | | 4.44 | | 2.61 | | 12.21 | | 6.69 | | 19.72 | | 23.09 | | 6.23 | | 15.28 | | 250.00 | | 250.00 | | 250.00 | | 78.85 | |--------| | | #+TBLFM:@>$1=vsum(@1$1..@-1$1) Put point in that empty cell and do 'C-u C-c C-c' Robert
Hello,
Thanks for the input.
From what I understand, it seems that org-table-sum is not behaving as
expected. I don't know if it would be interesting to split the function
into 2. One for summing of time values and one for summing integers?
For the sum of integers (possibly real numbers) it might be interesting
to make a function use TBLFM directly?
Best regards,
Jeremie
On Friday, 25 Sep 2020 at 10:59, Robert Pluim wrote:
>>>>>> On Thu, 24 Sep 2020 16:48:14 -0400, Kyle Meyer <kyle@kyleam.com> said:
>
> >> I did not find a way to reproduce this with other numbers, but the
> >> order seems to matter.
>
> Kyle> See <https://floating-point-gui.de/basic/>.
>
> Exactly. Which is why you should use 'calc' with floating point
> numbers, it handles them correctly. i.e.
>
> | 171.00 |
> | 4.07 |
> | 4.44 |
> | 2.61 |
> | 12.21 |
> | 6.69 |
> | 19.72 |
> | 23.09 |
> | 6.23 |
> | 15.28 |
> | 250.00 |
> | 250.00 |
> | 250.00 |
> | 78.85 |
> |--------|
> | |
> #+TBLFM:@>$1=vsum(@1$1..@-1$1)
>
> Put point in that empty cell and do 'C-u C-c C-c'
>
> Robert
>
--
Best regards
Jeremie Juste
>>>>> On Sun, 27 Sep 2020 01:07:22 +0200, Jeremie Juste <jeremiejuste@gmail.com> said:
Jeremie> Hello,
Jeremie> Thanks for the input.
Jeremie> From what I understand, it seems that org-table-sum is not behaving as
Jeremie> expected. I don't know if it would be interesting to split the function
Jeremie> into 2. One for summing of time values and one for summing integers?
I suspect that the people using org-table sum would not want to split
the function in two: itʼs a useful utility function (and why split off
the integer summing? Thatʼs always going to be accurate).
Jeremie> For the sum of integers (possibly real numbers) it might be interesting
Jeremie> to make a function use TBLFM directly?
You could make org-table-sum use calc, which would achieve the same. I
donʼt think there'd be any complaints about floating-point additions
suddenly being more accurate (famous last words)
Robert
--
Hello Robert, Thanks for sharing your thoughts. > I suspect that the people using org-table sum would not want to split > the function in two: itʼs a useful utility function (and why split off > the integer summing? Thatʼs always going to be accurate). I see your point here and I agree with you. > > You could make org-table-sum use calc, which would achieve the same. I > donʼt think there'd be any complaints about floating-point additions > suddenly being more accurate (famous last words) I have investigated a little further about using calc. I haven't come up with a solution yet. I don't know yet which function calc is using to perform this operation. I have tried calcFunc-vsum but fell back on the same issue. #+BEGIN_SRC elisp (calcFunc-vsum 85.6 .1) #+end_src -- Best regards Jeremie Juste
>>>>> On Mon, 28 Sep 2020 14:50:38 +0200, Jeremie Juste <jeremiejuste@gmail.com> said:
>>
>> You could make org-table-sum use calc, which would achieve the same. I
>> donʼt think there'd be any complaints about floating-point additions
>> suddenly being more accurate (famous last words)
Jeremie> I have investigated a little further about using calc. I haven't come up
Jeremie> with a solution yet. I don't know yet which function calc is using to
Jeremie> perform this operation. I have tried calcFunc-vsum but fell back on the
Jeremie> same issue.
Jeremie> #+BEGIN_SRC elisp
Jeremie> (calcFunc-vsum 85.6 .1)
Jeremie> #+end_src
I donʼt think calcFunc-vsum is intended to be called by lisp code
directly. Iʼd investigate how TBLFM calls it, and see if that can be
moved into org-table-sum.
Robert
--
Hello
I have figured out that calc-eval might do the job for the floating
point problem for org.
(info "(calc) Calling Calc from Your Programs")
(calc-eval "83.6+0.1")
So in the function org-table-sum I have made the following modification:
> (res (string-to-number (calc-eval (combine-and-quote-strings (mapcar 'number-to-string numbers ) "+"))))
modified lisp/org-table.el
@@ -4771,7 +4771,7 @@ If NLAST is a number, only the NLAST fields will actually be summed."
(nreverse items))))
(numbers (delq nil (mapcar #'org-table--number-for-summing
items1)))
- (res (apply '+ numbers))
+ (res (string-to-number (calc-eval (combine-and-quote-strings (mapcar 'number-to-string numbers ) "+"))))
(sres (if (= org-timecnt 0)
(number-to-string res)
(setq diff (* 3600 res)
I believe that the org-table--number-for-summing function can be
bypassed but my elisp foo is weak. Feel free to improve.
Best regards
Jeremie
Hello, I have slightly improved the org-table-sum function. It calls directly calc-eval and yield the same result as vsum. It can not handle better floating points. > | 171.00 | > | 4.07 | > | 4.44 | > | 2.61 | > | 12.21 | > | 6.69 | > | 19.72 | > | 23.09 | > | 6.23 | > | 15.28 | > | 250.00 | > | 250.00 | > | 250.00 | > | 78.85 | > |--------| > | | > #+TBLFM:@>$1=vsum(@1$1..@-1$1) diff --git a/lisp/org-table.el b/lisp/org-table.el index a3c49874c..ac237af2c 100644 --- a/lisp/org-table.el +++ b/lisp/org-table.el @@ -4728,6 +4728,18 @@ blank, and the content is appended to the field above." ((equal n 0) nil) (t n)))) + +(defun org-table-cell-no-prop (x) + "remove property from org-table-cell. If cell is empty output nil. X is a string - a cell from org-table +usage (org-table-cell-no-prop #(\"foo \" 0 2 (face default))) ==> foo +" + (interactive) + (let ( (res (replace-regexp-in-string "\s+" "" + (substring-no-properties x)))) + (when (not (equal res "")) + res) + )) + ;;;###autoload (defun org-table-sum (&optional beg end nlast) "Sum numbers in region of current table column. @@ -4769,9 +4781,9 @@ If NLAST is a number, only the NLAST fields will actually be summed." (t (setq items (reverse items)) (setcdr (nthcdr (1- nlast) items) nil) (nreverse items)))) - (numbers (delq nil (mapcar #'org-table--number-for-summing - items1))) - (res (apply '+ numbers)) + (numbers (delq nil (mapcar 'org-table-cell-no-prop items1 ))) + (res (string-to-number (calc-eval + (combine-and-quote-strings numbers "+")))) Best regards Jeremie Juste