Description: When AVERAGE function has more than 30 arguments,the result returned after entering the function in the cell is #N/A. Note that you have to save the change and then reopen the spreadsheet and you will see #N/A in the cell that has more than 30 arguments for the AVERAGE function. This behavior was seen with the MIN function as well. There maybe others that have the same behavior. Microsoft excel has no issue using more than 30 arguments for these spreadsheet functions. Steps to Reproduce: 1. Create a row or column of 30 numbers. 2. In an unused cell, use the AVERAGE() to compute the average of step 1 above 3. Note that the value is correct for the 30 numbers used as arguments. 4. Repeat steps 1-3 but use another row or column of 31 numbers 5. Save and close the spreadsheet 6. Reopen the spreadsheet and note the result of step, it now displays #N/A Actual Results: #N/A is returned instead of the correct average of the 31 numbers Expected Results: The correct number representing the average of 31 numbers Reproducible: Always User Profile Reset: Yes Additional Info: .
Hallo AVERAGE accepts Range-addresses aka: »A2:A20000« which counts as ONE Argument but includes 19999 Numbers, that should solve your issue!
One could argue that this is not a bug as there is a work around as one can use a nesting of functions i.e. MAX(arg1, arg2, MAX(arg3,arg4)) You mentioned that something like A2:A20000 would work but these cells are contiguous. In my application, the MAX (or AVERAGE) function was using a list of spreadsheet cells that are not adjacent to each other. I have to add specific cells to the list by using a mouse click + command key (on a Macintosh) on the cells of interest. The other point that is concerning about this is that it is misleading to the user. They enter a formula into a spreadsheet cell that calculates a value, but when the spreadsheet is closed and reopened, The value that is previously calculated is replaced with a value of #N/A in the cell that had the original function. If there is really an issue with the formula, it should be indicated to the user once they hit the return key to accept the formula. Rob
Created attachment 189831 [details] sample ODS I could not reproduce in a current master build, see attachment. Am I missing something? Which file format do you use? Can you reproduce with my sample file? Please also paste the full version information copied from LibreOffice > About. Thank you! Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 19e9fe7c8c89399753ac1730e1c76378b18418bc CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
Created attachment 189832 [details] Saved file in xls of the original ods file Thanks for sending the attachment. After working with your sample file, I think I found the problem. The behavior is proper when working in ods format as confirmed by opening and modifying the file saving in ods format. When the original ods file is saved in xls format, the issue appears. The sample file does exhibit the behavior when saved in xls format. When I first discovered this issue, I was opening and saving work using the xls format. Before submitting this issue, I did update Libre Office to the most current version for the Mac: Version: 7.6.1.2 (X86_64) / LibreOffice Community Build ID: f5defcebd022c5bc36bbb79be232cb6926d8f674 CPU threads: 8; OS: Mac OS X 11.7.2; UI render: Skia/Metal; VCL: osx Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded I hope this helps. Let me know if more information is needed.
Right, thank you for clarifying. This is indeed a limitation with the XLS format, and is not an issue with ODS or XLSX. I am marking as a duplicate of bug 44817 then. (and I recommend moving away from the old binary formats, and using XLSX or ODS instead) Thank you! *** This bug has been marked as a duplicate of bug 44817 ***