emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* org-table-sum
@ 2020-09-24 19:38 Kein Test
  2020-09-24 20:08 ` org-table-sum Mark Janes
                   ` (2 more replies)
  0 siblings, 3 replies; 12+ messages in thread
From: Kein Test @ 2020-09-24 19:38 UTC (permalink / raw)
  To: emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-24 19:38 org-table-sum Kein Test
@ 2020-09-24 20:08 ` Mark Janes
  2020-10-22 22:20   ` org-table-sum Jeremie Juste
  2020-09-24 20:48 ` org-table-sum Kyle Meyer
  2020-09-25  7:51 ` org-table-sum Eric S Fraga
  2 siblings, 1 reply; 12+ messages in thread
From: Mark Janes @ 2020-09-24 20:08 UTC (permalink / raw)
  To: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-24 19:38 org-table-sum Kein Test
  2020-09-24 20:08 ` org-table-sum Mark Janes
@ 2020-09-24 20:48 ` Kyle Meyer
  2020-09-24 21:07   ` org-table-sum Kyle Meyer
  2020-09-25  8:59   ` org-table-sum Robert Pluim
  2020-09-25  7:51 ` org-table-sum Eric S Fraga
  2 siblings, 2 replies; 12+ messages in thread
From: Kyle Meyer @ 2020-09-24 20:48 UTC (permalink / raw)
  To: Kein Test; +Cc: emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-24 20:48 ` org-table-sum Kyle Meyer
@ 2020-09-24 21:07   ` Kyle Meyer
  2020-09-25  8:59   ` org-table-sum Robert Pluim
  1 sibling, 0 replies; 12+ messages in thread
From: Kyle Meyer @ 2020-09-24 21:07 UTC (permalink / raw)
  To: Kein Test; +Cc: emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-24 19:38 org-table-sum Kein Test
  2020-09-24 20:08 ` org-table-sum Mark Janes
  2020-09-24 20:48 ` org-table-sum Kyle Meyer
@ 2020-09-25  7:51 ` Eric S Fraga
  2 siblings, 0 replies; 12+ messages in thread
From: Eric S Fraga @ 2020-09-25  7:51 UTC (permalink / raw)
  To: Kein Test; +Cc: emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-24 20:48 ` org-table-sum Kyle Meyer
  2020-09-24 21:07   ` org-table-sum Kyle Meyer
@ 2020-09-25  8:59   ` Robert Pluim
  2020-09-26 23:07     ` org-table-sum Jeremie Juste
  2020-10-24 15:15     ` org-table-sum Jeremie Juste
  1 sibling, 2 replies; 12+ messages in thread
From: Robert Pluim @ 2020-09-25  8:59 UTC (permalink / raw)
  To: Kyle Meyer; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-25  8:59   ` org-table-sum Robert Pluim
@ 2020-09-26 23:07     ` Jeremie Juste
  2020-09-28 12:27       ` org-table-sum Robert Pluim
  2020-10-24 15:15     ` org-table-sum Jeremie Juste
  1 sibling, 1 reply; 12+ messages in thread
From: Jeremie Juste @ 2020-09-26 23:07 UTC (permalink / raw)
  To: Robert Pluim; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-26 23:07     ` org-table-sum Jeremie Juste
@ 2020-09-28 12:27       ` Robert Pluim
  2020-09-28 12:50         ` org-table-sum Jeremie Juste
  0 siblings, 1 reply; 12+ messages in thread
From: Robert Pluim @ 2020-09-28 12:27 UTC (permalink / raw)
  To: Jeremie Juste; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-28 12:27       ` org-table-sum Robert Pluim
@ 2020-09-28 12:50         ` Jeremie Juste
  2020-09-28 13:54           ` org-table-sum Robert Pluim
  0 siblings, 1 reply; 12+ messages in thread
From: Jeremie Juste @ 2020-09-28 12:50 UTC (permalink / raw)
  To: Robert Pluim; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-28 12:50         ` org-table-sum Jeremie Juste
@ 2020-09-28 13:54           ` Robert Pluim
  0 siblings, 0 replies; 12+ messages in thread
From: Robert Pluim @ 2020-09-28 13:54 UTC (permalink / raw)
  To: Jeremie Juste; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-24 20:08 ` org-table-sum Mark Janes
@ 2020-10-22 22:20   ` Jeremie Juste
  0 siblings, 0 replies; 12+ messages in thread
From: Jeremie Juste @ 2020-10-22 22:20 UTC (permalink / raw)
  To: Mark Janes; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

* Re: org-table-sum
  2020-09-25  8:59   ` org-table-sum Robert Pluim
  2020-09-26 23:07     ` org-table-sum Jeremie Juste
@ 2020-10-24 15:15     ` Jeremie Juste
  1 sibling, 0 replies; 12+ messages in thread
From: Jeremie Juste @ 2020-10-24 15:15 UTC (permalink / raw)
  To: Robert Pluim; +Cc: Kein Test, emacs-orgmode

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


^ permalink raw reply	[flat|nested] 12+ messages in thread

end of thread, other threads:[~2020-10-24 15:16 UTC | newest]

Thread overview: 12+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2020-09-24 19:38 org-table-sum Kein Test
2020-09-24 20:08 ` org-table-sum Mark Janes
2020-10-22 22:20   ` org-table-sum Jeremie Juste
2020-09-24 20:48 ` org-table-sum Kyle Meyer
2020-09-24 21:07   ` org-table-sum Kyle Meyer
2020-09-25  8:59   ` org-table-sum Robert Pluim
2020-09-26 23:07     ` org-table-sum Jeremie Juste
2020-09-28 12:27       ` org-table-sum Robert Pluim
2020-09-28 12:50         ` org-table-sum Jeremie Juste
2020-09-28 13:54           ` org-table-sum Robert Pluim
2020-10-24 15:15     ` org-table-sum Jeremie Juste
2020-09-25  7:51 ` org-table-sum Eric S Fraga

Code repositories for project(s) associated with this inbox:

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).