1

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?

2

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
| improve this answer | |
2

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.

| improve this answer | |
  • 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
1

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:
| improve this answer | |

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.