To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=67777


User atdsm changed the following:

                What    |Old value                 |New value
================================================================================
                      CC|''                        |'atdsm'
--------------------------------------------------------------------------------
          Ever confirmed|                          |1
--------------------------------------------------------------------------------
                  Status|UNCONFIRMED               |NEW
--------------------------------------------------------------------------------
              Issue type|DEFECT                    |ENHANCEMENT
--------------------------------------------------------------------------------
                Keywords|                          |ms_interoperability, oooqa
--------------------------------------------------------------------------------
              OS/Version|Windows XP                |All
--------------------------------------------------------------------------------
                Priority|P2                        |P3
--------------------------------------------------------------------------------
                 Summary|Calc returns #VALUE, Excel|CHOOSE function cannot ret
                        | calculates formula just f|urn a cell reference
                        |ine                       |
--------------------------------------------------------------------------------




------- Additional comments from [EMAIL PROTECTED] Wed Jul 26 05:36:09 -0700 
2006 -------
This issue appears to be a misunderstanding of the CHOOSE function as used in
Calc. In MS Excel, the CHOOSE function can either return values or cell
references. That is, Excel will choose based on context whether to return the
contents of cell A6 or the referece "A6" (refering to cell A6).

In Calc, the CHOOSE function returns only values.

Let's use the example from your spreadsheet:

SUM($O6:CHOOSE(($H$2-1),$O6,$P6,$Q6,$R6)) where cells O6-R6 all contain the
value "1."

Excel will return a cell reference from the CHOOSE function based on what the
SUM function needs, resulting in (if $H$2-1 evaluates to 2, for example)
SUM($O6:$P6) after CHOOSE has been evaluated. This is a logical function, and
therefore works.

Calc will return the value of the cell called, however. So (if $H$2-1 evaluates
to 2, for example) the same function will result in SUM($O6:1) after CHOOSE has
been evaluated. This isn't a logical function and results in the error.

I established this difference in behavior by reading the Help files in Excel and
Calc on the CHOOSE function and by doing tests to verify the problem. CHOOSE
function works as described in Help for both programs, but Calc's functionality
is a subset only of Excel's functionality in this case.

-----------------------------------

* Changing type to Enhancement request. Requested enhancement: CHOOSE function
should choose based on context whether a cell value or reference is to be
returned, as this will grant more Excel compatibility.
* Lowering priority to P3.
* Adding ms_interoperability keyword.
* Changed summary from "Calc returns #VALUE, Excel calculates formula just fine"
to "CHOOSE function cannot return a cell reference"
* Changed OS to All (I think this applies regardless of OS)

Thanks,

Steve

---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to