0

I am looking for a way to multiply 2 columns element by element and then sum the resulting vector. I have the following example:

|   |       | col1 | enable |
|---+-------+------+--------|
|   |       |    1 |      1 |
|   |       |    3 |      0 |
|   |       |      |      0 |
|   |       |    7 |      1 |
|---+-------+------+--------|
| # | sum   |   11 |      2 |
| # | value |      |        |
#+TBLFM: @6$3=vsum(@I$3..@II$3);N::@6$4=vsum(@I$4..@II$4);N

Now I want to add up all values of column col1, where there is a 1 in the enable column. The enable column determins, whether the value in column col1 is mathematically considered, because multiplied by 0, it will become an addend of 0 in the sum.

Mathematically speaking I want to:

result = sum_{i=0}^i=2 (col1_i * enable_i)

How can I achieve this?

I found inner on https://www.gnu.org/software/emacs/manual/html_node/calc/Generalized-Products.html#Generalized-Products and https://www.gnu.org/software/emacs/manual/html_node/calc/Function-Index.html#Function-Index, but do no understand how to make use of it or whether it is what I am looking for (not so firm in the terminology).

If the answer is, that this is not possible with a GNU Emacs Calc formula, that's also fine and good to know, but I think it should be possible, as it seems rather basic.

I would like to avoid having to add an extra column for the products and then summing that. The more clutter I can avoid, the better, as this table might get much wider in the future.

4 Answers 4

4

Your guess inner is right. The first two operands of inner are the multiplication and the summation operator, respectively. The remaining two arguments are the vectors for the inner product.

The summation operator is add and the multiplication operator is mul.

Your table inclusive the table-formula should look like:

|   |       | col1 | enable |
|---+-------+------+--------|
|   |       |    1 |      1 |
|   |       |    3 |      0 |
|   |       |    0 |      0 |
|   |       |    7 |      1 |
|---+-------+------+--------|
| # | value |    8 |        |
#+TBLFM: @>$3=inner(mul,add,@I$3..@II$3,@I$4..@II$4)
2
  • Wow, if I understand this correctly, it means, that I could use arbitrary other operations with inner. That makes it quite powerful. Apr 22, 2020 at 15:13
  • 2
    Calc * calculates the inner product of two vectors, so for this particular case you really don't need inner: #+TBLFM: @>$3=@I$3..@II$3 * @I$4..@II$4 will do, although the empty cell at @4$3 does have to be filled in, as @Tobias has done in the answer: you cannot leave it empty or else the vectors have different dimensions.
    – NickD
    Apr 22, 2020 at 19:06
1

This is straightforward: use the EN options, as follows:

@7$3=vsum(@I..@II*@I$+1..@II$+1);EN
1

You may try the orgtb-aggregate package available on Melpa. It will create new tables from yours. In this example, partial sums are computed for similar values of enable:

#+name: mytable
|   |       | col1 | enable |
|---+-------+------+--------|
|   |       |    1 |      1 |
|   |       |    3 |      0 |
|   |       |      |      0 |
|   |       |    7 |      1 |

#+BEGIN: aggregate :table "mytable" :cols "enable vsum(col1)"
| enable | vsum(col1) |
|--------+------------|
|      1 |          8 |
|      0 |          3 |
#+END:

Alternately, you may apply the mathematical formula you gave:

#+BEGIN: aggregate :table "mytable" :cols "vsum(col1*enable);NE"
| vsum(col1*enable);NE |
|----------------------|
|                    8 |
#+END:

(The ;NE format is useful to cope with an empty cell in the 3rd row)

https://github.com/tbanel/orgaggregate/blob/master/README.org

0

You can create an auxiliary column to store the multiples:

|   |     | col1 | enable | mul |
|---+-----+------+--------+-----|
|   |     |    1 |      1 |   1 |
|   |     |    3 |      0 |   0 |
|   |     |      |      0 |   0 |
|   |     |    7 |      1 |   8 |
|---+-----+------+--------+-----|
| # | sum |   11 |      2 |   9 |
#+TBLFM: $5=$3*$4::@6$3=vsum(@I$3..@II$3);N::@6$4=vsum(@I$4..@II$4);N::@6$5=vsum(@I..@II)
1
  • Yes, that is one way, however, I would like to avoid an unnecessary column for this (see question / OP). Apr 22, 2020 at 15:08

Your Answer

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

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