Issue 21584 - Formulas from excel change when imported to Calc 1.1.0
Summary: Formulas from excel change when imported to Calc 1.1.0
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: PC Windows 2000
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-10-22 20:54 UTC by Unknown
Modified: 2003-10-23 16:44 UTC (History)
1 user (show)

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

Attachments
2 tab spreadsheet, with formulas that fail when imported to Calc (77.00 KB, application/octet-stream)
2003-10-23 14:23 UTC, Unknown 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description Unknown 2003-10-22 20:54:46 UTC 
Hi folks,
   I have another Calc question. In an Excel workbook, with 2 sheets, I have 
some data on one sheet that I refer to from the second. The data is stored in 
the sheet 'Product Pricing', and is in cell C2. I have the following formula 
entered on the second sheet:

   IF(VALUE(F2)>0,'Project Pricing'!$C$2,"")

   When I import this workbook to Calc, the formula changes to the following:

   IF(VALUE(F2)>0;$'Project Pricing'.$C$2;"")

   Of course, the formula is now in an incorrect format, and fails with a 502 
error.
   Any thoughts?
thanx,
ron
Comment 1 utomo99 2003-10-23 08:39:41 UTC 
Please Attach the documents which make this problem, so we can test
it/faster to confirm.  
(Without the documents, we cannot confirm the problem easily/need more
time)
Don't forget to cut other part of the documents, so the file size is
small, but we still able to see the problem. 

Change the version to 1.1 as summary
Comment 2 Unknown 2003-10-23 14:23:42 UTC 
Created attachment 10578 [details]
2 tab spreadsheet, with formulas that fail when imported to Calc
Comment 3 john.marmion 2003-10-23 15:40:13 UTC 
Calc uses the $ before the Sheet Name and uses '.' to specify the
row/column position of the sheet while Excel uses the '!'. 

The reason why the Formula fails is because the cell F2 is blank and
thus you have an invalid argument. Excel displays your formula result
as a '#VALUE' error. Your example document works the same in both Calc
and Excel. I will close this as invalid.
Comment 4 john.marmion 2003-10-23 15:41:26 UTC 
closed
Comment 5 Unknown 2003-10-23 15:55:31 UTC 
Hi,
   The code should produce a null value when F2 is null or when F2 <= 
0. In Excel, when F2 is null, the cell with the formula is also null. 
In Calc, when F2 is null, the 502 error appears in the cell with the 
formula.
thanx,
ron
Comment 6 frank 2003-10-23 16:23:14 UTC 
Hi,

this is not a bug.

The imported formula is always correct. The parameter delimiters are
different in Excel and Calc.

Your problem is the VALUE function which acts different in both
spreadsheets. An empty cell in Calc is empty and therefore you can't
calculate a value from it. In Excel you can. But "" is not zero. So
Calc works according to the rules.

If you change your formulas like this one :

=IF(NOT(OR(F2="";F2<=0));$'Project Pricing'.$C$2;"")

Remember the delimiters !

your sheet should work in both applications.

Frank
Comment 7 frank 2003-10-23 16:26:37 UTC 
I close it again. Only diiference to John is that I would close it as
wontfix for mostly the same reasons he stated.

Frank
Comment 8 john.marmion 2003-10-23 16:44:32 UTC 
Thanks Frank. I was possibly a bit quick off the mark there Ron. Its
interesting to see that if you type [IF(VALUE(""))] into Excel it
returns a '#VALUE' which is effectively the same as [IF(VALUE(F2))].
Calc is consistent in its approach.