2

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?

3

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.

  • I have a question about making some modifications to your elisp code. It's too long to post as a comment, so I've posted it as a question: emacs.stackexchange.com/questions/13893/… Thanks again for your contributions--I'm a newcomer to plain-text but I'm really enjoying it! – Brian Fitzpatrick Jul 11 '15 at 0:50
3

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

  • This is a cool idea. Thanks for looking at it! – Brian Fitzpatrick Jul 9 '15 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.