Org table spreadsheet use column name on left hand side

Question

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

Answer 1

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