* How to do a sum of products in a table?
@ 2013-06-14 14:09 Alan Schmitt
2013-06-14 15:13 ` Rasmus
0 siblings, 1 reply; 5+ messages in thread
From: Alan Schmitt @ 2013-06-14 14:09 UTC (permalink / raw)
To: emacs-orgmode
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
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: How to do a sum of products in a table?
2013-06-14 14:09 How to do a sum of products in a table? Alan Schmitt
@ 2013-06-14 15:13 ` Rasmus
2013-06-14 16:49 ` Alan Schmitt
0 siblings, 1 reply; 5+ messages in thread
From: Rasmus @ 2013-06-14 15:13 UTC (permalink / raw)
To: emacs-orgmode
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!
^ permalink raw reply [flat|nested] 5+ messages in thread* Re: How to do a sum of products in a table?
2013-06-14 15:13 ` Rasmus
@ 2013-06-14 16:49 ` Alan Schmitt
2013-06-14 17:34 ` Rasmus
0 siblings, 1 reply; 5+ messages in thread
From: Alan Schmitt @ 2013-06-14 16:49 UTC (permalink / raw)
To: Rasmus; +Cc: emacs-orgmode
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
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: How to do a sum of products in a table?
2013-06-14 16:49 ` Alan Schmitt
@ 2013-06-14 17:34 ` Rasmus
2013-06-14 18:53 ` Alan Schmitt
0 siblings, 1 reply; 5+ messages in thread
From: Rasmus @ 2013-06-14 17:34 UTC (permalink / raw)
To: emacs-orgmode
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!
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: How to do a sum of products in a table?
2013-06-14 17:34 ` Rasmus
@ 2013-06-14 18:53 ` Alan Schmitt
0 siblings, 0 replies; 5+ messages in thread
From: Alan Schmitt @ 2013-06-14 18:53 UTC (permalink / raw)
To: Rasmus; +Cc: emacs-orgmode
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
^ permalink raw reply [flat|nested] 5+ messages in thread
end of thread, other threads:[~2013-06-14 18:53 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2013-06-14 14:09 How to do a sum of products in a table? Alan Schmitt
2013-06-14 15:13 ` Rasmus
2013-06-14 16:49 ` Alan Schmitt
2013-06-14 17:34 ` Rasmus
2013-06-14 18:53 ` Alan Schmitt
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).