Created attachment 55620 [details] Calc error with sum() in xls format Using SUM() function in a spreadsheet to sum up more than 30 items/cells will return an error (Value not available). The error occurs ONLY after the spreadsheet is saved and reopened. This error condition appears to be OK if it is saved in ods format. A sample error is attached for clarification. Cell E31 is the demonstration of the error. Just duplicate C31 and add one more cell in the sum(). The result will be good at this point. Save the file in the current xls format and then reopen it. #NA will replace the previous result. Thank you for your support.
I reproduce the described behavior. But what does say xls format ? Is more than 30 items allowed in the sum() function in xls format? Please, could you try with MS-Excel or MS-Excel-viewer? Best regards. JBF
Might be a FOLEOPEN error, my MS EXCEL Viewer (12.0.6.6334.5000) MSO (12.0.6514.500) shows SUM result "496" for 1 additional item without problem. GNUMERIC also shows an error. To me this behavior to replace a (maybe wrong) formula by "=NA()", so that the user has no chance to examine the problem, is some strange. I believe that can be improved. @Kohei: 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.
This "replace formula" issue looks similar to "Bug 44831 - FILEOPEN Excel.xls shows #NAME instead of calculation result or formula"
Is there any progress in correcting the problem reported?
Created attachment 65050 [details] This is what I see. This is what I see what I open the attached xls document. I also opened the same doc in Excel XP, and I see the same values. So I assume this is working?
I forgot to say I tested it using 3.5. Master exhibits the same, so I call it fixed.
The problem reported is on the summation of more than 30 individual items. The sum becomes an error after the spread sheet is saved. Excel never has this problem. Only Libreoffice/Openoffice finds this simple summation as an error. Let me know if my definition is not clear. Thank you for helping.
Then how come I see the same summation value in Excel?
Anyway. I just don't see any problem here. Try 3.5 or later and see if that works for you.
The example in the attachment is to show the error. cell e31 is the error. Cell c31 is the formula sum function of 30 elements (result value = 465). If one extra item is added to the sum function, say a31, the resulting value is 496. Then close and save the spread sheet, reopen it and the value #N/A is being shown instead of value 496. This is an error that needed to be addressed. It may the saving algorithm that is overflow or the sum function in dealing with 30 or more individual items.
[Reproducible] with Server Installation of "LibreOffice 3.6.0.4 rc German UI/Locale [Build-ID: 932b512] on German WIN7 Home Premium (64bit). Attached Sample document I created from an .ods by saving it as .xls. That can be opened with EXCEL VIEWER and will show values in C30 and C31. Opening .xls with LibO will show an error message in C31 Already in OOo 3.1.1 (And OOo 1.1.5), so inherited from OOo Still [Reproducible] with parallel installation of Master "LOdev " 3.7.0.0.alpha0+ - WIN7 Home Premium (64bit) ENGLISH UI [Build ID: 66e4540]" (tinderbox:Win-x86@6, pull time 2012-07-26 02:09:47) @Kohei: can you please have a look?
Created attachment 65058 [details] New Sample Document
Created attachment 65092 [details] Excel only supports up to 30 args in SUM. I tried to put 31 arguments to SUM *in* Excel 2007, and got this error message. Looks like it's Excel that has hard time accepting the 31st argument in SUM, which undoubtedly is the cause of this problem.
This is not our bug. We need to have this addressed with the Excel team.
@Kohei Yoshida: Your secreenshot seems to show something completely different? Here we discuss the problem that EXCEL (or may be only viewer) is more tolerant concerning SUM formulas with too many arguments. May be that EXCEL is limited to 30 arguments there, and even may be that the EXCEL format rules limit creation of that function to 30 arguments. But: EXCEL (Viewer) SHOWS without any problems, and so it is definitively a problem that we can't do so, because LibO is too intolerant. But yardstick should be EXCEL, not the Viewer. It EXCEL shows the same problem and if also destroys that formula when it saves the document that would decrease importance of a fix? My tests showed that EXCEL 2010 will not show SUM with 31 arguments, and during my quick test I did not find a way to save a document with such a formula. Excel opens my .odt with some problems and replaces the formula by the result value. Sue my result is similar to Kohei's, currently I see no necessity for a bug fix, here seems no need to do the job better than Excel itself.
(In reply to comment #15) > @Kohei Yoshida: > Your secreenshot seems to show something completely different? Here we discuss > the problem that EXCEL (or may be only viewer) is more tolerant concerning SUM > formulas with too many arguments. No, Excel is LESS tolerant, as it doesn't take more than 30 arguments, whereas Calc does! > May be that EXCEL is limited to 30 arguments there, and even may be that the > EXCEL format rules limit creation of that function to 30 arguments. It's not maybe, it's the truth. > But: EXCEL (Viewer) SHOWS without any problems, and so it is definitively a > problem that we can't do so, because LibO is too intolerant. But yardstick > should be EXCEL, not the Viewer. Try re-calculate in Excel. Excel only shows the cached result that we put in. Sorry, I'm completely lost. I don't understand what you guys want to see fixed. I'll remove myself from further discussion. Perhaps ask someone else for a second opinion. Thanks.
I changed my mind after some more investigation, we need a fix here. LibO accepts SUM sith more than 30 summands for FILESAVE as .xls (see "Bug 53149 - FILESAVE as .xls with SUM() with more than 30 comma separated summands does not modify this function"!), but then keeps user schoolmasterly in leading-strings without any necessity and persists to show contents in documents that have been created by LibO! That's not user friendly.
Never confirmed and clear disagreement - looking for second opinion from QA, moving to UNCONFIRMED.
If I understand right, the problem occur when saving a spreadsheet in xls file format in Calc. As showed by the Kohei's screencopies MS-Excel does not allow to create a SUM formula with more than 30 arguments. When saving in foreign formats the user is notified that it is possible that not all features of Calc are supported by the foreign format. I think that users who use LibreOffice to save documents in MS-Office should be aware of what is allowed by the file format they choose. I suggest to close this bug report as NotABug. Best regards. JBF
TESTING with 4.4.0.0.alpha2 + Ubuntu 14.05 (In reply to Jean-Baptiste Faure from comment #19) > If I understand right, the problem occur when saving a spreadsheet in xls > file format in Calc. As showed by the Kohei's screencopies MS-Excel does not > allow to create a SUM formula with more than 30 arguments. (if anyone wants to test a sum of 1..31 (or more) entries, generate it w/this: for i in `seq 1 31`; do echo -n A$i, ; done ) > When saving in > foreign formats the user is notified that it is possible that not all > features of Calc are supported by the foreign format. True, but we only give them the same generic "This document *may* contain formatting or content.." message we give every time we save in a non-ODF format. This is a great example of a specific case in which we *know* that the file format can't handle what we're trying to shove into it. IMHO, ideally the dialog should read This document contains formatting that *cannot* be saved...Microsoft Excel.. with a bullet point below the paragraph that states something like: * Cell B31: More arguments have been specified for this function than are allowed...(etc.) I'd also suggest we add a (default to unchecked) box that must be checked before the user can click the save button. It should be labeled thusly: [ ] I understand that this is a dangerous operation that loses data! > I think that users who use LibreOffice to save documents in MS-Office should > be aware of what is allowed by the file format they choose. I agree that they should be aware, but unless *we* spell it out for them, they will almost certainly never figure it out on their own. > I suggest to close this bug report as NotABug. > I agree that we can't engineer a fix for Excel, but I think this bug is valid as an enhancement request for a better warning. Summary -> (updated) Status -> NEW
There are going to be so many corner cases of what will be saved and what won't be. I believe this was discussed by ux at some point and summarily dismissed because it would be so hard to figure out every test case of when Excel formats are going to lose things. Also it might lead to bad results if we warn about some things, miss something else, and a user then says "but you lead me to believe you'll warn me about EVERY corner case that might occur." Just my 2 cents - I'd close this as WONTFIX as the reality is it would just be too hard to implement and the current warning is sufficient to say "you very well might lose stuff if you don't save in odf formats"
(In reply to Joel Madero from comment #21) > There are going to be so many corner cases of what will be saved and what > won't be. I only know a couple of them, although I'd expect that the devs/the codebase knows about many more of the limitations, because otherwise we'd be writing out invalid XLS files that Excel wouldn't be able to read correctly. > I believe this was discussed by ux at some point and summarily > dismissed because it would be so hard to figure out every test case of when > Excel formats are going to lose things. I'm not suggesting that we try to completely map the entire XLS spec. I'm just suggesting that in cases that we *know* are going to lose data, we say something explicit about it. I think that's the best way for us to provide a high-quality software product to our users. > Also it might lead to bad results if > we warn about some things, miss something else, and a user then says "but > you lead me to believe you'll warn me about EVERY corner case that might > occur." I agree that our users sometimes say silly things, and often blame us for things over which we have no control, but I think it's okay for us to sometimes say "This operation MAY lose data," and other times to say "This operation WILL lose data." > Just my 2 cents - I'd close this as WONTFIX as the reality is it would just > be too hard to implement and the current warning is sufficient to say "you > very well might lose stuff if you don't save in odf formats" As I mentioned previously, our current "you might lose stuff" warning is displayed *every single time* someone saves a document in a non-ODF file format. We provide the same bland warning so often that I'm certain our users are desensitized to it. If they haven't lost data the last 100 times they've saved an XLS file, they're going to mistakenly believe it's unlikely that they'll lose data on the 101st save. I see this as an opportunity for us to differentiate ourselves from our competitors: We have a chance to tell the user what works with the file formats and what doesn't. As a bonus, users will probably be much more encouraged to stick with ODF.
*** Bug 93956 has been marked as a duplicate of this bug. ***
Same in OOo 3.3, so marking as inherited.
*** Bug 157336 has been marked as a duplicate of this bug. ***