# Separately sum positive/negative numbers?

By example:

``````|   | Amount |
|---+--------|
|   |   3.50 |
|   |   1.00 |
|   |  -6.00 |
|   |   5.00 |
|   |  -2.50 |
|---+--------|
|   |   9.50 |
| ^ |   wins |
|---+--------|
|   |   8.50 |
| ^ | losses |
``````

What is the value of `TBLFM` to calculate wins and losses as above?

If you want to use Calc instead of Emacs Lisp:

``````#+TBLFM: \$wins=vsum(map(max,@I..II,0));%.2f::\$losses=-vsum(map(min,@I..II,0));%.2f
``````

These seem to work:

``````#+TBLFM: \$wins='(format "%0.2f" (apply '+ (seq-filter (lambda (x) (> x 0)) (list @2..@-1))));L::\$losses='(format "%0.2f" (- (apply '+ (seq-filter (lambda (x) (< x 0)) (list @2..@-2)))));L
``````

You might need to `(require 'seq)` to use `seq-filter`.

I am not sure how you would do this in calc. If it can do elementwise multiplication and logical comparisons, you might be able to multiply the numbers by L < 0, and then sum the result to get the negative sum, and by L > 0 for the positive sum.

• Thanks! I was hoping that this can be done using Emacs Calc syntax. To get the format as in my question, I had to change your code to: `#+TBLFM: \$wins='(format "%0.2f" (apply '+ (seq-filter (lambda (x) (> x 0)) (list @2..@-1))));L::\$losses='(format "%0.2f" (- (apply '+ (seq-filter (lambda (x) (< x 0)) (list @2..@-2)))));L` Maybe you can update your answer accordingly? It also needs to be all in one line for `org-table-recalculate` and `org-table-iterate` to work. – feklee Nov 7 '18 at 10:46
• I updated the solution. – John Kitchin Nov 7 '18 at 13:09
• Calc seems simpler: `\$wins=vsum(map(max,@I..II,0))` ---also I find that counting hlines is generally neater than counting lines. – Omar Nov 8 '18 at 3:31

You may want to take a look at the `orgtbl-aggregate` package available on http://melpa.org

Documentation here: https://github.com/tbanel/orgaggregate

First you create a column which tells it is a `win` or a `lose`.

``````#+name: winlose
| Amount | Win? |
|--------+------|
|   3.50 | win  |
|   1.00 | win  |
|  -6.00 | lose |
|   5.00 | win  |
|  -2.50 | lose |
#+TBLFM: \$2='(if (>= (string-to-number \$1) 0) "win" "lose")
``````

Then you aggregate the `Amount` column grouped by `Win?` tags.

``````#+BEGIN: aggregate :table "winlose" :cols "'Win?' vsum(Amount)"
| 'Win?' | vsum(Amount) |
|--------+--------------|
| win    |          9.5 |
| lose   |         -8.5 |
#+END:
``````