Created attachment 174182 [details] Sample spreadsheet with named expressions The attached document contains some named expressions. The cells that refer to those expressions (Billing.A1:A2, Tickets.D1:D2) show Err:502 now; but when the named expressions were entered initially, the formulas worked. The document-scope named expressions are: > INF: (2^53-1)*2^(1024-53) > LAST_ROW: MATCH(INF;$Tickets.$A:$A) > DURATIONS: $Tickets.$A$2:INDEX($Tickets.$A:$A;LAST_ROW) > IDS: $Tickets.$B$2:INDEX($Tickets.$B:$B;LAST_ROW) Even as simple formula as =SUM(DURATIONS) produces an error.
It looks related to INF. Replacing its reference in LAST_ROW by its expression, like this: > LAST_ROW: MATCH((2^53-1)*2^(1024-53);$Tickets.$A:$A) fixes the problem. It turned out that I made a mistake while preparing the example, moving IFERROR's last argument out of the argument list - that's unrelated; the correct formula in Billing.A1 / Tickets.D1 is > =SUMPRODUCT(IFERROR(CEILING.MATH(DURATIONS;300);0)) and in Billing.A2 / Tickets.D2 is > =SUMPRODUCT(IFERROR(CEILING(SUMIFS(DURATIONS;IDS;IDS);300)/COUNTIFS(IDS;IDS);0)) Why could INF cause this? There's no built-in function named so.
Seems to be some nested named expressions problem. Even the simplest case like FOO 23 BAR FOO+1 doesn't work (anymore?) and the definition of BAR lowercases foo+1 and in a cell =BAR produces #NAME?
It does work if the definition of BAR is then once edited (append space, backspace) in the Manage Names dialog.
It also works if the Manage Names dialog was closed after FOO 23 was defined, which points out the referenced name isn't found while adding BAR (logically, not in the documet yet). It does not explain why something doesn't work when loading documents.
Apart from that the formulas in D1 and D2 are wrong lacking the second parameter of IFERROR(), probably should be =SUMPRODUCT(IFERROR(CEILING.MATH(DURATIONS;300);0)) and =SUMPRODUCT(IFERROR(CEILING(SUMIFS(DURATIONS;IDS;IDS);300)/COUNTIFS(IDS;IDS);0)) which doesn't change the error though.
(In reply to Eike Rathke from comment #5) > Apart from that the formulas in D1 and D2 are wrong lacking the second > parameter of IFERROR() Right - see comment 1 ;) Thanks for looking into this, Eike!
Old problem, I reproduced loading with 5.3.7 already. Probably the nesting *and* order encountered.
HAHAHA no.. "INF" *is* a (hidden) symbol.. it's the XMLSchema-2 infinity representation recognized by rtl::math::stringToDouble() that returns HUGE_VAL (0x7fffffffffffffff) and sets rtl_math_ConversionStatus_OutOfRange for which we set FormulaError::IllegalArgument at the named expression's token array, so it persists and can't be caught by IFERROR(). If name and usage are changed (in two steps) from INF to, for example, INFIN, things work..
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b37fd7f38165dadc5b1a674b73f4b18824e4789e Resolves: tdf#143809 "INF" may be a named expression or DB area name It will be available in 7.3.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.
Pending review https://gerrit.libreoffice.org/c/core/+/120352 for 7-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/3dd3c1ed99a014cb98a7a68668b9426020094ec2 Resolves: tdf#143809 "INF" may be a named expression or DB area name It will be available in 7.2.1. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/18771471f75685a6d3838a22d16dcff5c398e652 tdf#143809: sc_subsequent_filters: Add unittest It will be available in 7.3.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 "libreoffice-7-1": https://git.libreoffice.org/core/commit/c5dbe94eb71acd9ab19cd9a8d561dd75da82d3d7 Resolves: tdf#143809 "INF" may be a named expression or DB area name It will be available in 7.1.6. 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.