# In an orgmode clock report table, how do I sum a column that was computed with another formula?

Suppose I have some headlines that look like this.

``````* TODO Parent
CLOCK: [2015-02-09 Mon 07:00]--[2015-02-09 Mon 07:08] =>  0:08
CLOCK: [2015-02-09 Mon 19:20]--[2015-02-09 Mon 19:24] =>  0:04
CLOCK: [2015-02-09 Mon 07:08]--[2015-02-09 Mon 07:11] =>  0:03
``````

Now, if I have a clock table report defined like this.

``````#+BEGIN: clocktable :maxlevel 2 :scope file :formula "\$4='(random)::@2\$4='(vsum @3\$4)"
``````

I have a custom formula that computes a value. To illustrate my problem, I've used (random) as a way to populate a column. How do I then sum up the results?

``````| Headline           | Time   |      |                     |
|--------------------+--------+------+---------------------|
| *Total time*       | *0:15* |      |              #ERROR |
|--------------------+--------+------+---------------------|
| TODO Parent        | 0:15   |      |  796400794509121126 |
| \__ TODO Sub Head1 |        | 0:12 | 2086072072269781784 |
| \__ TODO Sub Head2 |        | 0:03 | 1733653844633948893 |
``````

I've used the formula debugger C-f { and I can see that my vsum invocation is receiving the cell values as propertized strings. Is there a way to compute that sum and lay it down in that cell where the #ERROR resides?

Hoping to provide a little more value to this answer, here's some explanation (to the best of my knowledge) of how Org processes the table formulae.

1. Org splits formulae on `::` into simpler expressions.
2. Org replaces the range expressions with the actual values extracted from the table columns.
3. If the formula is a Calc expression, it calls `calc-eval` on it and then parses the result of `calc-eval` to give the final result.

Now, to figure out what Calc does with your formula you can do the following (the example should explain it better):

``````;; Assume Org substituted the range expression with actual values
;; and the destination with some variable it knows to get back
(calc-eval "x=vsum(1, 2, 3)")
"x = 6"
;; And here's how you can see what Calc will do to the formula,
;; the so-called AST (abstract syntax tree), which also tells you
;; what function you would need to call from ELisp to get the
;; same result as you would have by specifying the corresponding
;; formula.
((calcFunc-eq (var x var-x) (calcFunc-vsum 1 2 3)))
``````

So, in the end, you could have used a formula like this:

``````#+TBLFM: @3\$4..@>\$4='(random)::@2\$4='(apply 'calcFunc-vsum (mapcar 'string-to-number '(@3\$4..@>\$4)))
``````

And you could test it in the `*scratch*` buffer to see that, if you are running on a 32-bit system, you'd get an integer result, but on 64-bit system, the result might look something like: `(bigpos 815607762 964955910 2)` because Calc uses 32-bit arithmetic. And this would be invalid as the table value. So, you could either do:

``````#+TBLFM: @3\$4..@>\$4='(random)::@2\$4='(math-format-value (apply 'calcFunc-vsum (mapcar 'string-to-number '(@3\$4..@>\$4))))
``````

Which would take care of all kinds of data-structures that Calc understands (such as dates, errors, complex numbers). Or, in a simpler case, you could use Emacs-lisp mathematics:

``````#+TBLFM: @3\$4..@>\$4='(random)::@2\$4='(reduce '+ (mapcar 'string-to-number (list @3\$4..@>\$4)))
``````

The problem is that you have an error in your sum formula. You declared it as a lisp form using `='(vsum @3\$4)`, but since vsum is a calc command the correct syntax in table formulas is just `=vsum(@3\$4)`.

• I think OP wants to use `vsum` in ELisp, in which case something like this should do: `#+TBLFM: @3\$4..@>\$4='(random)::@2\$4='(reduce '+ (mapcar 'string-to-number (list @3\$4..@>\$4)))`. – wvxvw Feb 13 '15 at 8:48
• So this sums the column which is what I had wanted. I'd be happy to accept this as the answer. But just to be clear, shouldn't I be able to use vsum to do this too? `(vsum @3\$4..@>\$4)` doesn't seem to be it. – Eric Johnson Feb 13 '15 at 9:20
• @EricJohnson nope, `vsum` is not an ELisp function or macro. Org translates it into some other code, probably `calc-summation` or maybe just `(apply '+ value-1 value-2 ... value-n)`. – wvxvw Feb 13 '15 at 9:25
• So with that - your comment is the answer. If you'd like the credit, post your original comment as the answer and then I can accept it. – Eric Johnson Feb 13 '15 at 9:51