How to do a sum of products in a table?

How to do a sum of products in a table?

[Alan Schmitt]

Hello,

I'm grading an exam, and I'm trying to use org instead of Numbers to
tabulate the results. For each student, I'll input how well he answered
to the question, and his grade will be (sum of mark * coefficient),
where each question has a different coefficient (in Numbers I was using
the SUMPRODUCT function).

Is there a way to do this in org mode?

Thanks,

Alan

Re: How to do a sum of products in a table?

[Rasmus]

Hi Alan,

> I'm grading an exam, and I'm trying to use org instead of Numbers to
> tabulate the results. For each student, I'll input how well he answered
> to the question, and his grade will be (sum of mark * coefficient),
> where each question has a different coefficient (in Numbers I was using
> the SUMPRODUCT function).
>
> Is there a way to do this in org mode?

Sure, but please provide an example.  E.g. is each student a column?
Or is the dotproduct of two vectors where mark is just one

Here's a prtty simple example

|   | nr. | score     | score |
|---+-----+-----------+-------|
| # |   1 | [1, 2, 3] |   4.2 |
| # |   2 | [3, 4, 5] |   7.8 |
#+TBLFM: $4=$3*[.3, .6, .9]

It would probably be nicer to have the weighting matrix externally
defined.  Perhaps you can use #+CONSTANTS or the remote function.  If
you prefer you can also let each

A more advance example is given in the manual:

  http://orgmode.org/org.html#Advanced-features

     |---+---------+--------+--------+--------+-------+------|
     |   | Student | Prob 1 | Prob 2 | Prob 3 | Total | Note |
     |---+---------+--------+--------+--------+-------+------|
     | ! |         |     P1 |     P2 |     P3 |   Tot |      |
     | # | Maximum |     10 |     15 |     25 |    50 | 10.0 |
     | ^ |         |     m1 |     m2 |     m3 |    mt |      |
     |---+---------+--------+--------+--------+-------+------|
     | # | Peter   |     10 |      8 |     23 |    41 |  8.2 |
     | # | Sam     |      2 |      4 |      3 |     9 |  1.8 |
     |---+---------+--------+--------+--------+-------+------|
     |   | Average |        |        |        |  25.0 |      |
     | ^ |         |        |        |        |    at |      |
     | $ | max=50  |        |        |        |       |      |
     |---+---------+--------+--------+--------+-------+------|
     #+TBLFM: $6=vsum($P1..$P3)::$7=10*$Tot/$max;%.1f::$at=vmean(@-II..@-I);%.1f

Refer to

  http://orgmode.org/org.html#The-spreadsheet

–Rasmus

-- 
Hooray!

Re: How to do a sum of products in a table?

[Alan Schmitt]

Hi Rasmus,

Rasmus writes:

> Sure, but please provide an example.

Yes, I should have done that. Here is the beginning of the grading (I
removed many columns which are identical).

| Name  | Grade | Question 1 | Question 2 | Question 3 |
| Coeff |     6 |          1 |          3 | 2          |
|-------+-------+------------+------------+------------|
| Foo   |       |        1/2 |          1 | 1/4        |
| Bar   |       |          1 |          1 | 0           |
#+TBLFM: @2$2=vsum($3..$>)

I would like the grade of Foo to be 10 * (1/2 * 1 + 1 * 3 + 1/4 * 2)/6
and Bar to be 10 * (1 * 1 + 1 * 3 + 0 * 2)/6

> E.g. is each student a column?

Each student is a row, an each question is a column.

> A more advance example is given in the manual:
>
>   http://orgmode.org/org.html#Advanced-features

Thanks, but I does not seem to do what I want. I prefer to keep the
coefficient in the column with the question name so that I can easily
know what it refers to when I adjust it.

Thanks again,

Alan

Re: How to do a sum of products in a table?

[Rasmus]

Alan Schmitt <alan.schmitt@polytechnique.org> writes:

> | Name | Grade | Question 1 | Question 2 | Question 3 |
> | Coeff | 6 | 1 | 3 | 2 |
> |-------+-------+------------+------------+------------|
> | Foo | | 1/2 | 1 | 1/4 |
> | Bar | | 1 | 1 | 0 |
> #+TBLFM: @2$2=vsum($3..$>)
>
> I would like the grade of Foo to be 10 * (1/2 * 1 + 1 * 3 + 1/4 * 2)/6
> and Bar to be 10 * (1 * 1 + 1 * 3 + 0 * 2)/6

both are ≈ 6.67.


> Thanks, but I does not seem to do what I want. I prefer to keep the
> coefficient in the column with the question name so that I can easily
> know what it refers to when I adjust it.

So perhaps you'd want something like this

|   | Name      |     Grade | Question 1 | Question 2 | Question 3 |
| ! |           |     grade | q1         |            | q3         |
| _ |           |         g |            |            |            |
| / | Coeff     |         6 | 1          |          3 | 2          |
|---+-----------+-----------+------------+------------+------------|
| * | Foo       |      6.67 | 1/2        |          1 | 1/4        |
| * | Bar       |      6.67 | 1          |          1 | 0          |
| * | Baz       |       7.5 | 1          |        3/2 | -1/2       |
|---+-----------+-----------+------------+------------+------------|
| _ |           |      mean |            |        max |            |
|   | Mean      | 6.9444445 | std.dev.   | 0.48112521 |            |
| $ | Constants |  magic=10 |            |            |            |
|---+-----------+-----------+------------+------------+------------|
#+TBLFM: $g=vsum($q1..$q3):: $3=round(@I-1$q1..@I-1$q3*$q1..$q3*$magic/$g,2)::
#+TBLFM: $mean=vmean(@I$grade..II$grade)::$max=vsdev(@I$grade..II$grade)

It even exports pretty tables so you could hardly wish for more.

–Rasmus

-- 
When in doubt, do it!

Re: How to do a sum of products in a table?

[Alan Schmitt]

Rasmus writes:

> So perhaps you'd want something like this
>
> |   | Name      |     Grade | Question 1 | Question 2 | Question 3 |
> | ! |           |     grade | q1         |            | q3         |
> | _ |           |         g |            |            |            |
> | / | Coeff     |         6 | 1          |          3 | 2          |
> |---+-----------+-----------+------------+------------+------------|
> | * | Foo       |      6.67 | 1/2        |          1 | 1/4        |
> | * | Bar       |      6.67 | 1          |          1 | 0          |
> | * | Baz       |       7.5 | 1          |        3/2 | -1/2       |
> |---+-----------+-----------+------------+------------+------------|
> | _ |           |      mean |            |        max |            |
> |   | Mean      | 6.9444445 | std.dev.   | 0.48112521 |            |
> | $ | Constants |  magic=10 |            |            |            |
> |---+-----------+-----------+------------+------------+------------|
> #+TBLFM: $g=vsum($q1..$q3):: $3=round(@I-1$q1..@I-1$q3*$q1..$q3*$magic/$g,2)::
> #+TBLFM: $mean=vmean(@I$grade..II$grade)::$max=vsdev(@I$grade..II$grade)
>
> It even exports pretty tables so you could hardly wish for more.

Thanks, this is great! I had no idea multiplying ranges would do the
right thing.

Thanks again,

Alan