| Summary: | ifna() returns #VALUE! if the input is a sum of a N/A value and another text cell | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Michael Tsang <miklcct> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | medium | ||
| Version: | 4.0.0.3 release | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:24.2.0 target:7.6.3 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | Spreadsheet showing the issue in Libreoffice 7.5.6.2 | ||
In your Example-Sheet the Content of C2 is Text but not numeric! So you have to change your Formula to one of: ``` =IFNA(C2 & NA(),"ok") or =IFERROR(C2 + NA(),"ok") But that's not what this is about.. the results of
=IFNA("ok"+NA(),"ok")
and
=IFNA(C2+NA(),"ok")
with C2 containing a text "ok" are expected to be the same.
Note however that relying on the order of propagation of errors is fragile. In the context of operator +() attempting to obtain a numeric value from C2 generates a #VALUE! error for text content, operating that with another error operand (here NA()) may depend on at which point the errors are generated or used propagated and which takes precedence, where standalone (breaking execution early) or within an error evaluating function like IFNA() may also differ. Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/3161a6c351a2f5f70c0420ee8cccf2eb23de1ecf Resolves: tdf#157797 Let GetCellValue() propagate previous error, if any It will be available in 24.2.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/+/158040 for 7-6 Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/1ed6f4ba6fcae232705d005de6fcbdcc8769585f Resolves: tdf#157797 Let GetCellValue() propagate previous error, if any It will be available in 7.6.3. 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. |
Created attachment 190240 [details] Spreadsheet showing the issue in Libreoffice 7.5.6.2 ifna(value, alternative value) throws #VALUE! if the value is a #N/A produced by adding a text cell (not a string literal) to a #N/A value. The following two produces "ok": =IFNA(NA(),"ok") =IFNA("ok"+NA(),"ok") However, the following produces #VALUE! despite the input value is #N/A, where C2 is a cell containing the text "ok": =IFNA(C2+NA(),"ok")