Re: Re: org-table and formulas

From: Carsten Dominik <carsten.dominik@gmail.com>
To: Alan Boudreault <aboudreault@mapgears.com>
Cc: "Sébastien Vauban" <wxhgmqzgwmuf@spammotel.com>, emacs-orgmode@gnu.org
Subject: Re: Re: org-table and formulas
Date: Wed, 13 Jan 2010 12:59:54 +0100	[thread overview]
Message-ID: <5378FAF3-867E-4E97-B43A-67A5D4B2CCBA@gmail.com> (raw)
In-Reply-To: <4B4DAB04.5000503@mapgears.com>

Hi Alan,

I think what you would have to do is to build a lisp function that  
creates the full reference by string manipulation, and then calls org- 
table-get-remote-range directly to retrieve the result.

That function returns a string or a list of strings, so conversion  
might be needed depending on how you use the result.

- Carsten

On Jan 13, 2010, at 12:14 PM, Alan Boudreault wrote:

> Hi Sébastien and thank you for your answer.
>
> The problem with that technique is that I'll have to name ALL the  
> cell for ALL the colomn. Here's an example of my table:
>
> #+TBLNAME: FOOD
> | Name            | Brand         | Amounts (g) | Calories | Protein  
> | Carbohydrate |  Fat |
> |-----------------+---------------+-------------+----------+--------- 
> +--------------+------|
> | Poitrine Poulet     | KirkLand      |         100 |      100  
> |      24 |            0 |    1 |
> | Capeli d'angelo    | Primo         |          85 |      300 |       
> 10 |           63 |    1 |
> | Couscous              | Clic          |          62 |      220  
> |       8 |           46 |    1 |
> | Fromage Cottage | Sealtest      |         125 |      100 |      15  
> |            8 |    1 |
> | Ficello                   | Black Diamond |          21 |       60  
> |       6 |            0 |    4 |
> | Concombre cru    |               |         100 |       15 |     
> 0.65 |         3.63 | 0.11 |
> | Clementine cru    |               |         100 |       47 |     
> 0.85 |        12.02 | 0.15 |
>
> In the table 2, I would like to be able to just set a column to ie.  
> 2 (for "Poitrine Poulet"), then I'll be able to get all the  info of  
> the different columns I need to do my calculs. I see there is also a  
> "_" option for row name, but I'm unsure on how to specify the column  
> desired with that name and how to get it evaluated if I need to  
> concat something...
>
> regards,
> Alan
>
> Sébastien Vauban wrote:
>> Hi Alan,
>>
>> Alan Boudreault wrote:
>>
>>> Here's what I'm trying to do (without success) in my spreadsheet:
>>>
>>> I have a first table :
>>>
>>> #+TBLNAME: table1
>>> | ... |... |.... |...|...
>>>
>>> and a second table:
>>>
>>> #+TBLNAME: table2
>>> | line_reference | ... |... |.... |...|...
>>>
>>> The line_reference is the line number to get some information in  
>>> the table1.
>>> So in a cell of the table2, I tried a formula like: remote(table1,@ 
>>> $1$3) ($1
>>> = the line_reference) which of course, didn't work.
>>>
>>> then I tried to use elisp: remote(table1,'(concat "@" $1  
>>> "$3")') .... but no
>>> success anyway.
>>>
>>> after all, I decided to merge my 2 tables because it looks like I  
>>> can use
>>> elisp inside a org function (remote). The last tried formula was:  
>>> '(concat
>>> "@" $1 "$3")' ,($1 = the line_reference). The problem of this  
>>> formula is
>>> only that I got the string "@2$3" inside the cell and it's not  
>>> evaluated as
>>> a formula.
>>>
>>> Could anyone give me some hint to get done what I'm trying to do?
>>>
>>
>> What I do, which works:
>>
>> - give a name to the cell in table1
>> - reference it (in table2) by its name
>>
>>
>>  #+TBLNAME: Eau
>>  |   | Date             | Relevé m3 | Consommation | Prix TVAC |
>>  |---+------------------+-----------+--------------+-----------|
>>  |   | [2009-11-15 Sun] | 2072.6263 |       0.0000 |      0.00 |
>>  |   | [2009-11-22 Sun] | 2074.1774 |       1.5511 |      1.55 |
>>  |   | [2009-11-29 Sun] | 2076.0834 |       1.9060 |      1.91 |
>>  |   | [2009-12-06 Sun] | 2077.6757 |       1.5923 |      1.59 |
>>  |   | [2009-12-13 Sun] | 2079.2140 |       1.5383 |      1.54 |
>>  |   | [2009-12-20 Sun] | 2081.4222 |       2.2082 |      2.21 |
>>  |   | [2009-12-27 Sun] | 2083.2146 |       1.7924 |      1.79 |
>>  |   | [2010-01-03 Sun] | 2085.4916 |       2.2770 |      2.28 |
>>  |   | [2010-01-10 Sun] | 2087.4283 |       1.9367 |      1.94 |
>>  | ^ |                  |           |              |       Eau |
>>  #+TBLFM: $4=@+0$-1-@-1$-1;%.4f::$5=$4*1;%.2f::@2$4=0;%.4f
>>
>>
>>  | Énergie     | Prix TVAC |
>>  |-------------+-----------|
>>  | Eau         |      1.94 |
>>  | Gaz         |    156.81 |
>>  | Électricité |     11.64 |
>>  |-------------+-----------|
>>  | Total       |    170.39 |
>>  #+TBLFM: @2$2=remote(Eau,$Eau)::@3$2=remote(Gaz, 
>> $Gaz)::@4$2=remote(Elec,$Elec)::@5$2=vsum(@-I..@-II);%.2f
>>
>> Best regards,
>>  Seb
>>
>>
>
>
> -- 
> Alan Boudreault
> Mapgears
> http://www.mapgears.com/
>
>
> _______________________________________________
> Emacs-orgmode mailing list
> Please use `Reply All' to send replies to the list.
> Emacs-orgmode@gnu.org
> http://lists.gnu.org/mailman/listinfo/emacs-orgmode

- Carsten