Is there a simple way to "drop" scores from a mean function in org-mode?

Question

I have an org table:

| Student | Score 1 | Score 2 | Score 3 | Score 4 | Score 5 |   Mean | Mean (Lowest Two Dropped) |
|---------+---------+---------+---------+---------+---------+--------+---------------------------|
| John    |    5.74 |   84.43 |    8.32 |   46.71 |   37.94 | 36.628 |                           |
| Paul    |   70.13 |   83.51 |   85.95 |    8.78 |   55.53 |  60.78 |                           |
| George  |   66.28 |   20.80 |    2.63 |   69.76 |   46.85 | 41.264 |                           |
| Ringo   |   50.93 |   66.09 |   44.76 |   19.50 |   16.32 |  39.52 |                           |
#+TBLFM: @2$7..@>$7=vmean($2..$6)

Column 1 lists a student. Columns 2-6 list each student's score on an assignment.

The code vmean($2..$6) can be used to compute each student's average score on all five assignments.

I'd like to be generous to my students and drop the lowest n scores (in this case n=2). I'm having trouble figuring out how to accomplish this.

Is there a simple way to drop the lowest n scores for each student and take the new average?

Answer 1

A way to drop the lowest N grades is to sort the vector, and choose a subvector.

|   | Student | Score 1 | Score 2 | Score 3 | Score 4 | Score 5 |   Mean | Mean (Lowest Two Dropped) |
| $ | ndrop=2 |         |         |         |         |         |        |                           |
|---+---------+---------+---------+---------+---------+---------+--------+---------------------------|
| # | John    |    5.74 |   84.43 |    8.32 |   46.71 |   37.94 | 36.628 |                     56.36 |
| # | Paul    |   70.13 |   83.51 |   85.95 |    8.78 |   55.53 |  60.78 |                     79.86 |
| # | George  |   66.28 |   20.80 |    2.63 |   69.76 |   46.85 | 41.264 |                     60.96 |
| # | Ringo   |   50.93 |   66.09 |   44.76 |   19.50 |   16.32 |  39.52 |                     53.93 |
| # | test    |      50 |      40 |      30 |      20 |      10 |     30 |                     40.00 |
#+TBLFM: $9=vmean(subvec(sort($<<<..$-2),($ndrop+1),0));%.2f::@3$8..@>$8=vmean($3..$7)

I added recalc marks in order to set the variable ndrop, and have automatic recalculation working.

The formula $9=vmean(subvec(sort($<<<..$-2),($ndrop+1),0));%.2f works like this:

• sort($<<<..$-2) yields a sorted vector of scores, from the third column to the second column to the left.
• subvec(...) returns the elements from $ndrop+1 to the end of the sorted scores.
• vmean(...) is the mean of the subvector.
• ;%.2f is formatting: keep 2 decimal places.