calc compliance with openformula

e-letter · April 20, 2012, 1:57pm

Readers,

According to the specification for openformula (see:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html),
the syntax for the function 'sum' is:

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)
However in calc the function:

=a1+b2

still produces a correct result. Isn't the logical behaviour for the
second function to return an error, for non-compliance with the
standard. This is being asked for learning if it is better to use the
official syntax or accept that these "short-cuts" exist.

LO334

Regina · April 20, 2012, 2:31pm

Hi e-letter,

e-letter schrieb:

Readers,

According to the specification for openformula (see:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html),
the syntax for the function 'sum' is:

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)

No. it would be SUM(a1;b2)

However in calc the function:

=a1+b2

+ is an operator no function. For operators see ODF1.2. part2, section 5 and 6.

You have to be aware of the conversion rules in addition.

still produces a correct result. Isn't the logical behaviour for the
second function to return an error, for non-compliance with the
standard. This is being asked for learning if it is better to use the
official syntax or accept that these "short-cuts" exist.

In LibreOffice the behavior of the operator + and the function is not the same in regard of cells with no numerical content.

Your question is to general. Please give a more concrete example, what you try to calculate, what your result is and what you expect the result should be.

Kind regards
Regina

e-letter · April 21, 2012, 8:24am

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)

No. it would be SUM(a1;b2)

It would be better if calc returned an error, stating incorrect syntax

In LibreOffice the behavior of the operator + and the function is not
the same in regard of cells with no numerical content.

Your question is to general. Please give a more concrete example, what
you try to calculate, what your result is and what you expect the result
should be.

The question is general because a general understanding is required!
The example concerns only integers, e.g. where a1=1 and b2=2, the
results are correct, but the question is about learning the correct
syntax so that formula entered in a calc spreadsheet will not be lost
or corrupted when the spreadsheet is opened in another odf compliant
software, e.g. gnumeric. I don't want to use a sum syntax such as
(a1+b2), which gives the correct result, but if opened in another
program, the formula is lost or gives an incorrect answer.

jfn · April 21, 2012, 9:52am

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)

No. it would be SUM(a1;b2)

It would be better if calc returned an error, stating incorrect syntax

I'm afraid it is *not* incorrect syntax. Likewise, SUM(5) is correct syntax as well.

The question is general because a general understanding is required!
The example concerns only integers, e.g. where a1=1 and b2=2, the
results are correct, but the question is about learning the correct
syntax so that formula entered in a calc spreadsheet will not be lost
or corrupted when the spreadsheet is opened in another odf compliant
software, e.g. gnumeric. I don't want to use a sum syntax such as
(a1+b2), which gives the correct result, but if opened in another
program, the formula is lost or gives an incorrect answer.

All spreadsheet software I know of are accepting the syntax above. Of course, the syntax to use is preferably SUM(A1:A10), ie using a cell range rather than discrete cell references, but the latter can be useful (see 3. below).

So the following are all syntactically correct:

1. SUM(A1:A10)
-> returns the sum of the numerical values within the range. Non-numeric values are ignored.

2. SUM(A1:A10;Z1:Z10)
-> like 1, with multiple ranges.

3. SUM(A1;B2;Z15)
-> returns the sum of several cells. Non-numeric values are ignored, opposite to what A1+B2+Z15 would return (see Regina's answer).

...and any combination of the previous.

HTH,

Johnny_Rosenberg · April 21, 2012, 10:13am

Readers,

According to the specification for openformula (see:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html),
the syntax for the function 'sum' is:

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)

”a1+a2” is NOT a Number Sequence List. ”a1;a2” is.
What happens in your example, is that a1 and a2 are first calculated
and then sent to the function SUM, which then doesn't have very much
more to do than just returning its input value. So if a1=5 and a2=7,
then SUM's argument is 12. So SUM calculates the sum of 12 in this
case, which is very unnecessary.

On the other hand, with syntax ”=SUM(a1;a2₎”, a1 and a2 are sent to
SUM, and SUM calculates and returns the sum.

However in calc the function:

=a1+b2

still produces a correct result. Isn't the logical behaviour for the
second function to return an error, for non-compliance with the
standard.

No. ”=a1+a2” has nothing to do with the SUM function at all.

This is being asked for learning if it is better to use the
official syntax or accept that these "short-cuts" exist.

It's not a short-cut, since it has nothing to do with the SUM
function, see above.

Johnny_Rosenberg · April 21, 2012, 10:26am

Readers,

According to the specification for openformula (see:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html),
the syntax for the function 'sum' is:

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)
However in calc the function:

=a1+b2

still produces a correct result. Isn't the logical behaviour for the
second function to return an error, for non-compliance with the
standard.

Go to the link below and read about ”Operators”. Then tell me my
”=a1+b2” is ”non-compliance with the standard”:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017940_715980110

Tom_Davies · April 21, 2012, 11:14am

Hi
If it's not standard ODF behaviour then just post a bug-report about it. Sometimes programs try to be more forgiving but as someone pointed out that might not be helpful in all cases.
Regards from
Tom

Villeroy · April 21, 2012, 2:03pm

Am 20.04.2012 15:57, e-letter wrote:

Readers,

According to the specification for openformula (see:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html),
the syntax for the function 'sum' is:

SUM( { NumberSequenceList N }+ )

Analog to a regular expression, this means: One or more element of type numberSequenceList

planas · April 21, 2012, 3:48pm

SUM( { NumberSequenceList N }+ )

e.g. SUM(a1+b2)

No. it would be SUM(a1;b2)

It would be better if calc returned an error, stating incorrect syntax

I'm afraid it is *not* incorrect syntax. Likewise, SUM(5) is correct syntax as well.

The question is general because a general understanding is required!
The example concerns only integers, e.g. where a1=1 and b2=2, the
results are correct, but the question is about learning the correct
syntax so that formula entered in a calc spreadsheet will not be lost
or corrupted when the spreadsheet is opened in another odf compliant
software, e.g. gnumeric. I don't want to use a sum syntax such as
(a1+b2), which gives the correct result, but if opened in another
program, the formula is lost or gives an incorrect answer.

All spreadsheet software I know of are accepting the syntax above. Of course, the syntax to use is preferably SUM(A1:A10), ie using a cell range rather than discrete cell references, but the latter can be useful (see 3. below).

So the following are all syntactically correct:

1. SUM(A1:A10)
-> returns the sum of the numerical values within the range. Non-numeric values are ignored.

2. SUM(A1:A10;Z1:Z10)
-> like 1, with multiple ranges.

3. SUM(A1;B2;Z15)
-> returns the sum of several cells. Non-numeric values are ignored, opposite to what A1+B2+Z15 would return (see Regina's answer).

...and any combination of the previous.

I think the above syntax was developed for Lotus 123 in the early 80's(?) and everyone else implemented the rules/syntax to be compatible with Lotus. By the mid 80's this syntax was standard for all spreadsheets.

The only real change from Lotus is that (using 1) is in Lotus formulas were enter +SUM(A1:A10) while the IMHO the more logical =SUM(A1:A10) was introduced later by the mid 80's.

jfn · April 21, 2012, 4:19pm

Thanks for the historical backgroud, Jay. Interesting to know.

mariosv · April 21, 2012, 9:56pm

I'm sorry but +SUM(A1:A10) doesn't work in 123, there is always needed @ to introduce functions in 123:
@SUM(A1:A10).

Miguel Ángel

  * Inglés - detectado
  * Inglés
  * Español
  * Gallego
  * Italiano

  * Inglés
  * Español
  * Gallego
  * Italiano

<javascript:void(0);>

e-letter · April 22, 2012, 8:57am

Agreed; the purpose of the post was to ask a simple question and learn
something new about spreadsheet formulae, so thanks to those for their
information.