[bug] spreadsheet does not replace some fields

[bug] spreadsheet does not replace some fields

[Michael Brand]

Hi all

Isn't there a nasty spreadsheet bug? I'm quite confused that it has
not been discovered yet so I start to doubt on my reliability.

- bug:
  | this field is not replaced | 1 |
  | this field is not replaced | 2 |
  |----------------------------+---|
  | 3                          | 3 |
  #+TBLFM: $1 = $2

  - last minutes' release_7.8.11-85-g62453c5
  - at least back to release_7.8.09-555-g323836f
  - emacs -q, 23.3.1

- expected:
  | 1 | 1 |
  | 2 | 2 |
  |---+---|
  | 3 | 3 |
  #+TBLFM: $1 = $2

- this variation works as expected:
  | 1 | 1 |
  | 2 | 2 |
  | 3 | 3 |
  #+TBLFM: $1 = $2

Michael

Re: [bug] spreadsheet does not replace some fields

[Juan Pechiar]

On Wed, Jun 20, 2012 at 02:26:42PM +0200, Michael Brand wrote:
> Isn't there a nasty spreadsheet bug? I'm quite confused that it has
> not been discovered yet so I start to doubt on my reliability.

Hi, Michael.

This is a feature, not a bug.

From the manual (section 3.5.6 Column Formulas):

  If the table contains horizontal separator hlines, everything before
  the first such line is considered part of the table header and will
  not be modified by column formulas.

So, you need to add a horizontal separator at the top of your table.
Otherwise the first 2 lines are considered as headers

Regards,
.j.

> - bug:
>   | this field is not replaced | 1 |
>   | this field is not replaced | 2 |
>   |----------------------------+---|
>   | 3                          | 3 |
>   #+TBLFM: $1 = $2
>
>   - last minutes' release_7.8.11-85-g62453c5
>   - at least back to release_7.8.09-555-g323836f
>   - emacs -q, 23.3.1
>
> - expected:
>   | 1 | 1 |
>   | 2 | 2 |
>   |---+---|
>   | 3 | 3 |
>   #+TBLFM: $1 = $2
>
> - this variation works as expected:
>   | 1 | 1 |
>   | 2 | 2 |
>   | 3 | 3 |
>   #+TBLFM: $1 = $2
>
> Michael

Re: [bug] spreadsheet does not replace some fields

[Michael Brand]

Hi Juan

On Wed, Jun 20, 2012 at 3:47 PM, Juan Pechiar <juan@pechiar.com> wrote:
> From the manual (section 3.5.6 Column Formulas):
>
>  If the table contains horizontal separator hlines, everything before
>  the first such line is considered part of the table header and will
>  not be modified by column formulas.

Yes, of course, I should know! I read this some years ago but didn't
realize then what effect it will have for me one day: All rows in my
current example except the last one are just considered as the table
header and everything becomes clear. How could I not conclude myself.
Thank you for helping out.

With this in mind it should be

| item  | absolute | percent |
|-------+----------+---------|
| a     |       17 |      34 |
| b     |       21 |      42 |
| c     |       12 |      24 |
|-------+----------+---------|
| total |       50 |     100 |
#+TBLFM: @>$2=vsum(@<<$2..@>>$2)::$3=100 * $2 / @>$2

or

|-------+----------+---------|
| item  | absolute | percent |
|-------+----------+---------|
| a     |       17 |      34 |
| b     |       21 |      42 |
| c     |       12 |      24 |
|-------+----------+---------|
| total |       50 |     100 |
|-------+----------+---------|
#+TBLFM: @>$2=vsum(@<<$2..@>>$2)::$3=100 * $2 / @>$2

and it is not possible to take the shortcut of

| a     | 17 |     |
| b     | 21 |     |
| c     | 12 |     |
|-------+----+-----|
| total | 50 | 100 |
#+TBLFM: @>$2=vsum(@<$2..@>>$2)::$3=100 * $2 / @>$2

or

|-------+----+-----|
| a     | 17 |     |
| b     | 21 |     |
| c     | 12 |     |
|-------+----+-----|
| total | 50 | 100 |
|-------+----+-----|
#+TBLFM: @>$2=vsum(@<$2..@>>$2)::$3=100 * $2 / @>$2

to group rows with hline

Michael

Re: [bug] spreadsheet does not replace some fields

[Michael Brand]

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

Hi all

On Wed, Jun 20, 2012 at 10:53 PM, Michael Brand
<michael.ch.brand@gmail.com> wrote:
> On Wed, Jun 20, 2012 at 3:47 PM, Juan Pechiar <juan@pechiar.com> wrote:
>> From the manual (section 3.5.6 Column Formulas):
>>
>>  If the table contains horizontal separator hlines, everything before
>>  the first such line is considered part of the table header and will
>>  not be modified by column formulas.
>
> Yes, of course, I should know! I read this some years ago but didn't
> realize then what effect it will have for me one day: All rows in my
> current example except the last one are just considered as the table
> header and everything becomes clear. How could I not conclude myself.
> Thank you for helping out.
>
> With this in mind it should be
>
> | item  | absolute | percent |
> |-------+----------+---------|
> | a     |       17 |      34 |
> | b     |       21 |      42 |
> | c     |       12 |      24 |
> |-------+----------+---------|
> | total |       50 |     100 |
> #+TBLFM: @>$2=vsum(@<<$2..@>>$2)::$3=100 * $2 / @>$2
>
> or
>
> |-------+----------+---------|
> | item  | absolute | percent |
> |-------+----------+---------|
> | a     |       17 |      34 |
> | b     |       21 |      42 |
> | c     |       12 |      24 |
> |-------+----------+---------|
> | total |       50 |     100 |
> |-------+----------+---------|
> #+TBLFM: @>$2=vsum(@<<$2..@>>$2)::$3=100 * $2 / @>$2
>
> and it is not possible to take the shortcut of
>
> | a     | 17 |     |
> | b     | 21 |     |
> | c     | 12 |     |
> |-------+----+-----|
> | total | 50 | 100 |
> #+TBLFM: @>$2=vsum(@<$2..@>>$2)::$3=100 * $2 / @>$2
>
> or
>
> |-------+----+-----|
> | a     | 17 |     |
> | b     | 21 |     |
> | c     | 12 |     |
> |-------+----+-----|
> | total | 50 | 100 |
> |-------+----+-----|
> #+TBLFM: @>$2=vsum(@<$2..@>>$2)::$3=100 * $2 / @>$2
>
> to group rows with hline

To make this clearer in the manual I suggest the attached small patch.

Michael

[-- Attachment #2: 0001-org.texi-Mention-when-a-table-header-is-mandatory.patch.txt --]
[-- Type: text/plain, Size: 1802 bytes --]

From 1f9cc08ecad6e7f78f44084f0d001203f04104a7 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Fri, 24 Aug 2012 19:46:49 +0200
Subject: [PATCH] org.texi: Mention when a table header is mandatory

* org.texi (Column formulas): Add a sentence to be more explicit about
when a table header is mandatory.
---
 doc/org.texi |   11 +++++++----
 1 files changed, 7 insertions(+), 4 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index c33b5a9..cf0656c 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2770,10 +2770,13 @@ Named field, see @ref{Advanced features}.
 When you assign a formula to a simple column reference like @code{$3=}, the
 same formula will be used in all fields of that column, with the following
 very convenient exceptions: (i) If the table contains horizontal separator
-hlines, everything before the first such line is considered part of the table
-@emph{header} and will not be modified by column formulas.  (ii) Fields that
-already get a value from a field/range formula will be left alone by column
-formulas.  These conditions make column formulas very easy to use.
+hlines with rows above and below, everything before the first such hline is
+considered part of the table @emph{header} and will not be modified by column
+formulas.  Therefore a header is mandatory when you use column formulas and
+want to add hlines to group rows, like for example to separate a total row at
+the bottom from the summand rows above.  (ii) Fields that already get a value
+from a field/range formula will be left alone by column formulas.  These
+conditions make column formulas very easy to use.
 
 To assign a formula to a column, type it directly into any field in the
 column, preceded by an equal sign, like @samp{=$1+$2}.  When you press
-- 
1.7.4.2

Re: [bug] spreadsheet does not replace some fields

[Bastien]

Hi Michael,

Michael Brand <michael.ch.brand@gmail.com> writes:

> To make this clearer in the manual I suggest the attached small patch.

Applied, thanks.

-- 
 Bastien