Issue 67777 - CHOOSE function cannot return a cell reference
Summary: CHOOSE function cannot return a cell reference
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 2.0.3
Hardware: All All
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: ms_interoperability, oooqa
Depends on:
Reported: 2006-07-26 07:42 UTC by kpalagin
Modified: 2013-08-07 15:15 UTC (History)
5 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

testcase 1 (160.00 KB, application/
2006-07-26 07:43 UTC, kpalagin
no flags Details
testcase 1 (160.00 KB, application/
2006-07-26 07:43 UTC, kpalagin
no flags Details
testcase 2 (34.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2006-07-26 07:43 UTC, kpalagin
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description kpalagin 2006-07-26 07:42:03 UTC
In attached err508.xls I am getting #VALUE in cell J6. Formula appears to 
Excel calculates this formula just fine.
The formula is "=IF($H$2=1;0;(SUM($O6:CHOOSE(($H$2-1);$O6;$P6;$Q6;$R6))))+G6"

If I save file in .ods then I am getting different error alltogether - #NAME?.
Comment 1 kpalagin 2006-07-26 07:43:01 UTC
Created attachment 38029 [details]
testcase 1
Comment 2 kpalagin 2006-07-26 07:43:08 UTC
Created attachment 38030 [details]
testcase 1
Comment 3 kpalagin 2006-07-26 07:43:42 UTC
Created attachment 38031 [details]
testcase 2
Comment 4 atdsm 2006-07-26 13:36:09 UTC
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)


Comment 5 frank 2006-07-26 14:52:24 UTC
as enhancement to requirements for decission finding
Comment 6 kpalagin 2006-07-26 15:46:47 UTC
Dear Steve,
thanks a lot for your analisis!

Dear developers,
please enhance CHOOSE function to return cell reference too.
I am migrating 40+ users to OOo and this function is used in number of cases. 
What makes it even more critical is it is upper management who uses it.
Thanks a lot.
Comment 7 drking 2008-02-12 07:30:17 UTC
There may be some confusion here? In Calc CHOOSE *will* return a reference:
works fine

I think the difficulty may be that Calc does not expect a function after the 
colon in a range:
gives a #NAME? error, even though INDIRECT always returns a reference. Calc 
seems to be trying to recognise indirect as a named cell.
Comment 8 ooo 2008-02-12 12:16:34 UTC
This seems to be yet something different. With the changes in CWS odff related
to issue 4904 (implementation of range operator) loading the .ods file works
fine now, but loading the .xls files still results in #VALUE! error as if the
import filter doesn't transform that into the correct token sequence.

Furthermore, re-editing the formula in J6 will calculate the value in the cell
but that will not update cell values in K6 and L6, which get updated only if
those formulas are re-edited as well. Seems as if broadcasters/listeners are not
setup correctly. However, that may be an effect related to the original problem
and should be re-investigated once that is solved.
Comment 9 ooo 2008-02-12 12:19:18 UTC
Defect of import filter.
Comment 10 daniel.rentz 2008-02-15 13:12:14 UTC
excel import fixed
Comment 11 ooo 2008-02-19 18:45:13 UTC
Reassigning to QA for verification.
Comment 12 kpalagin 2008-02-19 19:29:39 UTC
THANK YOU very much!!
Comment 13 oc 2008-02-27 06:58:21 UTC
verified in internal build cws_odff
Comment 14 thackert 2008-08-06 18:57:31 UTC
Hello kpalagin, *,
I have tested the first attached file with DEV300m28 under Debian SID AMD64 and
it works (i.e. the calculation in J6 takes place ... ;) ) So I close this issue.
@kpalagin: Could you check this with m28 under Windows as well? Does it work for
you too? If not, you can reopen this issue immediately ... ;)