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:44:10 +0200 Message-ID: <28508EE0-1CAF-4624-8304-456670929C8B@gmail.com> References: <87wsh7w589.fsf@selenimh.orion.org> <9136F031-BBCD-4A80-A117-6047C5D3F777@uva.nl> 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 1KkEMc-0004HV-Qt for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:44:18 -0400 Received: from exim by lists.gnu.org with spam-scanned (Exim 4.43) id 1KkEMa-0004Eb-TL for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:44:18 -0400 Received: from [199.232.76.173] (port=52945 helo=monty-python.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1KkEMa-0004Dt-DT for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:44:16 -0400 Received: from ug-out-1314.google.com ([66.249.92.175]:53884) by monty-python.gnu.org with esmtp (Exim 4.60) (envelope-from ) id 1KkEMZ-0005Up-QU for emacs-orgmode@gnu.org; Mon, 29 Sep 2008 04:44:16 -0400 Received: by ug-out-1314.google.com with SMTP id z36so411829uge.17 for ; Mon, 29 Sep 2008 01:44:13 -0700 (PDT) In-Reply-To: <9136F031-BBCD-4A80-A117-6047C5D3F777@uva.nl> 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: Carsten Dominik Cc: emacs-orgmode@gnu.org On Sep 29, 2008, at 10:43 AM, Carsten Dominik wrote: > > On Sep 20, 2008, at 9:00 AM, Nicolas Goaziou wrote: > >> Hello, >> >> I stumbled upon this problem : I'd like to compute the weighted =20 >> mean 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, =20 > and 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 Typo: the function call must be `my-wmean', not just `wmean'. - CarstenG=