1

I have one table called GRADING containing names and points entered by hand, and another table (FOO) containing the grading scheme. Is there a simple formula which allows to lookup the number of points and assign the corresponding grade from FOO? I have tried many variation of the formula $3=remote(FOO,@@#$2). If we could replace @# by $2 I think it would do the trick, but everything gets messed up.

#+NAME: GRADING
| Name  | Points | Grade |
|-------+--------+-------|
| Alice |     10 |       |
| Rob   |     20 |       |
| Bob   |     30 |       |
| Jack  |     35 |       |
| Marc  |     12 |       |
| Marie |     65 |       |
| Fanny |     12 |       |
#+TBLFM: $3=remote(FOO,@@#$2)


#+NAME: FOO
| Number of points | Grade |
|------------------+-------|
|                0 |     1 |
|                1 |     1 |
|                2 |     1 |
|                3 |   1.5 |
|                4 |   1.5 |
|                5 |   1.5 |
|                6 |   1.5 |
|                7 |   1.5 |
|                8 |   1.5 |
|                9 |   1.5 |
|               10 |     2 |
|               11 |     2 |
|               12 |     2 |
|               13 |     2 |
|               14 |     2 |
|               15 |     2 |
|               16 |     2 |
|               17 |   2.5 |
|               18 |   2.5 |
|               19 |   2.5 |
|               20 |   2.5 |
|               21 |   2.5 |
|               22 |   2.5 |
|               23 |   2.5 |
|               24 |     3 |
|               25 |     3 |
|               26 |     3 |
|               27 |     3 |
|               28 |     3 |
|               29 |     3 |
|               30 |     3 |
|               31 |     3 |
|               32 |     3 |
|               33 |   3.5 |
|               34 |   3.5 |
|               35 |   3.5 |
|               36 |   3.5 |
|               37 |   3.5 |
|               38 |   3.5 |
|               39 |   3.5 |
|               40 |   3.5 |
|               41 |   3.5 |
|               42 |     4 |
|               43 |     4 |
|               44 |     4 |
|               45 |     4 |
|               46 |     4 |
|               47 |     4 |
|               48 |     4 |
|               49 |   4.5 |
|               50 |   4.5 |
|               51 |   4.5 |
|               52 |   4.5 |
|               53 |   4.5 |
|               54 |   4.5 |
|               55 |   4.5 |
|               56 |     5 |
|               57 |     5 |
|               58 |     5 |
|               59 |     5 |
|               60 |     5 |
|               61 |   5.5 |
|               62 |   5.5 |
|               63 |   5.5 |
|               64 |   5.5 |
|               65 |   5.5 |
|               66 |     6 |
|               67 |     6 |
|               68 |     6 |
|               69 |     6 |
|               70 |     6 |
1

Hope this will be useful to someone (have been searching for the solution for hours!). The function to use is #+TBLFM: $3='(org-lookup-first $2 '(remote(FOO,@2$1..@>$1)) '(remote(FOO,@2$2..@>$2)))

Found it by reading this answer which links to the org lookup functions documentation, which in turn links to useful examples in Worg.

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.