Thanks for the helpful replies to question 2 but what about question 1? Any thoughts on how I can get org-mode to dynamically add an extra row? Thanks, -Emin On Tue, Sep 7, 2010 at 5:20 AM, Christian Moe wrote: > On 9/7/10 2:56 AM, Juan wrote: > >> A very complex way of not adding the extra column: >> >> | name | a | b | c | >> |------+----+---+---| >> | foo | 1 | 2 | 3 | >> | bar | 3 | 2 | 1 | >> | bar | 4 | 5 | 6 | >> |------+----+---+---| >> | | 7 | | | >> #+TBLFM: @5$2='(apply '+ (mapcar* (lambda(x y) (if (string= x "bar") y 0)) >> '(@I$1..@II$1) '(@I$2..@II$2)));L >> >> * the two arguments at the end are the name and a columns: '(foo bar bar) >> and '(1 3 4) >> * the lambda function returns the second argument if first is "bar", 0 >> otherwise. >> * mapcar* applies the lambda function to arguments from the 2 lists. >> * apply '+ adds the resulting list >> >> Regards, >> .j. >> > > Neat! This is what I wanted to achieve. Good thing I gave up, though, > I see it would have kept me up all night. > > (And yes, I meant "add a new /column/", not row.) > > If one wants to do this often (e.g., in the other two columns), one > could tuck away some of the complexity into one's .emacs, and at the > same time get away from hard-coding the match string, like so: > > #+begin_src elisp > (defun vsumif (string x y) > "Sum values of Y for all X matching STRING." > > (apply '+ > (mapcar* > (lambda(x y) > (if (string= x match) y 0)) > x y))) > #+end_src > > Now, one can e.g. put the string one is matching for in the table. Try > updating the spreadsheet below, then changing `foo' in the bottom row > (@5$1) to `bar' and updating again. > > > | name | a | b | c | > |------+---+---+---| > | foo | 1 | 2 | 3 | > | bar | 3 | 2 | 1 | > | bar | 4 | 5 | 6 | > |------+---+---+---| > | bar | | | | > #+TBLFM: @5$2='(vsumif '@5$1 '(@I$1..@II$1) '(@I$2..@II$2));L > > It's still a lengthy formula and not the easiest to write. If you'd > like to add up all foos or bars for columns a, b and c, you may be > better off swapping rows and columns so you can use column formulas: > > | | name | foo | bar | bar | bar | > |---+------+-----+-----+-----+-----| > | / | <> | < | | > | <> | > | | a | 1 | 3 | 4 | | > | | b | 2 | 2 | 5 | | > | | c | 3 | 1 | 6 | | > #+TBLFM: $6='(vsumif '@1$6 '(@1$3..@1$5) '($3..$5));L > > Again, replace `foo' in @1$6 with `bar' to get totals for bar. > > I have added vertical lines to the table. > > One could presumable write =vsumif= in a more general form that would > not be hard-coded to test only for matching strings. The function > defined above might be better named =vsumifstring=. > > Cheers, > CM > > >> On Tue, Sep 07, 2010 at 12:44:03AM +0200, Christian Moe wrote: >> >>> On 9/6/10 3:38 PM, Inquisitive Scientist wrote: >>> >>> 2. How do I compute the sum of a column only if a corresponding row >>>> matches some condition? For example, how do I compute the sum of >>>> numbers in column a for which the name in column "name" is "bar"? For >>>> example, I should get 7 for the sum in column a in the table below: >>>> >>>> | name | a | b | c | >>>> |------+---+---+---| >>>> | foo | 1 | 2 | 3 | >>>> | bar | 3 | 2 | 1 | >>>> | bar | 4 | 5 | 6 | >>>> |------+---+---+---| >>>> >>> >>> Here's one way: Add a new row >>> >> > sorry: meant "add a new column" > > after the first, as below. Then run C-c >>> C-c on the formula line: >>> >>> | name | | a | b | c | >>> |------+---+---+---+---| >>> | foo | | 1 | 2 | 3 | >>> | bar | | 3 | 2 | 1 | >>> | bar | | 4 | 5 | 6 | >>> |------+---+---+---+---| >>> | | | | | | >>> #+TBLFM: $2='(if (string= $1 "bar") 1 0):: >>> @5$3=vsum(vmask(@I$2..@II$2,@I..@II)) >>> >>> It does exactly what you asked, but I don't think it will scale well... >>> >>> >> _______________________________________________ >> Emacs-orgmode mailing list >> Please use `Reply All' to send replies to the list. >> Emacs-orgmode@gnu.org >> http://lists.gnu.org/mailman/listinfo/emacs-orgmode >> >> > >