Created attachment 53187 [details]
Mortgage Calculator - some cells show err:520, instead of values
My Ubuntu 11.04, 11.10 and Windows Computers XP and Windows 7, all give this Err:520 in some cells with formulas. Happens with all versions of Libra Office.
ALL versios of Libra Office give an Err:520 in cells that should be functioning okay. This spreadsheet works fine with Open Office versions 3.0 to 3.3. Also works fine with Ms Office excel versions. This spreadsheet also functions with Open Office on Ubuntu 10.10 and earlier.
It doesn't seem to matter which operating system I am using. This spreadsheet does not work with Libra Office.
[Reproducible] with reporter's sample document and "LibreOffice 3.4.4RC2 - WIN7 Home Premium (64bit) German UI [OOO340m1 (Build:402)]",
Also reporducible with parallel installation of MinGW Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) English UI [(Build ID: 2ba5d12-e8c71c5-41e7bcd-4b83b90)] (daily/MinGW_cross-compilation2011-10-25_00.12.09)"
No Err:520 problem with "LibreOffice Portable 3.3.0 - WIN7 Home Premium (64bit) German UI [OOO330m19 (Build:6 Tag 22.214.171.124)]" with "LibreOffice Portable 3.3.0 - WIN7 Home Premium (64bit) German UI [OOO330m19 (Build:6 Tag 126.96.36.199)], but some others in D20:D25 and may be elsewhere."
MS EXCEL Viewer, OOo 3.1.1, OOo 3.3 and Gnumeric without Err:520 problem, OOo 3.3 also shows wrong (no) values in D20:D25
To find out the real reason of the problem is beyond my skills and patience, the document contains lots of lots of lots of formulas referring to lots of other formulas referring ...., and if 1 formula has as result "Err:520", that will "infect" all other results referring to there.
Saving sample document as .ods, closing and reopening it changes the Err:520 problem to a #NAME#? problem; what ever that might mean
Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
problem seems to be the import of range name: nper
I'll try to have a look at that.
Ok, quite easy problem:
Our xls import has the same problem our ods range name import had. When we import a range name which refers to a range name that is not yet imported we get an error.
I think we can reuse the ods solution for this problem.
Solution will get really nasty. It is not that easy as I expected. The problem is already in the binary import and not the formula compilation.
I have not yet found a good solution to this problem.
Analyzed the problem a bit with Kohei and we have maybe a solution for the problem that is neither ugly nor needs a rewrite of calc's formula compiler.
Main target will be 3.5 and depending on the needed changes I'll think about backporting to the 3-4 branch
oh and just for the record: not only nper is wrong nearly all range names in range name formulas are skipped
I have half of the fix but will not have time to work on it for the next two weeks.
The patch has still a problem with the import of matrix formulas in range names.
I talked to Markus on IRC. I'll take a look at this.
Fixed on libreoffice-3-5 and master.
I missed the boat for Beta3 just by a few hours. So the fix will be reflected in the one after Beta 3 (RC1?).
*** Bug 51669 has been marked as a duplicate of this bug. ***