lookup functions in spreadsheet/table

lookup functions in spreadsheet/table

[Neil Hepburn]

Greetings

I am looking for a lookup function for tables in org-mode. In the past I have used a spreadsheet to keep track of student grades and then had a table that contained cut points and grades. Assigning grades was simple because the formula would simply look up the student's grade in the cut points table and assign the appropriate letter grade.  In Yicksel, also known as Excel, the function would be =vlookup(<target>,<lookup range>,<column to return>). If I need to "tweak" my grade distribution to satisfy an anal retentive Registrar type, I can do so by altering the cut points. Now that I am using org-mode and emacs for everything else, thought it would be interesting to keep my grade book in it too. 

I've looked at the org-mode manual for the spreadsheet functions as well as looked in the GNU Emacs Calc manual for such a function but to no avail. Does such a function exist in org-mode?

Cheers,
Neil



=============================================
Neil Hepburn, Economics Instructor
Department of Social Sciences, Augustana Faculty
University of Alberta
4901-46 Avenue
Camrose, Alberta  T4V 2R3

Phone (780) 679-1588
email nhepburn@ualberta.ca

No trees were harmed in creating this message. (However, millions of electrons were terribly disturbed.)

Re: lookup functions in spreadsheet/table

[Juan]

Hi Neil,

One possible solution is to have a babel block defining an elisp
function for note to grade transformation, and then using that
function in your table.

Something like this (partly tested):

#+begin_src emacs-lisp
(defun grade (x)
  (cond
   ( (< x 20) "C")
   ( (< x 50) "B")
   ( t "A")
   ))
#+end_src


| 1  | C |
| 22 | B |
| 44 | B |
| 77 | A |
#+TBLFM: $2='(grade $1);N

This way you get notes and grades in a single table.

If you need a separate table with just names and grades (no notes)
then probably babel is the way to go to explicitly process the table
and generate a new one.

Hope it helps.

Regards,
.j.

On Fri, Jul 30, 2010 at 03:06:49PM -0600, Neil Hepburn wrote:
> I am looking for a lookup function for tables in org-mode. In the
> past I have used a spreadsheet to keep track of student grades and
> then had a table that contained cut points and grades. Assigning
> grades was simple because the formula would simply look up the
> student's grade in the cut points table and assign the appropriate
> letter grade. In Yicksel, also known as Excel, the function would be
> =vlookup(<target>,<lookup range>,<column to return>). If I need to
> "tweak" my grade distribution to satisfy an anal retentive Registrar
> type, I can do so by altering the cut points. Now that I am using
> org-mode and emacs for everything else, thought it would be
> interesting to keep my grade book in it too.