Conditionally calculate the sum of a column depending on a fields value in another cell of the same row

Question

In orgmode, I do have a table like the following to track my overall working time a day. It is not important for which tasks or projects or even which client I spent my time on, just if I was in office or worked from remote.

The table looks like this:

| Day | Start |   End | Home |   Sum |
|-----+-------+-------+------+-------|
|   1 | 09:00 | 17:00 | No   |  8.00 |
|   2 | 09:00 | 12:00 | No   |  3.00 |
|   2 | 13:00 | 20:00 | Yes  |  7.00 |
|-----+-------+-------+------+-------|
|     |       |       | 7.00 | 11.00 |
#+TBLFM: @2$5..@-1$5=$3-$2;t

Now I need a formula that calculates the fields @<$4 and @<$5 as sums, while in @<$4 the values of $5 are summed when in that row $4 is Yes and in @<$5 the values of $5 shall be summed when $3 of that row is not Yes.

Instead of Yes/No I can switch to any other kind of marker as well.

Answer 1

I guess there is 2 options .. calculate some helper columns and sum multiply those or create helper elisp function .. I'll start with the latter.

#+BEGIN_SRC elisp
  (defun my-sum-helper (a  b pred)
    (reduce #'+
            (cl-mapcar
              #'*
              (mapcar
               (lambda(x) (if (string= x pred) 1 0)) a)
              b)))
#+END_SRC

#+RESULTS:
: my-sum-helper


| Day | Start |   End | Home |  Sum |
|-----+-------+-------+------+------|
|   1 | 09:00 | 17:00 | No   | 8.00 |
|   2 | 09:00 | 12:00 | No   | 3.00 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 |
|-----+-------+-------+------+------|
|     |       |       | 7.0  | 11.0 |
#+TBLFM: @2$5..@-1$5=$3-$2;t::@>$4='(my-sum-helper '(@2$4..@4$4) '(@2$5..@4$5) "Yes");L::@>$5='(my-sum-helper '(@2$4..@4$4) '(@2$5..@4$5) "No");L

To use: evaluate the src block first then evaluate the formulas.

LIke I said you can also create an intermediate column wih 0 an 1 values with the formula: @2$6..@4$6='(if (string= "$4" "Yes") 1 0);L. This facilitates the use of calc:

| Day | Start |   End | Home |  Sum |   |
|-----+-------+-------+------+------+---|
|   1 | 09:00 | 17:00 | No   | 8.00 | 0 |
|   2 | 09:00 | 12:00 | No   | 3.00 | 0 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 | 1 |
|-----+-------+-------+------+------+---|
|     |       |       | 7.   |  11. |   |
#+TBLFM: @2$5..@-1$5=$3-$2;t::@2$6..@4$6='(if (string= "$4" "Yes") 1 0);L::@>$4=vsum(@2$6..@4$6 * @2$5..@4$5);N::@>$5=vsum((1-@2$6..@4$6) * @2$5..@4$5);N

hope this helps.

Answer 2

Create table function, i.e. #+TBLFM:, and use the built-in org-lookup-all function with string= predicate and apply elisp function.

Note: Use the Literal mode L or the cell address references may misbehave.

I've wondered how to do calculations against filtered subset of an org-mode table data for years and never figured out how until today!

The Answer

| Day | Start |   End | Home |  Sum |
|-----+-------+-------+------+------|
|   1 | 09:00 | 17:00 | No   | 8.00 |
|   2 | 09:00 | 12:00 | No   | 3.00 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 |
|-----+-------+-------+------+------|
|     |       |       | 7.0  | 11.0 |
#+TBLFM: @2$5..@-1$5=$3-$2;t::@>$4='(apply '+ (org-lookup-all "Yes" '(@I$4..@II$4) '(@I$5..@II$5) 'string= ));L::@>$5='(apply '+ (org-lookup-all "No" '(@I$4..@II$4) '(@I$5..@II$5) 'string= ));L

The Tricky Bits

• Filtering the table with org-lookup-all function

  (org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)
  

  • VAL is the filter value, e.g. Yes or No.
  • S-LIST is the search list, e.g. ("No" "No" "Yes").
  • R-LIST is the returned list, e.g. (8.00 3.00 7.00)

  • PREDICATE is not optional in our answer because we're comparing strings, e.g. string=

  • Returns a filtered list, e.g. (8.0 3.0)

• Using apply elisp function instead of the vsum calc function.

  Couldn't get vsum to work - blah.

Thank you for asking your question!

This code was tested using
GNU Emacs 24.4.1 (x86_64-apple-darwin14.0.0, NS apple-appkit-1343.14)
Org-Mode Version: 8.3.5

Answer 3

An alternative would be to use the orgtbl-aggregate available on Melpa.

 #+name: yourtable
 | Day | Start |   End | Home |   Sum |
 |-----+-------+-------+------+-------|
 |   1 | 09:00 | 17:00 | No   |  8.00 |
 |   2 | 09:00 | 12:00 | No   |  3.00 |
 |   2 | 13:00 | 20:00 | Yes  |  7.00 |

 #+BEGIN: aggregate :table "yourtable" :cols "Home sum(Sum)"
 | Home | sum(Sum) |
 |------+----------|
 | No   |      11. |
 | Yes  |       7. |
 #+END:

See the repo for installation instructions:

(add-to-list 'package-archives '("melpa" . "http://melpa.milkbox.net/packages/") t)
(package-initialize)
(package-install "orgtbl-aggregate")