* tables: is it possible to use a comma instead of a period as a number delimiter
@ 2011-05-15 23:12 Izzie
2011-05-16 10:05 ` Michael Brand
0 siblings, 1 reply; 8+ messages in thread
From: Izzie @ 2011-05-15 23:12 UTC (permalink / raw)
To: emacs-orgmode
Hi,
I started using org tables including a column of numbers formatted the European
way with a comma instead of a period, for example 127,43 for 127.43.
When I use a formula to sum the whole column it expect a period and ends up with
a false calculation. I'd revert my numbers to the American format but this table
is used for accounting so it's not an option. Going through the manual didn't
provide any help.
Is there a way to have org use the european format or am I to ditch the formula
and make use of C-c + to manually calculate the sum ?
Izzie
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-15 23:12 tables: is it possible to use a comma instead of a period as a number delimiter Izzie
@ 2011-05-16 10:05 ` Michael Brand
2011-05-16 11:26 ` Christian Moe
0 siblings, 1 reply; 8+ messages in thread
From: Michael Brand @ 2011-05-16 10:05 UTC (permalink / raw)
To: Izzie; +Cc: emacs-orgmode
Hi Izzie
The only direct solution I can think of now is with Emacs Lisp for
number/string conversion and ./, replacement:
#+begin_src emacs-lisp :results silent
(defun com2num (com)
"convert number string with comma like \"2,3\" to number like 2.3"
(string-to-number (replace-regexp-in-string "," "." com)))
(defun num2com (fmt num)
"convert number like 2.300001 to number string with comma
like \"2,3\", formatted with fmt like \"%.1f\""
(replace-regexp-in-string "\\." "," (format fmt num)))
#+end_src
| <r> |
|------|
| 10,2 |
| 3,0 |
| 5,6 |
|------|
| 18,8 |
#+TBLFM: @5='(num2com "%.1f" (apply '+ (mapcar 'com2num '(@2..@4))))
Michael
On Mon, May 16, 2011 at 01:12, Izzie <ml_orgmode.kapush@antichef.net> wrote:
> I started using org tables including a column of numbers formatted the European
> way with a comma instead of a period, for example 127,43 for 127.43.
>
> When I use a formula to sum the whole column it expect a period and ends up with
> a false calculation. I'd revert my numbers to the American format but this table
> is used for accounting so it's not an option. Going through the manual didn't
> provide any help.
>
> Is there a way to have org use the european format or am I to ditch the formula
> and make use of C-c + to manually calculate the sum ?
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-16 10:05 ` Michael Brand
@ 2011-05-16 11:26 ` Christian Moe
2011-05-16 12:20 ` Carsten Dominik
0 siblings, 1 reply; 8+ messages in thread
From: Christian Moe @ 2011-05-16 11:26 UTC (permalink / raw)
To: Michael Brand; +Cc: emacs-orgmode, Izzie
Hi,
This seems to be a limitation in Emacs Calc, on which Org table
spreadsheet functions are based. Calc accepts only the dot as decimal
point for number *entry*. However, you can have it *display* the point
as you like by customizing calc-point-char (that's `d .' in a Calc
buffer).
In Org, you *could* try this:
(setq org-calc-default-modes
(append org-calc-default-modes '(calc-point-char ",")))
However, it looks like this creates more problems than it's worth.
Commas remain useless for data entry, including entry from Org table
cells, so you would need to keep a source column with dots, and copy
the numbers over to the column you want to display. And you could only
do math on the source column. E. g., the following would work for a
column of numbers to be summed at the bottom line:
| Point | Comma |
|-------+-------|
| 2.3 | |
| 11.3 | |
| 2.1 | |
|-------+-------|
| | |
#+TBLFM: $2=$1+0::@5$2=vsum(@I$1..@II$1)
... But would you want to do this? Probably not. So something like
Michael's solution seems to be the best option. Or are we missing
something here?
This is not unlike the hour-minutes-seconds time format hack we
discussed a while ago.
http://orgmode.org/worg/org-hacks.html#time-computation
Org tables with Calc is the greatest invention since buttered toast,
but user-friendly localizable formats for non-scientific uses may not
be its strong side.
Yours,
Christian
On 5/16/11 12:05 PM, Michael Brand wrote:
> Hi Izzie
>
> The only direct solution I can think of now is with Emacs Lisp for
> number/string conversion and ./, replacement:
>
> #+begin_src emacs-lisp :results silent
> (defun com2num (com)
> "convert number string with comma like \"2,3\" to number like 2.3"
> (string-to-number (replace-regexp-in-string "," "." com)))
> (defun num2com (fmt num)
> "convert number like 2.300001 to number string with comma
> like \"2,3\", formatted with fmt like \"%.1f\""
> (replace-regexp-in-string "\\." "," (format fmt num)))
> #+end_src
>
> |<r> |
> |------|
> | 10,2 |
> | 3,0 |
> | 5,6 |
> |------|
> | 18,8 |
> #+TBLFM: @5='(num2com "%.1f" (apply '+ (mapcar 'com2num '(@2..@4))))
>
> Michael
>
> On Mon, May 16, 2011 at 01:12, Izzie<ml_orgmode.kapush@antichef.net> wrote:
>> I started using org tables including a column of numbers formatted the European
>> way with a comma instead of a period, for example 127,43 for 127.43.
>>
>> When I use a formula to sum the whole column it expect a period and ends up with
>> a false calculation. I'd revert my numbers to the American format but this table
>> is used for accounting so it's not an option. Going through the manual didn't
>> provide any help.
>>
>> Is there a way to have org use the european format or am I to ditch the formula
>> and make use of C-c + to manually calculate the sum ?
>
>
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-16 11:26 ` Christian Moe
@ 2011-05-16 12:20 ` Carsten Dominik
2011-05-16 13:23 ` Christian Moe
0 siblings, 1 reply; 8+ messages in thread
From: Carsten Dominik @ 2011-05-16 12:20 UTC (permalink / raw)
To: mail; +Cc: Michael Brand, emacs-orgmode, Izzie
On May 16, 2011, at 1:26 PM, Christian Moe wrote:
> Hi,
>
> This seems to be a limitation in Emacs Calc, on which Org table spreadsheet functions are based. Calc accepts only the dot as decimal point for number *entry*. However, you can have it *display* the point as you like by customizing calc-point-char (that's `d .' in a Calc buffer).
>
> In Org, you *could* try this:
>
> (setq org-calc-default-modes
> (append org-calc-default-modes '(calc-point-char ",")))
>
> However, it looks like this creates more problems than it's worth. Commas remain useless for data entry, including entry from Org table cells, so you would need to keep a source column with dots, and copy the numbers over to the column you want to display. And you could only do math on the source column. E. g., the following would work for a column of numbers to be summed at the bottom line:
>
> | Point | Comma |
> |-------+-------|
> | 2.3 | |
> | 11.3 | |
> | 2.1 | |
> |-------+-------|
> | | |
> #+TBLFM: $2=$1+0::@5$2=vsum(@I$1..@II$1)
>
> ... But would you want to do this? Probably not. So something like Michael's solution seems to be the best option. Or are we missing something here?
>
> This is not unlike the hour-minutes-seconds time format hack we discussed a while ago.
>
> http://orgmode.org/worg/org-hacks.html#time-computation
>
> Org tables with Calc is the greatest invention since buttered toast, but user-friendly localizable formats for non-scientific uses may not be its strong side.
I also think that it would be more trouble than useful
to try to change this. After all, you want to keep the
tables functional.
When the OP says he needs this for accounting, I guess
he is exporting this data somehow? How about changing
from dot to comma only in one of the export hooks?
(add-hook 'org-export-preprocess-hook
'org-use-comma-in-exported-tables)
(defun org-use-comma-in-exported-tables ()
(goto-char (point-min))
(while (re-search-forward "\\([0-9]\\)\\.\\([0-9]\\)" nil t)
(org-if-unprotected
(when (save-match-data (org-at-table-p))
(replace-match "\\1,\\2" t nil)))))
- Carsten
>
> Yours,
> Christian
>
>
> On 5/16/11 12:05 PM, Michael Brand wrote:
>> Hi Izzie
>>
>> The only direct solution I can think of now is with Emacs Lisp for
>> number/string conversion and ./, replacement:
>>
>> #+begin_src emacs-lisp :results silent
>> (defun com2num (com)
>> "convert number string with comma like \"2,3\" to number like 2.3"
>> (string-to-number (replace-regexp-in-string "," "." com)))
>> (defun num2com (fmt num)
>> "convert number like 2.300001 to number string with comma
>> like \"2,3\", formatted with fmt like \"%.1f\""
>> (replace-regexp-in-string "\\." "," (format fmt num)))
>> #+end_src
>>
>> |<r> |
>> |------|
>> | 10,2 |
>> | 3,0 |
>> | 5,6 |
>> |------|
>> | 18,8 |
>> #+TBLFM: @5='(num2com "%.1f" (apply '+ (mapcar 'com2num '(@2..@4))))
>>
>> Michael
>>
>> On Mon, May 16, 2011 at 01:12, Izzie<ml_orgmode.kapush@antichef.net> wrote:
>>> I started using org tables including a column of numbers formatted the European
>>> way with a comma instead of a period, for example 127,43 for 127.43.
>>>
>>> When I use a formula to sum the whole column it expect a period and ends up with
>>> a false calculation. I'd revert my numbers to the American format but this table
>>> is used for accounting so it's not an option. Going through the manual didn't
>>> provide any help.
>>>
>>> Is there a way to have org use the european format or am I to ditch the formula
>>> and make use of C-c + to manually calculate the sum ?
>>
>>
>
>
- Carsten
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-16 12:20 ` Carsten Dominik
@ 2011-05-16 13:23 ` Christian Moe
2011-05-16 17:54 ` Carsten Dominik
0 siblings, 1 reply; 8+ messages in thread
From: Christian Moe @ 2011-05-16 13:23 UTC (permalink / raw)
To: Carsten Dominik; +Cc: Michael Brand, emacs-orgmode, Izzie
On 5/16/11 2:20 PM, Carsten Dominik wrote:
> When the OP says he needs this for accounting, I guess
> he is exporting this data somehow? How about changing
> from dot to comma only in one of the export hooks?
Ah... sanity. Yes.
And just in time, too, as I was ready to unleash on Izzie the ultimate
accountant's nightmare -- a fragile "with-comma" macro to allow using
comma as decimal separator in Org spreadsheets with Lisp formulas.
Yours,
Christian
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-16 13:23 ` Christian Moe
@ 2011-05-16 17:54 ` Carsten Dominik
2011-05-16 18:08 ` Nick Dokos
0 siblings, 1 reply; 8+ messages in thread
From: Carsten Dominik @ 2011-05-16 17:54 UTC (permalink / raw)
To: mail; +Cc: Michael Brand, emacs-orgmode, Izzie
On 16.5.2011, at 15:23, Christian Moe wrote:
> On 5/16/11 2:20 PM, Carsten Dominik wrote:
>> When the OP says he needs this for accounting, I guess
>> he is exporting this data somehow? How about changing
>> from dot to comma only in one of the export hooks?
>
> Ah... sanity. Yes.
>
> And just in time, too, as I was ready to unleash on Izzie the ultimate accountant's nightmare -- a fragile "with-comma" macro to allow using comma as decimal separator in Org spreadsheets with Lisp formulas.
:) Interesting! But maybe not really practical.
We can start a file on worg, org-madness.org, next to org-hacks.org...?
Cheers
- Carsten
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-16 17:54 ` Carsten Dominik
@ 2011-05-16 18:08 ` Nick Dokos
2011-05-16 20:46 ` Carsten Dominik
0 siblings, 1 reply; 8+ messages in thread
From: Nick Dokos @ 2011-05-16 18:08 UTC (permalink / raw)
To: Carsten Dominik; +Cc: Michael Brand, nicholas.dokos, emacs-orgmode, mail, Izzie
Carsten Dominik <carsten.dominik@gmail.com> wrote:
>
> On 16.5.2011, at 15:23, Christian Moe wrote:
>
> > On 5/16/11 2:20 PM, Carsten Dominik wrote:
> >> When the OP says he needs this for accounting, I guess
> >> he is exporting this data somehow? How about changing
> >> from dot to comma only in one of the export hooks?
> >=20
> > Ah... sanity. Yes.
> >=20
> > And just in time, too, as I was ready to unleash on Izzie the ultimate =
> accountant's nightmare -- a fragile "with-comma" macro to allow using =
> comma as decimal separator in Org spreadsheets with Lisp formulas.
>
> :) Interesting! But maybe not really practical.
> We can start a file on worg, org-madness.org, next to org-hacks.org...?
>
... with a prize for the craziest idea: maybe a t-shirt with an Escher-like
impossible unicorn[fn:1]. To your infinite relief, I'll let Bastien's artist friend
design it :-)
Nick
Footnotes:
[fn:1] impossible unicorn - hmm...
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: tables: is it possible to use a comma instead of a period as a number delimiter
2011-05-16 18:08 ` Nick Dokos
@ 2011-05-16 20:46 ` Carsten Dominik
0 siblings, 0 replies; 8+ messages in thread
From: Carsten Dominik @ 2011-05-16 20:46 UTC (permalink / raw)
To: nicholas.dokos; +Cc: Michael Brand, emacs-orgmode, mail, Izzie
On 16.5.2011, at 20:08, Nick Dokos wrote:
> Carsten Dominik <carsten.dominik@gmail.com> wrote:
>
>>
>> On 16.5.2011, at 15:23, Christian Moe wrote:
>>
>>> On 5/16/11 2:20 PM, Carsten Dominik wrote:
>>>> When the OP says he needs this for accounting, I guess
>>>> he is exporting this data somehow? How about changing
>>>> from dot to comma only in one of the export hooks?
>>> =20
>>> Ah... sanity. Yes.
>>> =20
>>> And just in time, too, as I was ready to unleash on Izzie the ultimate =
>> accountant's nightmare -- a fragile "with-comma" macro to allow using =
>> comma as decimal separator in Org spreadsheets with Lisp formulas.
>>
>> :) Interesting! But maybe not really practical.
>> We can start a file on worg, org-madness.org, next to org-hacks.org...?
>>
>
> ... with a prize for the craziest idea: maybe a t-shirt with an Escher-like
> impossible unicorn[fn:1]. To your infinite relief, I'll let Bastien's artist friend
> design it :-)
>
> Nick
>
> Footnotes:
>
> [fn:1] impossible unicorn - hmm...
awesome!
^ permalink raw reply [flat|nested] 8+ messages in thread
end of thread, other threads:[~2011-05-16 20:46 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2011-05-15 23:12 tables: is it possible to use a comma instead of a period as a number delimiter Izzie
2011-05-16 10:05 ` Michael Brand
2011-05-16 11:26 ` Christian Moe
2011-05-16 12:20 ` Carsten Dominik
2011-05-16 13:23 ` Christian Moe
2011-05-16 17:54 ` Carsten Dominik
2011-05-16 18:08 ` Nick Dokos
2011-05-16 20:46 ` Carsten Dominik
Code repositories for project(s) associated with this public 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).