Re: Spreadsheet and weighted means

From: Carsten Dominik <dominik@uva.nl>
To: Nicolas Goaziou <n.goaziou@neuf.fr>
Cc: emacs-orgmode@gnu.org
Subject: Re: Spreadsheet and weighted means
Date: Mon, 29 Sep 2008 10:43:14 +0200	[thread overview]
Message-ID: <9136F031-BBCD-4A80-A117-6047C5D3F777@uva.nl> (raw)
In-Reply-To: <87wsh7w589.fsf@selenimh.orion.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  
> 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=(15*0.2+12*0.5+8*1)/(0.2+0.5+1) and 12=(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… I perhaps have overlooked something  
> 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  
with the fact that you want to treat empty fields as non-existing, 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='(wmean '($3..$5) '(@1$3..@1$5));E%d

Note the use of the E flag, to make sure empty fields are not skipped,  
but passed through as empty strings.......

Hope this helps.

- Carsten

>
>
> -- 
> 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