From mboxrd@z Thu Jan 1 00:00:00 1970 From: Juan Pechiar Subject: Re: Smart vsum Date: Mon, 15 Nov 2010 10:40:04 -0200 Message-ID: <20101115124004.GF2450@soloJazz.com> References: <87ipzyzt0z.fsf@Compaq.site> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="6c2NcOVqGQ03X4Wi" Return-path: Received: from [140.186.70.92] (port=33002 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PHyM1-0003ZV-Jn for emacs-orgmode@gnu.org; Mon, 15 Nov 2010 07:40:16 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PHyLx-0006tE-GU for emacs-orgmode@gnu.org; Mon, 15 Nov 2010 07:40:13 -0500 Received: from cpoproxy2-pub.bluehost.com ([67.222.39.38]:55128) by eggs.gnu.org with smtp (Exim 4.71) (envelope-from ) id 1PHyLx-0006t3-6i for emacs-orgmode@gnu.org; Mon, 15 Nov 2010 07:40:09 -0500 Content-Disposition: inline In-Reply-To: <87ipzyzt0z.fsf@Compaq.site> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Cecil Westerhof Cc: emacs-orgmode@gnu.org --6c2NcOVqGQ03X4Wi Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Hi Cecil. The attached email from orgmode mailing list includes code for a conditional vsum, which is what you need here. Regards, .j. On Mon, Nov 15, 2010 at 01:10:52PM +0100, Cecil Westerhof wrote: > It is just a table with activities and the needed time pro activity. > When there is an asterisk in the first column, that means that the > activity is already done. I would like a little more fancy table that > also displays the time needed for the activities that still have to be > done. Like the following: > |---+----------+-----------| > | | activity | time | > |---+----------+-----------| > | * | A | 0@ 10' | > | * | B | 0@ 05' | > | | C | 0@ 05' | > | | D | 0@ 05' | > | | E | 0@ 05' | > |---+----------+-----------| > | # | Totaal | 0.50 | > | ^ | | totalTime | > | # | Totaal | 0.25 | > | ^ | | restTime | > |---+----------+-----------| > #+TBLFM: $totalTime = deg(vsum(@-II..@-I)); f2 > > Is this possible? If yes, how would I do this? > --6c2NcOVqGQ03X4Wi Content-Type: message/rfc822 Content-Disposition: inline Return-Path: X-Original-To: jips@localhost Delivered-To: jips@localhost Received: from iso.palmas.net (localhost.localdomain [127.0.0.1]) by iso.palmas.net (Postfix) with ESMTP id E83AE49011F for ; Tue, 7 Sep 2010 06:20:06 -0300 (UYT) Envelope-to: john@solojazz.com Delivery-date: Tue, 07 Sep 2010 03:19:57 -0600 Received: from solojazz.com [69.89.21.83] by iso.palmas.net with POP3 (fetchmail-6.3.17) for (single-drop); Tue, 07 Sep 2010 06:20:06 -0300 (UYT) Received: from solojazz by box519.bluehost.com with local-bsmtp (Exim 4.69) (envelope-from ) id 1OsuLL-00020K-0T for john@solojazz.com; Tue, 07 Sep 2010 03:19:57 -0600 X-Spam-Checker-Version: SpamAssassin 3.3.0 (2010-01-18) on box519.bluehost.com X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00,RCVD_IN_DNSWL_LOW, SPF_SOFTFAIL shortcircuit=no autolearn=no version=3.3.0 Received: from cs-listserv.ieee.org ([140.98.193.208] helo=cs-ems2.ieee.org) by box519.bluehost.com with esmtps (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1OsuLK-0001xW-DI for john@solojazz.com; Tue, 07 Sep 2010 03:19:54 -0600 Received: from gemini4.ieee.org (gemini4.ieee.org [140.98.193.189]) by cs-ems2.ieee.org (8.13.8/8.12.11) with ESMTP id o879Jp1H006676 for ; Tue, 7 Sep 2010 05:19:51 -0400 Received: from gemini4.ieee.org (gemini4.ieee.org [127.0.0.1]) by postfix.imss70 (Postfix) with ESMTP id 7546569CA1C for ; Tue, 7 Sep 2010 04:41:26 -0400 (EDT) Received: from hormel8.ieee.org (hormel8.ieee.org [140.98.193.231]) by gemini4.ieee.org (Postfix) with ESMTP id 506ED69CA18 for ; Tue, 7 Sep 2010 04:41:26 -0400 (EDT) Received: from mars.hitrost.net (mars.hitrost.net [91.185.193.39]) by hormel8.ieee.org (8.13.8/8.13.8/Debian-3) with ESMTP id o879JeNg027855 for ; Tue, 7 Sep 2010 05:19:40 -0400 Received: from lk.92.63.17.213.dc.cable.static.lj-kabel.net ([92.63.17.213] helo=Celebrian.local) by mars.hitrost.net with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.72) (envelope-from ) id 1OsuL1-0000S1-Lk; Tue, 07 Sep 2010 11:19:35 +0200 Message-ID: <4C8603FB.9080409@christianmoe.com> Date: Tue, 07 Sep 2010 11:20:59 +0200 From: Christian Moe Reply-To: mail@christianmoe.com User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X 10.5; en-US; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2 MIME-Version: 1.0 To: Juan CC: emacs-orgmode@gnu.org, Inquisitive Scientist Subject: Re: [Orgmode] questions about table mode and spreadsheets References: <4C856EB3.9060608@christianmoe.com> <20100907005653.GE2414@soloJazz.com> In-Reply-To: <20100907005653.GE2414@soloJazz.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Antivirus-Scanner: Clean mail though you should still use an Antivirus X-Bayes-Prob: 0.0001 (Score 0, tokens from: @@RPTN) X-CanIt-Geo: ip=91.185.193.39; country=SI; region=09; city=Celje; latitude=46.2361; longitude=15.2675; http://maps.google.com/maps?q=46.2361,15.2675&z=6 X-IEEE-UCE-Settings: pechiar@computer_society (inherits from 32_HI_TAG-LO_BLOCK,default) X-IEEE-UCE-Stats-ID: Bayes signature not available X-Scanned-By: IEEE UCE Filtering Service (UCE . ieee . org) on 140.98.193.231 X-Identified-User: {1652:box519.bluehost.com:solojazz:solojazz.com} {sentby:spamassassin for local delivery to identified user} 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 > --6c2NcOVqGQ03X4Wi Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ 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 --6c2NcOVqGQ03X4Wi--