Issue 3724

Summary: Xcl Exp: Formulas with parentheses
Product: Calc Reporter: jblough <jblough>
Component: codeAssignee: oc
Status: CLOSED FIXED QA Contact: issues <issues.openoffice.org>
Severity: trivial    
Priority: P3 CC: apremchandran, issues, mmeeks, utomo.prawiro, vdvo
Version: 605Keywords: oooqa
Target Milestone: ---   
Hardware: PC   
OS: Windows 2000   
Issue Type: DEFECT Latest Confirmation on: ---
Developer Difficulty: ---
Attachments:
Description Flags
Another example without parens
none
yet another testdoc
none
Multi Page Excel file with complex IF formulas
none
archive containing test documents for formula export none

Description jblough 2002-03-29 18:03:11 UTC
Here is how the issue can be demonstrated:

Create a spreadsheet
Type "test" in cell A1 of Sheet1
Go to Sheet2 and type "=(Sheet1.A1)" in the cell
Save in Excel 97/2000/XP format
Open the spreadsheet in Excel 97
The value that shows for cell At on Sheet 2 is "#VALUE!" when it should be "test"
Comment 1 oc 2002-04-08 12:00:47 UTC
Hi Daniel,
one 4 you
Comment 2 daniel.rentz 2002-04-09 08:11:33 UTC
2do: Correct token class for operands in parentheses
Comment 3 daniel.rentz 2002-07-29 09:28:56 UTC
*** Issue 6547 has been marked as a duplicate of this issue. ***
Comment 4 daniel.rentz 2002-10-10 12:46:53 UTC
*** Issue 8184 has been marked as a duplicate of this issue. ***
Comment 5 saphena 2003-01-09 00:54:49 UTC
The parentheses is irrelevant.

ooo101 uses sheet1.a1, saving as Excel does not alter it to sheet1!a1
Comment 6 daniel.rentz 2003-01-09 07:31:36 UTC
What do you mean with "irrelevant"?
I have a document with 2 references: =Sheet1.A1 and =(Sheet1.A2), and
export this file to Excel. The former works in Excel, the latter not.
After reimport to Calc, both work correctly.
Comment 7 saphena 2003-01-09 16:36:22 UTC
Profuse apologies - I posted that at the end of a very long night - I
was, of course, talking utter rubbish and I shall endeavour not to do
anything quite so stupid again.
Comment 8 daniel.rentz 2003-01-10 07:23:30 UTC
Bob, ok ok, no problem ;-) 
Comment 9 daniel.rentz 2003-01-13 16:08:04 UTC
target->OOo2.0
Comment 10 cdunham 2003-10-29 22:33:51 UTC
Created attachment 10769 [details]
Another example without parens
Comment 11 cdunham 2003-10-29 22:35:51 UTC
Not sure if this is related, but I posted an example that has: 
 
=B8+INDIRECT(ADDRESS(2;2)) 
 
When saved as an Excel file, and opened in Excel, this cell reads 
#VALUE! until the cell is edited (even without changing anything). 
 
Without the 'B8+', it works fine. 
Comment 12 daniel.rentz 2003-10-30 07:40:49 UTC
Thanks, I will take this into account.
Comment 13 daniel.rentz 2004-04-19 07:08:32 UTC
*** Issue 27933 has been marked as a duplicate of this issue. ***
Comment 14 oc 2004-05-28 11:04:03 UTC
OC: Due to high workload this issue is retargeted to ooo.later
Comment 15 frank 2004-06-15 11:06:18 UTC
*** Issue 30217 has been marked as a duplicate of this issue. ***
Comment 16 utomo99 2004-06-16 03:35:15 UTC
Add me and Michael meeks in CC. 

I hope Michael can help as Daniel load cannot handle this for OOo 2.0. 
but if we look at the duplicate issue it considered as common issue, and 
better to solve sooner. 
also look at issue  created (which is duplicate of this issue)
http://www.openoffice.org/issues/show_bug.cgi?id=30217
Thanks
Comment 17 frank 2004-08-06 13:26:58 UTC
*** Issue 31257 has been marked as a duplicate of this issue. ***
Comment 18 daniel.rentz 2004-08-06 13:31:30 UTC
Created attachment 16987 [details]
yet another testdoc
Comment 19 dpoirier 2004-10-14 21:33:05 UTC
Created attachment 18411 [details]
Multi Page Excel file with complex IF formulas
Comment 20 dpoirier 2004-10-14 21:36:47 UTC
I have read through all of the tickets opened on this item and would like to
take a step further.  I am experiencing the same issue of receiving #Value,
however, the fixes I have read in these threads do not appear to be working.  I
created an attachment for you to review (# 18411).  Note this file was created
in Open Office and saved as an excel file.  It has never been saved as a .sxc
file.  The parentheses in these forumlas are required for the forumla logic and
can not be removed.  
Comment 21 daniel.rentz 2004-10-15 08:57:21 UTC
Thanks for the test doc. I am currently working on this issue and I think to be
ready next week, so this will work in OOo 2.0
Comment 22 daniel.rentz 2004-10-22 12:23:14 UTC
FIXED in CWS SRC680/dr27 (OOo 2.0)

The case  =(Sheet1.A1)  works now in all variations (cell formula, array formula, 
conditional format, data validation, defined names.

INDIRECT() in addition (test document "exportindirect.xls") works now, if loaded 
and re-exported in a fixed Calc (test doc provided by cdunham)

Matrix formulas with multiplication and references (bugdoc 
"Issue_31257_array_function_test.sxc") works.

I will attach more test documents to this issue, containing more general formulas 
(usage of all possible operands, operators, functions, operator precedence, ...).

@dpoirier:  The attached bugdoc "Formula Problem Doc.xls" is not valid. Just 
delete the spaces in all cells left to the #VALUE! cells, and the formulas will work 
in Calc and Excel.

Comment 23 daniel.rentz 2004-10-22 12:24:31 UTC
set target to OOo 2.0
Comment 24 daniel.rentz 2004-11-04 15:43:28 UTC
Created attachment 18938 [details]
archive containing test documents for formula export
Comment 25 daniel.rentz 2004-11-04 15:59:42 UTC
I have attached a ZIP archive containing a few test documents.

formula_import_export_biff8:
Contains several sheets with basic formula features, starting from simple values, 
over simple operations (addition, subtraction, ...), cell references, complex 
reference operations (range, intersection, list), function calls with variable number of 
arguments, and usage of parentheses at all possible places. Note that Calc lacks 
support for explicit error codes (i.e. =#VALUE!), constant arrays (i.e. ={1,2,3}), and 
range/list operations (i.e. =name1:name2) in formulas, cells using these features 
may show errors after import/export. Also, support for natural language references 
(2nd sheet) is very restricted. Both export filters (BIFF5, BIFF8) can be tested.

formula_special_export.sxc
Contains features that are partly not available in Excel or Calc's import filter. See 
first sheet in this document for more details. Both export filters (BIFF5, BIFF8) can 
be tested.

functions_export.sxc
Contains a list with all functions known in Calc AND Excel. Calc allows to enter 
functions with wrong number of arguments, Excel does not. Export filter has to filter 
these functions, it replaces them by a #NA error cell. Both export filters (BIFF5, 
BIFF8) can be tested. Note that some functions are not supported in BIFF5 (i.e. 
HYPERLINK) and will be replaced by #NA too.

functions_import_biff?.xls
These 5 documents are supposed to test function import from BIFF2, BIFF3, 
BIFF4, BIFF5/7, and BIFF8. They can be used to test a round trip (import, reexport)
.

name_import_export_biff?.xls
These 2 documents use defined names in various contexts. They contain user-
defined names, build-in names (i.e. print range, filter range), sheet-local and global 
names; and use them directly in cells and in functions (i.e. it is possible to sum up 
the print range of a sheet).
Comment 26 daniel.rentz 2004-11-05 09:38:53 UTC
reopened
Comment 27 daniel.rentz 2004-11-05 09:39:07 UTC
back to QA
Comment 28 daniel.rentz 2004-11-05 09:41:34 UTC
fixed
Comment 29 oc 2004-11-08 13:52:42 UTC
verified in internal build cws_dr27
Comment 30 oc 2004-11-23 14:14:33 UTC
closed because fix available in OOo1.9m62
Comment 31 frank 2005-01-19 14:50:25 UTC
*** Issue 40835 has been marked as a duplicate of this issue. ***