2

Is it possible to feed data from an org table into calc as a matrix, and then get it out again as a vector to fill in a column?

For example, if I have:

|           | Fund A | Fund B | Fund C | Combined |
| US        |     .1 |     .8 |     .5 |          |
| Europe    |     .2 |     .1 |     .4 |          |
| Pacific   |     .7 |     .1 |     .1 |          |
| Weighting |     .3 |     .5 |     .2 |          |

I would like to use a calc formula to matrix-multiply the data from the first three rows of numbers by the (transposed) weighting in the last row of numbers, and insert the result in the final column, like this:

|           | Fund A | Fund B | Fund C | Combined |
| US        |     .1 |     .8 |     .5 |      .53 |
| Europe    |     .2 |     .1 |     .4 |      .19 |
| Pacific   |     .7 |     .1 |     .1 |      .28 |
| Weighting |     .3 |     .5 |     .2 |          |

I was hoping maybe something like this would achieve that if inserted in the top cell of the last column -- but this is incorrect: no output is produced in the table:

=($2@2..$2@4) * trn($4@2..$4..@4)

If it's not possible using calc, is it possible using elisp?

1 Answer 1

3

The following works for me:

|           | Fund A | Fund B | Fund C | Combined |
|-----------+--------+--------+--------+----------|
| US        |     .1 |     .8 |     .5 |     0.53 |
| Europe    |     .2 |     .1 |     .4 |     0.19 |
| Pacific   |     .7 |     .1 |     .1 |     0.28 |
| Weighting |     .3 |     .5 |     .2 |          |
#+TBLFM: @2$>..@>>$> = ($2..$>> * trn(@>$2..@>$>>))_1

In words: set the last column of rows 2 through one before the last row equal to the first element of the vector (_1) that is the product of that row vector ($2..$>> i.e. cols 2 through one before the last column) with the transpose of the row vector in the last row and the same columns (@>$2..@>$>>).

The matrix product is a 1x1 matrix but calc interprets it as a vector: I first tried extracting the element using both row and column (_1_1) but the second _1 went unused, so I just deleted it.

2
  • Surely the matrix product is 4 x 1, not 1 x 1? Is three some way to visualise the result of the calc expression before it gets substituted back into the org table? Dec 2, 2019 at 23:14
  • It's not done all at once: it's done row by row, so it's the product of a 1x3 row vector (say the US one) and the transpose of another 1x3 row vector (the weights), so the result is indeed 1x1. You can turn on formula debugging (C-c {) and watch it step by step.
    – NickD
    Dec 3, 2019 at 2:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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