0

I am curious as to whether it's possible to automate the TBLFM in a particular way. This example illustrates the problem I am asking about:

|--------------------------------------------------------------------------------+-------+------+-------|
| ITEM                                                                           | Avail | Mult |   Tot |
|--------------------------------------------------------------------------------+-------+------+-------|
| Rubric                                                                         |   100 |    0 |   100 |
| Understanding of key issues and challenges in survey design                    |    30 |    1 |    30 |
| Discussion of alternative approaches to minimise survey error                  |    15 |    1 |    15 |
| Evidence of maximising quality of the survey and building trust                |    20 |    1 |    20 |
| Presentation                                                                   |    15 |    1 |    15 |
| Information sources and references to statistics legislation and documentation |    20 |    1 |    20 |
|--------------------------------------------------------------------------------+-------+------+-------|
| Some Student Name                                                              |   100 |    0 | 65.75 |
| Understanding of key issues and challenges in survey design                    |    30 |   .7 |   21. |
| Discussion of alternative approaches to minimise survey error                  |    15 |  .65 |  9.75 |
| Evidence of maximising quality of the survey and building trust                |    20 |   .7 |   14. |
| Presentation                                                                   |    15 |   .6 |    9. |
| Information sources and references to statistics legislation and documentation |    20 |   .6 |   12. |
|--------------------------------------------------------------------------------+-------+------+-------|
#+TBLFM: $4=$3*$2 :: @2$4=vsum(@II+1$4..@III$4) :: @8$4=vsum(@III+1$4..@IIII$4)

Here, I am explicitly updating the @2$4 and @8$4 blocks.

What I would like to do would be to automatically update "the cell after each hline in the 4th column" (so, something like @2+6k$4 for all k that make sense). I'd like to assign this the value "sum of all numbers in the following lines, up to the next hline".

In my actual use case, I'm populating the table with column view, and I have many more segments, requiring a monster TBLFM which I have so far constructed by hand:

#+BEGIN: columnview :hlines 1 :id global
#+TBLFM: $4=$3*$2::@2$4=vsum(@I$4..@II$4):
:@8$4=vsum(@II$4..@III$4):
:@14$4=vsum(@III$4..@IIII$4):
:@20$4=vsum(@IIII$4..@IIIII$4):
:@26$4=vsum(@IIIII$4..@IIIIII$4):
:@32$4=vsum(@IIIIII$4..@IIIIIII$4):
:@38$4=vsum(@IIIIIII$4..@IIIIIIII$4):
:@44$4=vsum(@IIIIIIII$4..@IIIIIIIII$4):
:@50$4=vsum(@IIIIIIIII$4..@IIIIIIIIII$4):
:@56$4=vsum(@IIIIIIIIII$4..@IIIIIIIIIII$4):
:@62$4=vsum(@IIIIIIIIIII$4..@IIIIIIIIIIII$4):
:@68$4=vsum(@IIIIIIIIIIII$4..@IIIIIIIIIIIII$4)
#+END:

Luckily I am teaching a relatively small class this time, but in future it would be nice to have the gradebook work in a more automated way.

Update: Input to the columnview table is via Org mode with the following format (in general Mult is between 0 and 1):

* Rubric

** Understanding of key issues and challenges in survey design
:PROPERTIES:
:Avail: 30
:Mult: 1
:END:
** Discussion of alternative approaches to minimise survey error
:PROPERTIES:
:Avail: 15
:Mult: 1
:END:
** Evidence of maximising quality of the survey and building trust
:PROPERTIES:
:Avail: 20
:Mult: 1
:END:
** Presentation
:PROPERTIES:
:Avail: 15
:Mult: 1
:END:
** Information sources and references to statistics legislation and documentation
:PROPERTIES:
:Avail: 20
:Mult: 1
:END:
2
  • I would reorganize the table: AFAIK, Org mode does not have any facilities for specifying hlines through a variable in a formula, so that makes looping over them impossible. What does the input to the column view look like?
    – NickD
    Jan 6 at 16:36
  • 1
    @NickD, thanks for the comment. I updated my question with info about the column view inputs! When I was researching the question I came across github.com/tbanel/orgaggregate which I haven't tried. It looks like it would allow me to calculate the products in one table, and then aggregate them in another table.
    – Joe Corneli
    Jan 6 at 18:08

1 Answer 1

1

I would suggest

  • to separate sums from raw data in a new table
  • to put student name in its own column
#+name: rawdata
| Name              | ITEM                                                                           | Avail | Mult |   Tot |
|-------------------+--------------------------------------------------------------------------------+-------+------+-------|
| Rubric            | Understanding of key issues and challenges in survey design                    |    30 |    1 |    30 |
| Rubric            | Discussion of alternative approaches to minimise survey error                  |    15 |    1 |    15 |
| Rubric            | Evidence of maximising quality of the survey and building trust                |    20 |    1 |    20 |
| Rubric            | Presentation                                                                   |    15 |    1 |    15 |
| Rubric            | Information sources and references to statistics legislation and documentation |    20 |    1 |    20 |
|-------------------+--------------------------------------------------------------------------------+-------+------+-------|
| Some Student Name | Understanding of key issues and challenges in survey design                    |    30 |   .7 |   21. |
| Some Student Name | Discussion of alternative approaches to minimise survey error                  |    15 |  .65 |  9.75 |
| Some Student Name | Evidence of maximising quality of the survey and building trust                |    20 |   .7 |   14. |
| Some Student Name | Presentation                                                                   |    15 |   .6 |    9. |
| Some Student Name | Information sources and references to statistics legislation and documentation |    20 |   .6 |   12. |
|-------------------+--------------------------------------------------------------------------------+-------+------+-------|

Now you can aggregate data in a new table with the orgtbl-aggregate package available on Melpa.

#+BEGIN: aggregate :table "rawdata" :cols "Name vsum(Avail) vsum(Avail*Mult)"
| Name              | vsum(Avail) | vsum(Avail*Mult) |
|-------------------+-------------+------------------|
| Rubric            |         100 |              100 |
| Some Student Name |         100 |            65.75 |
#+END:

You can add as many students or courses as yous want.

Just type C-c C-c in the #+BEGIN: aggregate... line.

You may also aggregate on courses, with an average instead of a sum:

#+BEGIN: aggregate :table "rawdata" :cols "ITEM vmean(Avail)"
| ITEM                                                                           | vmean(Avail) |
|--------------------------------------------------------------------------------+--------------|
| Understanding of key issues and challenges in survey design                    |           30 |
| Discussion of alternative approaches to minimise survey error                  |           15 |
| Evidence of maximising quality of the survey and building trust                |           20 |
| Presentation                                                                   |           15 |
| Information sources and references to statistics legislation and documentation |           20 |
#+END:

The documentation for the package orgtbl-aggregat is here: https://github.com/tbanel/orgaggregate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.