Observed behavior: Occasionally after saving and reloading a Calc spreadsheet certain ranges that contained a single array formula will load as the correct array formula for non-#N/A results; however, #N/A results will load as cells that still show the array formula in the formula bar, show #N/A within the cell, but are no longer associated with the rest of the array formula. They do not respond to a Ctrl+/ from the functional (non-#N/A) cells, and they can be deleted with a simple Delete press individually.
Conditions: I am posting to seek advice on how to determine the exact conditions. I can say this:
- I have noticed it only with UDF functions in the array formula, but I seldom use non-UDF functions in array formulas.
- The spreadsheets are substantial in size (40 sheets, 200 rows by 30 columns).
- The spreadsheets and the UDFs themselves are dependent upon externally linked values.
- I can have several carbon-copy sheets with only a difference of a single cell acting as a parameter to the said UDFs and have one of the sheets exhibit this behavior and the others not exhibit this behavior on the same reload.
I have attached a sample spreadsheet. Please note that I have never replicated the problem in the sample sheet, but I’m starting by posting that since unravelling the “production” sheet would be more than I would ask of an observer up front. The sample sheet should demonstrate what the problem is. If I/we can start zeroing in on the conditions for the behavior, I’m happy to supply a more detailed example…but recall that external links might be involved.
The sample file contains six sheets Sheet1 - Sheet6 with a scenario like the one I have experienced, and a sheet named Bad where I have simulated the odd behavior as closely as possible.
ArrayFormulaCracksAtNA.ods (11.7 KB)
Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 20; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Possibly related Bugzilla:
https://bugs.documentfoundation.org/show_bug.cgi?id=43003
https://bugs.documentfoundation.org/show_bug.cgi?id=93973