Mailing-List: contact ooo-issues-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: ooo-issues@incubator.apache.org
From: bugzilla@apache.org
To: ooo-issues@incubator.apache.org
Subject: [Bug 119565] [From Symphony] The SUM formula result changed
 incorrectly if save the .xls doc to .ods one.
Date: Sat, 25 Aug 2012 14:00:29 +0000
Message-ID: <bug-119565-248469-OQ0yfOpM6Y@https.issues.apache.org/ooo/>
In-Reply-To: <bug-119565-248469@https.issues.apache.org/ooo/>
References: <bug-119565-248469@https.issues.apache.org/ooo/>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit
Auto-Submitted: auto-generated
MIME-Version: 1.0

https://issues.apache.org/ooo/show_bug.cgi?id=119565

ooo@erack.de changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |ooo@erack.de

--- Comment #9 from ooo@erack.de ---
(In reply to comment #8)

There seems to be some confusion about the union (or range sequence)
operator and named expressions I'd like to clarify.

Note: I'll use the ';' semicolon parameter separator for clarity, in
      Excel that may be different depending on locale, e.g. ',' comma
      instead.

* in ODF the union operator is defined as '~' tilde
* OOo, AOOi and LibO use '~' in the UI as well
* in Excel, a union is entered as (range1;range2) including the
  parentheses, without parentheses it is not a union
* the Excel form is also accepted by AOOi,... for usability, only that
  it is transformed to (range1~range2)
* old OOo accepted (range1;range2) only (AFAIR) for INDEX() function and
  treated it specially there, the plain range1;range2 in named expressions
  passed to functions may have worked by accident
* the SUM() function is a bit unlucky example because SUM(range1;range2) and
  SUM((range1;range2)) and SUM(range1~range2) all yield identical results due
  to the SUM() function accepting one or more range parameters anyway, which
  may have led to the assumption that in Excel the union operator would be
  a simple ';' semicolon
* the difference can be seen for example with INDEX((range1;range2);1;1;2)
  where if the parentheses are omitted the function returns an error because
  it would get 5 parameters passed instead of 4
* named expressions (AKA defined names) are exactly that, they must define an
  expression that can be compiled
  * range1;range2 is NOT an expression
    * in fact you can't enter that as a named expression, you'd get an error
      and you also get an error in the imported document if you open the names
      dialog, select that definition and click the Modify button
  * range1~range2 or (range1;range2) would be valid expressions
* in the imported Excel document the faulty named expression works
  nevertheless because it is imported as already compiled, as soon as the
  expression would need to be recompiled it would fail, which is the reason
  when saved/reloaded as .ods it fails

As a solution the Excel import needs to handle that and in named
expressions generate a proper union from what looks like a list of
ranges instead of passing these as is.

-- 
You are receiving this mail because:
You are the assignee for the bug.