A formula such as: =(A2!A3) ...results in an error (es expected), because there is no reference intersection. Actual results: In that example, Calc currently shows the #REF! error. Expected results: According to the ODF standard, this error should be #NULL!, "Intersection of ranges produced zero cells. ERROR.TYPE of 1". See "Table 4" in ODF standard: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#refTable3 (yes, that link really points to "Table 4"). Background info: The reference intersection operator in Calc is the exclamation mark "!". https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#Infix_Operator_Reference_Intersection A formula such as: =SUM(A2:D3!B1:C4) ...provides the sum of the numeric values of B2:C3. A formula such as: =(B1:B3!A2:C2) ...provides the value of B2 (in this case, the parentheses are optional). A formula such as: =(A2!A2:A3) ...provides the value of A2. A formula such as: =(A2!A2) ...provides the value of A2. The #NULL! error is also seen in Excel for the same "no intersection" cases (where the space character is the intersection operator, e.g. "=(A2 A3)" is #NULL! in Excel). The #REF! error should be corrected to the #NULL! error for this case ("Intersection of ranges produced zero cells"), in order to be compliant with the ODF standard, and for interoperability. (CC'ing Eike Rathke)
(In reply to ady from comment #0) > See "Table 4" in ODF standard: > https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/ > OpenDocument-v1.3-os-part4-formula.html#refTable3 > (yes, that link really points to "Table 4"). Because those refTable fragments are 0-based. > https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#Infix_Operator_Reference_Intersection That actually says "If there are no cells in common, returns an Error" without specifying the error value (which is common in ODFF because implementations may vary). We can switch to #NULL! though for interoperability. Note that the table's comment "Intersection of ranges produced zero cells." is only an example (and apparently inspired by Excel), for us internally #NULL! is FormulaError::NoCode that can be set on error conditions producing no code.. (of which an empty intersection could be regarded as such).
(In reply to Eike Rathke from comment #1) > Because those refTable fragments are 0-based. I indeed noticed that; I mentioned it JIC someone thinks it was a typo. Maybe for version ODF 1.4, someone might evaluate the pros and cons of making it 1-based(?). > Note that the table's comment "Intersection of ranges produced zero cells." > is only an example (and apparently inspired by Excel) I failed in finding any other example, so perhaps it is also a definition(?).
Comments by definition are not definitions.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/110dedfa00336de45fcb7462940f41378a9809e1 Resolves: tdf#154915 Let empty intersection error be #NULL! instead of #REF! It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/df8ae4fddb77f5621f651f05e72f8c2bd4ae111b Related: tdf#154915 #NULL! is not an "Internal syntactical error" It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I tested the "no intersection" case. I don't know of other cases of #NULL!. If there are, I'll be happy to test them too. Solved as of LO 7.6.alpha built on 2023-04-21. Thank you Eike.
The #NULL! error currently could be presented interpreting a formula produces no result at all, not even an error, or when preparing the call of an Add-In function that for whatever reason was not called, or if for a structured table reference the corresponding token could not be generated. All internal processing that can't be forced with entering a simple formula and usually does not and should not happen.