Description: LibreOffice Calc won't save AVERAGE when it contains 31 cells or data. When I calculate an average which contains 31 cells or data, I save the file in a .xls format and reopen the file, the average is not in cell where I put it, instead I see the expression #N/A and in the input line =NA(). Please, note that each data with which it was calculated is also another average of another data. I attach the corresponding file with the data set. You can see the sell with the average that is failing at Q26 I am on Linux Mint 18.2, Sonya (Ubuntu 16.04, Xenial), 64 bits, KDE 5.8.8, and I use LibreOffice 5.1.6.2 Build ID: 1:5.1.6~rc2-0ubuntu1~xenial2 File: https://yadi.sk/i/IIFJ3azQ3QUEdF Steps to Reproduce: 1.Open the file 2.In any cell, in its Input line, put the following: =AVERAGE(P25,P49,P73,P97,P121,P145,P169,P193,P217,P241,P265,P289,P313,P337,P361,P385,P409,P433,P457,P481,P505,P529,P505,P553,P577,P601,P625,P649,P673,P697,P721). Each of these cells are, at the same time, averages of data 3.Save 4.Open the file again 5.You will see at the cell #N/A and in the input line =NA() Actual Results: LibreOffice Calc won't save averages with 31 or more data that is, at the same time, another average of another data, in .xls files Expected Results: LibreOffice Calc save averages with 31 or more data that is, at the same time, another average of another data, in .xls files Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes Terminal output by opening the file, saving the changes and exiting: $ LC_ALL=C libreoffice javaldx: Could not find a Java Runtime Environment! Warning: failed to read path from javaldx (soffice:17210): Gtk-WARNING **: Unable to locate theme engine in module_path: "adwaita", (soffice:17210): Gtk-WARNING **: Unable to locate theme engine in module_path: "adwaita", (soffice:17210): Gdk-WARNING **: gdk_window_set_icon_list: icons too large (soffice:17210): Gdk-WARNING **: gdk_window_set_icon_list: icons too large W: Unknown node under /registry/extlang: deprecated W: Unknown node under /registry/grandfathered: comments W: Unknown node under /registry/grandfathered: comments ** (soffice:17210): WARNING **: Invalidate all children called ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 36 ** (soffice:17210): WARNING **: Unknown event notification 34 ** (soffice:17210): WARNING **: Unknown event notification 36 Terminal output by reopening the file: $ LC_ALL=C libreoffice javaldx: Could not find a Java Runtime Environment! Warning: failed to read path from javaldx (soffice:17381): Gtk-WARNING **: Unable to locate theme engine in module_path: "adwaita", (soffice:17381): Gtk-WARNING **: Unable to locate theme engine in module_path: "adwaita", (soffice:17381): Gdk-WARNING **: gdk_window_set_icon_list: icons too large (soffice:17381): Gdk-WARNING **: gdk_window_set_icon_list: icons too large W: Unknown node under /registry/extlang: deprecated W: Unknown node under /registry/grandfathered: comments W: Unknown node under /registry/grandfathered: comments ** (soffice:17381): WARNING **: Invalidate all children called User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:57.0) Gecko/20100101 Firefox/57.0
Created attachment 138341 [details] printscreen from excel This is not a bug, but limitation of xls format
Thank you so much. Perhaps, LibreOffice could notify or warn when someone tries to exceed such limitation. Although, I think this should be reported as a new feature.
(In reply to Gatoso Oso from comment #2) > Thank you so much. Perhaps, LibreOffice could notify or warn when someone > tries to exceed such limitation. Although, I think this should be reported > as a new feature. Answer from developer: IMHO it is not possible to give an explicit warning to the user on export to OOXML that a function call may not work properly in Excel. (A general warning is already being given to the user when choosing for OOXML file format.) The problem is that to count the number of arguments, the function must be interpreted as if calculating the function. That does not happen on saving (and can be quite time consuming). The function arguments can be given as separate values and/or references to (ranges of) discrete values and/or results of other functions. E.g. with a named range AVERAGE(a) can have one to many arguments, depending on the range of a. Also, not all Excel versions are the same, some some functions will be handled correctly in Excel2016, but not in Excel2010. When saving to OOXML format, we don't know what (version of) application will be used to open it, so the warning may be more annoying than helpful.
«IMHO it is not possible to give an explicit warning to the user on export to OOXML that a function call may not work properly in Excel» I think the point is not Excel exactly, the point is that when someone exceed the limitation and save the document with a .xls format, the calculation of the function is not saved, all of this is made in LibreOffice Calc, not in Excel. I think the actual warning is fine: «This document may contain formatting or content that cannot be saved in the currently selected file format “Microsoft Word 2007-2013 XML”. Use the default ODF file format to be sure that the document is saved correctly» this describes what is the problem with precision, but it is useless in a practical way. I was making a homework of university and due to that the warn above mentioned was not related exactly with the problem of saving functions in .xls format, I did not note the issue until I finished the homework, closed the program, and reopened the file to verify all was right. Then, when I noticed the limitation I had to make the whole work again but, this time, splitting the average —by summing certain amount of values, getting some sums (less than 31) and by dividing the whole sum by the amount of variables— for avoiding the limitation. This took time and I was against the time. I know I could use the .odt format, the problem is that my professors, at the university, do not use LibreOffice but MS Office, and thus, they expect to receive the homeworks in a .xls file. I am not a developer, obviously, but I can give my vision as a user of LibreOffice Calc that uses the program to process huge amounts of data, not to calculate a fews variables. Thanks a lot