2

I have the following table and formulas:

|   | Runde | 0 | 1 | 2 | Resultat |          % |
|---+-------+---+---+---+----------+------------|
| ! | name  |   |   |   |   result | percentage |
| # | Alice | 1 | 1 | 0 |        2 |      66.67 |
| # | Bob   | 0 | 0 | 1 |        1 |      33.33 |
#+TBLFM: $6='(+ $<<<..$>>>);N
#+TBLFM: $7='(format "%2.2f" (* (/ 100 (float (+ @3$result @4$result))) $result));N

Using numbers on the left hand side works fine, but it would be even better if I could do the formulas as follows:

#+TBLFM: $result='(+ $<<<..$>>>);N
#+TBLFM: $percentage='(format "%2.2f" (* (/ 100 (float (+ @3$result @4$result))) $result));N

However, as soon as I put in column names on the left, the formulas are not working properly any more. Is this a bug, or is my understanding of how the formulas work wrong?

Version:

  • GNU Emacs 24.5.1 (x86_64-pc-linux-gnu, GTK+ Version 3.18.9) of 2017-09-20 on lcy01-07, modified by Debian
  • org mode version from package list: org 9.1.13 available gnu Outline-based notes management and organizer
Melioratus
  • 4,504
  • 1
  • 25
  • 43
Zelphir Kaltstahl
  • 375
  • 3
  • 14
  • 1
    Apparently *field names* work for assignment (those defined with `^` or `_`), but *column names* don't. – Juancho Jul 13 '18 at 23:02
  • 1
    Confirmed: checking the source for `org-table-get-stored-formulas`, there is a comment about named columns on LHS not being possible. – Juancho Jul 13 '18 at 23:36
  • @Juancho I don't think it's theoretically impossible, as I don't see how it would create ambiguity, but maybe in terms of what org tables can currently do it is impossible. Could you make your comment an answer? Then I could accept it. – Zelphir Kaltstahl Jul 14 '18 at 11:31

1 Answers1

2

Simulate LHS Column Name Assignments

Simulate Left Hand Side (LHS) column name assignments using named fields and a keyboard macro.

  1. Update your example table and formulas as follows:

    |   | Runde |  0 |  1 |  2 | Resultat | %          |
|---+-------+----+----+----+----------+------------|
| ! |       | c0 | c1 | cn |          |            |
| _ | name  |    |    |    | result   | percentage |
| # | Alice |  1 |  1 |  0 |          |            |
| # | Bob   |  0 |  0 |  1 |          |            |
| # | Eve   |  1 |  1 |  1 |          |            |
#+TBLFM: $result='(reduce '+ (vector $c0..$cn));N::$percentage='(calc-eval (format "(%i/%i)*100" $result (length (vector $c0..$cn))));%2.2f

    • Update Details

      • Added named column syntax for c0, c1, and cn which is referenced in formulas as $c0..$cn to allow additional columns to be added between c0 and cn in future as needed.

      • Redefined name, result and percentage using field name syntax.

      • Added data row to table.

      • Updated #+TBLFM: with similar formulas whose results are assigned on the LHS to the field names $result and $percentage.

  2. Define simulate-lhs-column-name-assignments keyboard macro using elisp src block.

    #+BEGIN_SRC elisp :results silent
  (fset 'simulate-lhs-column-name-assignments
     [?\C-u ?\C-u ?\C-c ?\C-c M-down])
#+END_SRC

  3. Bind simulate-lhs-column-name-assignments to key or your choice, e.g. f6 key.

    #+BEGIN_SRC elisp  :results silent
  (local-set-key (kbd "<f6>") 'simulate-lhs-column-name-assignments)
#+END_SRC

  4. Place point, aka cursor, inside a cell on the row which assigns field names.

    | _ | name  |    |    |    | result   | percentage |

  5. Call the simulate-lhs-column-name-assignments keyboard macro using the assigned key, e.g. f6 key, until the line with the field assignments is at bottom of table.

    After completing this step, your table should resemble the example below:

    |   | Runde |  0 |  1 |  2 | Resultat |          % |
|---+-------+----+----+----+----------+------------|
| ! |       | c0 | c1 | cn |          |            |
| # | Alice |  1 |  1 |  0 |        2 |      66.67 |
| # | Bob   |  0 |  0 |  1 |        1 |      33.33 |
| # | Eve   |  1 |  1 |  1 |        3 |     100.00 |
| _ | name  |    |    |    |   result | percentage |
#+TBLFM: $result='(reduce '+ (vector $c0..$cn));N::$percentage='(calc-eval (format "(%i/%i)*100" $result (length (vector $c0..$cn))));%2.2f

Thanks for asking a great question!

The code in this answer was validated using
emacs version: GNU Emacs 25.2.1 (x86_64-unknown-cygwin, GTK+ Version 3.22.10)
org version: 9.1.2

Melioratus
  • 4,504
  • 1
  • 25
  • 43
  • It is a decent workaround. Since there is no other answer showing how to use column names on the LHS, I will accept this as answer now : ) – Zelphir Kaltstahl Aug 11 '19 at 08:49