Re: [Orgmode] Spreadsheet and weighted means

[Carsten Dominik]

Hi Nicolas,

On Oct 1, 2008, at 6:46 PM, Nicolas Goaziou wrote:

> Carsten Dominik <address@hidden> writes:
>
> Hello,
>
> > 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)))
>
> Well, thank you very much: it does the job.
>
> I don't want to be picky but I'll investigate on a way to have an  
> empty
> string instead of a sorry 0 whenever a student hasn't sat for any  
> exam.

Well, you can do this by leaving the formatting to the function  
instead of the formula under the table:


(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)))))
   (if (= vsum 0) "" (format "%.1f" (/ vsum wsum)))))


The you could use this as your equation:

|           | Coeff. |    0.2 |    0.5 |      1 |
|-----------+--------+--------+--------+--------|
| Name      |        | Test 1 | Test 2 | Test 3 |
|-----------+--------+--------+--------+--------|
| Student A |   10.0 |     15 |     12 |      8 |
| Student B |   12.7 |        |     16 |     11 |
| Student C |        |        |        |        |
#+TBLFM: $2='(my-wmean '($3..$5) '(@address@hidden));E

> Finally, I wondered if it would be useful to make it built-in as
> weighted means are somewhat popular in education.

Well, I could do that, of course.  But which version of this  
function?  What ouput etc?
I guess this would then be the original version, which returns a  
number, and which returns 0 if the student has done absolutely  
nothing....

- Carsten