Table formula references

Table formula references

[Manuel Schneckenreither]

Hi,

I'd like to use following formula on like this:

  | t | y_t | y_{t+1} |
  |---+-----+---------|
  | 1 | 115 |         |
  | 2 | 121 |         |
  | 3 | 118 |         |
  | 4 | 127 |         |

  $4 = \sum_{\tau=0}^{@#-2}{0.9^{\tau} * y_{@#-2-\tau}}
  

So that's a simple sum, which just iterates over the values and uses the
index for power and to get the value of y_{4-\tau}. So @5$3 should be
0.9^0*127 + 0.9^1*118 + 0.9^2*121 + 0.9^3*115. Shouldn't be too hard I
though, but I couldn't figure it out how to implement it using
org-spreadsheets.

As I need the index I tried using subscr with no success. I can
obviously generate the list of rows used for the calculation using
@2$1..@2$#. However, as soon as I use an elsip function in the formula I
struggle with the representation of the list:

#+TBLFM: $3='(mapcar '1+ (@2$1..@2$#))

shows following debug output:

   Substitution history of formula
   Orig:   '(mapcar '1+ (@2$1..@2$3))
   $xyz->  '(mapcar '1+ (@2$1..@2$3))
   @r$c->  '(mapcar '1+ (#("1" 0 1 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table)) #("115" 0 3 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table))))
   $1->    '(mapcar '1+ (#("1" 0 1 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table)) #("115" 0 3 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table))))
   Result: #ERROR
   Format: NONE
   Final:  #ERROR

Is there a simple way to do it? Do I need to go over elsip functions,
and if so. How can I properly use a list?

I couldn't find a way on the web.


Best regards

Manuel

Re: Table formula references

[Michael Brand]

Hi Manuel

Not sure if I understand your formulas right, so check my solutions with
the formula debugger.

On Thu, Mar 30, 2017 at 10:38 AM, Manuel Schneckenreither
<manuel.schnecki@gmail.com> wrote:

> So @5$3 should be 0.9^0*127 + 0.9^1*118 + 0.9^2*121 + 0.9^3*115.

Emacs Calc has map with anonymous function and one or more vectors for
the above:

vsum(map(<0.9^#1 * #2>, [3, 2, 1, 0], [115, 121, 118, 127]))

which equals

vsum(map(<0.9^(4 - #1) * #2>, [1, 2, 3, 4], [115, 121, 118, 127]))

The rest is Org spreadsheet. See the spreadsheet section in the Org
user manual to understand this solution:

| t | y_t | y_{t+1} |
|---+-----+---------|
| 1 | 115 | 115.000 |
| 2 | 121 | 224.500 |
| 3 | 118 | 320.050 |
| 4 | 127 | 415.045 |
#+TBLFM: $3 = vsum(map(<0.9^($1 - #1) * #2>, @I$1..@0$1, @I$2..@0$2)); f-3

The same with Emacs Lisp:

| t | y_t | y_{t+1} |
|---+-----+---------|
| 1 | 115 | 115.000 |
| 2 | 121 | 224.500 |
| 3 | 118 | 320.050 |
| 4 | 127 | 415.045 |
#+TBLFM: $3 = '(apply #'+ (cl-mapcar (lambda (tau y-tau) (* (expt 0.9
(- $1 tau)) y-tau)) '(@I$1..@0$1) '(@I$2..@0$2))); N %.3f

Michael

Re: Table formula references

[Manuel Schneckenreither]

[-- Attachment #1: Type: text/plain, Size: 1289 bytes --]

Thanks, I'll take a look.

On Sun, Apr 2, 2017, 19:11 Michael Brand <michael.ch.brand@gmail.com> wrote:

> Hi Manuel
>
> Not sure if I understand your formulas right, so check my solutions with
> the formula debugger.
>
> On Thu, Mar 30, 2017 at 10:38 AM, Manuel Schneckenreither
> <manuel.schnecki@gmail.com> wrote:
>
> > So @5$3 should be 0.9^0*127 + 0.9^1*118 + 0.9^2*121 + 0.9^3*115.
>
> Emacs Calc has map with anonymous function and one or more vectors for
> the above:
>
> vsum(map(<0.9^#1 * #2>, [3, 2, 1, 0], [115, 121, 118, 127]))
>
> which equals
>
> vsum(map(<0.9^(4 - #1) * #2>, [1, 2, 3, 4], [115, 121, 118, 127]))
>
> The rest is Org spreadsheet. See the spreadsheet section in the Org
> user manual to understand this solution:
>
> | t | y_t | y_{t+1} |
> |---+-----+---------|
> | 1 | 115 | 115.000 |
> | 2 | 121 | 224.500 |
> | 3 | 118 | 320.050 |
> | 4 | 127 | 415.045 |
> #+TBLFM: $3 = vsum(map(<0.9^($1 - #1) * #2>, @I$1..@0$1, @I$2..@0$2)); f-3
>
> The same with Emacs Lisp:
>
> | t | y_t | y_{t+1} |
> |---+-----+---------|
> | 1 | 115 | 115.000 |
> | 2 | 121 | 224.500 |
> | 3 | 118 | 320.050 |
> | 4 | 127 | 415.045 |
> #+TBLFM: $3 = '(apply #'+ (cl-mapcar (lambda (tau y-tau) (* (expt 0.9
> (- $1 tau)) y-tau)) '(@I$1..@0$1) '(@I$2..@0$2))); N %.3f
>
> Michael
>

[-- Attachment #2: Type: text/html, Size: 2556 bytes --]

Re: Table formula references

[Eric S Fraga]

[-- Attachment #1: Type: text/plain, Size: 465 bytes --]

I thought the following would work but org seems to reorder the vector:

  | t | y_t |  0.9^t | y_{t+1} |
  |---+-----+--------+---------|
  | 1 | 115 |    0.9 |     0.9 |
  | 2 | 121 |   0.81 |    2.52 |
  | 3 | 118 |  0.729 |   4.707 |
  | 4 | 127 | 0.6561 |  7.3314 |
  #+TBLFM: $3=0.9^$1::$4=(@2$3..@0$3)*(@0$1..@2$1)

but maybe this will point you somewhere useful...

-- 
: Eric S Fraga (0xFFFCF67D), Emacs 25.1.1, Org release_9.0.5-391-g36c7cf

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 194 bytes --]

Table formula references

[Manuel Schneckenreither]

Hi,

I'd like to use following formula on like this:

  | t | y_t | y_{t+1} |
  |---+-----+---------|
  | 1 | 115 |         |
  | 2 | 121 |         |
  | 3 | 118 |         |
  | 4 | 127 |         |

  $4 = \sum_{\tau=0}^{@#-2}{0.9^{\tau} * y_{@#-2-\tau}}
  

So that's a simple sum, which just iterates over the values and uses the
index for power and to get the value of y_{4-\tau}. So @5$3 should be
0.9^0*127 + 0.9^1*118 + 0.9^2*121 + 0.9^3*115. Shouldn't be too hard I
though, but I couldn't figure it out how to implement it using
org-spreadsheets.

As I need the index I tried using subscr with no success. I can
obviously generate the list of rows used for the calculation using
@2$1..@2$#. However, as soon as I use an elsip function in the formula I
struggle with the representation of the list:

#+TBLFM: $3='(mapcar '1+ (@2$1..@2$#))

shows following debug output:

   Substitution history of formula
   Orig:   '(mapcar '1+ (@2$1..@2$3))
   $xyz->  '(mapcar '1+ (@2$1..@2$3))
   @r$c->  '(mapcar '1+ (#("1" 0 1 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table)) #("115" 0 3 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table))))
   $1->    '(mapcar '1+ (#("1" 0 1 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table)) #("115" 0 3 (fontified t line-prefix #("******* " 0 7 (face org-indent)) wrap-prefix #("******* " 0 8 (face org-indent)) face org-table))))
   Result: #ERROR
   Format: NONE
   Final:  #ERROR

Is there a simple way to do it? Do I need to go over elsip functions,
and if so. How can I properly use a list?

I couldn't find a way on the web.


Best regards

Manuel