From mboxrd@z Thu Jan 1 00:00:00 1970 From: Carsten Dominik Subject: Re: Spreadsheet and weighted means Date: Mon, 29 Sep 2008 10:43:14 +0200 Message-ID: <9136F031-BBCD-4A80-A117-6047C5D3F777@uva.nl> References: <87wsh7w589.fsf@selenimh.orion.org> Mime-Version: 1.0 (Apple Message framework v929.2) Content-Type: text/plain; charset=WINDOWS-1252; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1KkEX1-0001UQ-Er for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:55:03 -0400 Received: from exim by lists.gnu.org with spam-scanned (Exim 4.43) id 1KkEX0-0001UE-LL for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:55:03 -0400 Received: from [199.232.76.173] (port=36757 helo=monty-python.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1KkEX0-0001UB-GA for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:55:02 -0400 Received: from postduif.ic.uva.nl ([145.18.40.180]:39294) by monty-python.gnu.org with esmtp (Exim 4.60) (envelope-from ) id 1KkELi-00058Q-Aa for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:43:22 -0400 In-Reply-To: <87wsh7w589.fsf@selenimh.orion.org> 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: Nicolas Goaziou Cc: emacs-orgmode@gnu.org On Sep 20, 2008, at 9:00 AM, Nicolas Goaziou wrote: > Hello, > > I stumbled upon this problem : I'd like to compute the weighted mean =20= > of > some values, even though cells might be empty. In fact, I'm aiming at > something like this : > > | | Coeff. | 0.2 | 0.5 | 1 | > |-----------+--------+--------+--------+--------| > | Name | Mean | Test 1 | Test 2 | Test 3 | > |-----------+--------+--------+--------+--------| > | Student A | 10 | 15 | 12 | 8 | > | Student B | 12 | | 16 | 10 | > > where 10=3D(15*0.2+12*0.5+8*1)/(0.2+0.5+1) and = 12=3D(16*0.5+10*1)/(0.5+1) > > I just can't guess what has to be put in @3$2 as a column formula to > calculate those mean means=85 I perhaps have overlooked something =20 > simple. > Anyway, if anyone has a clue here, I will be pleased to hear it. Hi Nicolas, there s no builtin way to deal with this, in particular =20 with the fact that you want to treat empty fields as non-existing, and =20= therefore also to ignore the corresponding weight. You cou write a Lisp function to do this, though: (defun my-wmean (values weights) (let ((vsum 0) (wsum 0)) (while (and values weights) (setq v (pop values) w (pop weights)) (unless (equal "" v) (setq vsum (+ vsum (* (string-to-number w) (string-to-number = v))) wsum (+ wsum (string-to-number w))))) (/ vsum wsum))) The you could use this as your equation: | | Coeff. | 0.2 | 0.5 | 1 | |-----------+--------+--------+--------+--------| | Name | 0 | Test 1 | Test 2 | Test 3 | |-----------+--------+--------+--------+--------| | Student A | 10 | 15 | 12 | 8 | | Student B | 12 | | 16 | 10 | #+TBLFM: $2=3D'(wmean '($3..$5) '(@1$3..@1$5));E%d Note the use of the E flag, to make sure empty fields are not skipped, =20= but passed through as empty strings....... Hope this helps. - Carsten > > > --=20 > Nicolas Goaziou > > > _______________________________________________ > Emacs-orgmode mailing list > Remember: use `Reply All' to send replies to the list. > Emacs-orgmode@gnu.org > http://lists.gnu.org/mailman/listinfo/emacs-orgmode