Can columns of an org table be grouped to conveniently work with data?

Question

I have an org table

|        |        | P1 | P2 | P3 | P4 | Q1 | Q2 |  E1 |  E2 |   F |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
|        | Totals | 20 | 40 | 35 | 20 | 50 | 45 | 100 | 100 | 200 |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
| John   |        | 18 | 18 | 15 |  0 | 45 | 22 |  45 |  97 | 112 |
| Paul   |        |  4 | 12 |  4 |  8 | 10 | 14 |  31 |  58 | 158 |
| George |        | 15 | 34 | 32 | 16 | 46 | 12 |  94 |  46 | 141 |
| Ringo  |        | 20 | 17 | 25 |  1 | 49 | 16 |  61 |  52 | 129 |

I would like to fill in the column Totals with a weighted sum of the other columns. The weighted sum should have a formula

Totals = w*(P1+P2+P3+P4)+x*(Q1+Q2)+y*(E1+E2)+z*(F)

Where w, x, y, and z are defined constants.

This specific formula is easy to write down. My problem is that I'd like the formula to respond when I add columns to the table. So my formula should really be something like

Totals = w*(sum of cols marked "P")+x*(sum of cols marked "Q")
         +y*(sum of cols marked "E")+z*(sum of cols marked "F")

Thus when my table is changed to

|        |        | P1 | P2 | P3 | P4 | P5 | Q1 | Q2 |  E1 |  E2 |  E3 |   F |
|--------+--------+----+----+----+----+----+----+----+-----+-----+-----+-----|
|        | Totals | 20 | 40 | 35 | 20 | 40 | 50 | 45 | 100 | 100 | 100 | 200 |
|--------+--------+----+----+----+----+----+----+----+-----+-----+-----+-----|
| John   |        | 18 | 18 | 15 |  0 | 40 | 45 | 22 |  45 |  97 |  65 | 112 |
| Paul   |        |  4 | 12 |  4 |  8 | 33 | 10 | 14 |  31 |  58 |  45 | 158 |
| George |        | 15 | 34 | 32 | 16 | 24 | 46 | 12 |  94 |  46 |  78 | 141 |
| Ringo  |        | 20 | 17 | 25 |  1 | 38 | 49 | 16 |  61 |  52 |  65 | 129 |

My formula should become

Totals = w*(P1+P2+P3+P4+P5)+x*(Q1+Q2)+y*(E1+E2+E3)+z*(F)

Is there a way to do this?

Answer 1

I have an answer using elisp formulas:

#+begin_src emacs-lisp :results none
  (defun jj/factor (code)
    (let ((prefix (string-to-char code)))
      (cond ((eq prefix ?P) 0.1)
            ((eq prefix ?Q) 0.2)
            ((eq prefix ?E) 0.3)
            ((eq prefix ?F) 0.4)
            (t 1.0))))

  (defun jj/factorlist (codelist)
    (mapcar 'jj/factor codelist))

  (defun jj/dotproduct (a b)
    (apply '+ (mapcar* '* (jj/factorlist a) (mapcar 'string-to-number b))))
#+end_src


|        |        | P1 | P2 | P3 | P4 | Q1 | Q2 | E1  | E2  | F   |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
|        | Totals | 20 | 40 | 35 | 20 | 50 | 45 | 100 | 100 | 200 |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
| John   |        | 18 | 18 | 15 | 0  | 45 | 22 | 45  | 97  | 112 |
| Paul   |        | 4  | 12 | 4  | 8  | 10 | 14 | 31  | 58  | 158 |
| George |        | 15 | 34 | 32 | 16 | 46 | 12 | 94  | 46  | 141 |
| Ringo  |        | 20 | 17 | 25 | 1  | 49 | 16 | 61  | 52  | 129 |
#+TBLFM: @3$2..@>$2='(jj/dotproduct (list @1$3..@1$>) (list $3..$>))

You have to evaluate the source block once (by hitting C-c C-c over it).

The jj/factor function assigns a numeric factor given the a string.

The second function, jj/factorlist gets a list of factors from a list of strings.

The last function, jj/dotproduct, will sum all products as you intended.

Answer 2

You can group columns for output formatting only, but you can't then refer to them like you can with row groups (@I..II etc).

What you can do though is give columns names and refer to them:

| $ |    |    |    | x=2   |
| ! | p1 |    | pn |       |
|   | P1 | P2 | P3 | Total |
|---+----+----+----+-------|
|   | 1  |  2 | 3  | 12    |
#+TBLFM: @4$5=vsum($p1..$pn)*$x

If you want to add a p4 column by putting the point in Total and pressing M-S-right you will have to manually move the pn label from the p3 to the p4 column, so it is not quite as convenient as the @I syntax for rows.

Also, you can store your constants in the table using a row marked $.