CSE (shift+control+enter) formulas are not evaluated as such when Calc opens a calc session begins (opens file), though they work fine if input within the calc session. For example, the CSE formula COUNTA(IF(($A$1:$A$5=$A7),B$1:B$5)) will count the rows matching a certain value form the same row When loaded properly the viewed cell should show {=COUNTA(IF(($A$1:$A$5=$A7),B$1:B$5))} and display the rows counted. After the calc file is loaded it instead just shows this for the cell =COUNTA(IF(($A$1:$A$5=$A7),B$1:B$5)) and either an old or incorrect static value, or the error "=VALUE!" In order to correct the formula cell the formula has to be cleared and reentered with shift-control-enter for it to evaluated properly. Reentering the formula with CSE directly doesn't work right. Once the CSE formula is entered it will work correctly for the entire session, though calc will again not evaluate it properly when the file is opened later. I've tried saving in both .xls and .xlsx format, and neither works right. It doesn't seem to matter if Calc or Excel created the file. Excel will load the file properly even when Calc doesn't.
Please attach test file (.xls or .xlsx format). Thank you.
Hello, Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team This NEEDINFO message was generated on: 2015-10-14
Created attachment 124488 [details] Random example
So I created a random array in Excel 2003 (see the "Random example" attached). Opened it with Calc 5.1.2.1. It worked just fine (array functions were recognized as such). If I missed something important or it still doesn't work for you, feel free to reopen this bug. Until then it works for me.