Re: [O] Preserving the geometry of a table range

[Michael Brand]

Hi Jarmo

On Sun, Aug 26, 2012 at 8:54 AM, Jarmo Hurri jarmo.hu...@syk.fi wrote:
 I ran into this problem while trying to make a contribution by
 implementing functions corresponding to vlookup and hlookup in
 traditional spreadsheets.  I have only used relatively basic properties
 of org, so there might be a simple solution that I just don't know.

Only now I see that this second part with the actual problem of your
original post is still unanswered. Does this help?:

| key (ordered) | val |   | val (ordered) | key |
|---+-+---+---+-|
| 8 | foo |   | bar   |  42 |
|42 | bar |   | foo   |   8 |
#+TBLFM: $5 = '(nth (position $4 '(@$2..@$2) :test 'equal) '(@$1..@$1))

Michael

[O] Preserving the geometry of a table range

[Jarmo Hurri]

Greetings.

If you pass a table range to an elisp function in a tblfm - say, using
remote - the values in the range seem to be passed to the function as a
single list. Thus the geometry of the region is lost. Is it possible to
return a list of lists, e.g., a list of rows instead? Is this supported
by some notation in a tblfm formula? If not, could we have something
like this?

I ran into this problem while trying to make a contribution by
implementing functions corresponding to vlookup and hlookup in
traditional spreadsheets.  I have only used relatively basic properties
of org, so there might be a simple solution that I just don't know.

--

Jarmo

Re: [O] Preserving the geometry of a table range

[Michael Brand]

Hi Jarmo

could you make a small example with the table content before and after
the yet to be defined formula has been applied, maybe with a
pseudo-formula?

Michael

Re: [O] Preserving the geometry of a table range

[Jarmo Hurri]

Greetings Michael.

I can try. Here is an example of the desired low-level functionality
with a simple function. The lookup functions would be built on this.

Does this help?

* an example of low level functionality
  Here is an example of an elisp function working on a range as a list
  of rows. This one first finds the product of the elements on each
  row, and then sums up these products.
#+BEGIN_SRC emacs-lisp
(defun sum-row-prods (rows)
  (apply '+ (mapcar (lambda (row) (apply '* row)) rows)))
#+END_SRC

#+RESULTS:
: sum-row-prods

  Then here we have a table in which the element at @4$1 is given by
  applying the function sum-row-prods to a specific list of
  lists. This works already in org mode, since in the formula for @4$1
  the list of lists is given as a constant. The formula for @4$2
  illustrates the desired functionality, that is, being able to apply
  the function sum-row-prods to range @2$1..@3$2. The result should be
#+BEGIN_SRC emacs-lisp
(sum-row-prods '((2 3) (4 5)))
#+END_SRC

#+RESULTS:
: 26

  but now it naturally gives an error, since the argument to
  sum-row-prods is a list, not a list of lists (rows).

  |  a |  b |
  |+|
  |  2 |  3 |
  |  4 |  5 |
  | 10 | #ERROR |
  #+tblfm: @4$1='(sum-row-prods '((-4 1) (-2 -7)));N::@4$2='(sum-row-prods 
'(@2$1..@3$2));N

  I don't know what the new syntax for referring to a range as a list
  of rows should look like.

--

Jarmo

Re: [O] Preserving the geometry of a table range

[Michael Brand]

Hi Jarmo

On Sun, Aug 26, 2012 at 10:45 AM, Jarmo Hurri jarmo.hu...@syk.fi wrote:

 Does this help?

Yes.

   but now it naturally gives an error, since the argument to
   sum-row-prods is a list, not a list of lists (rows).

AFAIK there is no way to get the list of lists.

But if you didn’t already you may consider to separate the calculation
of rows and columns into two steps which I think should be possible in
most cases and use something like this:

| a | b | prod |
|---+---+--|
| 2 | 3 |6 |
| 4 | 5 |   20 |
|   |   |   26 |
#+TBLFM: $3 = '(* $1..$2); N :: @$3 = '(+ @2..@3); N

or with Calc formulas:

#+TBLFM: $3 = vprod($1..$2) :: @$3 = vsum(@2..@3)

Michael