| Summary: | Calc's ERROR.TYPE() function incorrect result vs ODF | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | ady <adylo811517> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | medium | ||
| Version: | 5.0.0.5 release | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:7.6.0 | ||
| Crash report or crash signature: | Regression By: | ||
|
Description
ady
2023-03-13 17:01:06 UTC
#NULL! is an alias of NoCode Err:521 (there are only the 7 #error names defined that Excel had but Calc uses more detailed errors). But yes, current implementation asks for the different ParameterExpected Err:511 and that is also documented. Wonder if that was on purpose.. but it also doesn't match Excel import. Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8dc4bea8323783b4abbf8010c492bf8d8de76481 Resolves: tdf#154167 ERROR.TYPE(#NULL!) must return 1 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/help/commit/c3a560d632327fea245b60b7a40e7950974f3cd7 Related: tdf#154167 ERROR.TYPE(#NULL) is for Err:521 Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/5e8ed765cc8c502f1272c191c0a1a4f82d505e57 Related: tdf#154167 ERROR.TYPE() list both alias name and Err:... value Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6e6020d01cfc373c6117164d112f59fdbfe2463d tdf#154167: sc_spreadsheet_functions_test: Add unittest 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. Using Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: dd3c70518851b532f43a853d8e0c6189013844c4 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL threaded Built 2023-03-17 There still a problem left over. =ERROR.TYPE(err:521) > results in 5 as any other Err:nnn does. At most, maybe ERROR.TYPE(Err:525) "could" be 5, but not every Err:nnn. I am not sure which of the following alternatives is the correct one, but ERROR.TYPE(Err:nnn) (or err:nnn, or any upper/lower case combination) should either: * be automatically converted to its equivalent error name when it is used as argument for ERROR.TYPE(); or * should all result in #N/A, as if they were not an error of any kind; or * should result in some specific error such as Err:501 or Err:525, not being recognized as a valid argument, or similar behavior. IDK what Excel does for unnamed errors (link in comment 2). Also, ERROR.TYPE(Err:) results in Err:511 (Missing variable). Which variable would be missing? The error number? Some cell address (as if it were a cell range because of the colon ":")? This seems wrong either way. I didn't specify these details in comment 0, but they are directly relevant to the main subject of this bug report. I am reopening; please advice whether an additional ticket should be opened instead. You are confusing things. With =ERROR.TYPE(err:521) you are not getting the error number result of the Err:521 equivalent, but already Err:521 is an erroneous expression (note also that if you typed exactly Err:521 it will be lower-cased err:521). =Err:521 results in #NAME? error and for that ERROR.TYPE() is applied, which correctly yields 5. Same for any Err:... you tried. ERROR.TYPE(Err:) results in Err:511 (Missing variable) because already the Err: leaves compilation in an error state. Try to input =Err: and in the upcoming error correction dialog hit Escape to force that expression, you'll get Err:520 (Internal syntactical error) as result. No bugs here. (In reply to Eike Rathke from comment #8) > You are confusing things. With =ERROR.TYPE(err:521) you are not getting the > error number result of the Err:521 equivalent, but already Err:521 is an > erroneous expression (note also that if you typed exactly Err:521 it will be > lower-cased err:521). > =Err:521 results in #NAME? error and for that ERROR.TYPE() is applied, > which correctly yields 5. > Same for any Err:... you tried. Probably I am not expressing myself correctly. The point that doesn't sound entirely logical for me is that a recognized argument (#NAME!) is giving the same result as invalid arguments (Err:nnn), which defeats the whole point of the function, at least for "#NAME!" errors. I think that the way the function is described in the ODF standard leaves room for some misinterpretation. * For non-errors, the function should result in "#N/A". * For the seven recognized arguments, the result should be 1 to 7, respectively, instead of propagating them as other common functions do. * For _other_ errors... Here is the part that is not entirely clear. For errors that are not part of the 7 recognized arguments, the result cannot be 5 (as if the argument would had been "#NAME!"). When the argument is an error but not within the 7 recognized values, the result should be actually an error. Either the function carries these "other" errors that are not part of those seven, or it results in "#NAME!". From ODF, someone might think it should result in #N/A; I disagree. At any rate, the function resulting in "5" would mean that "any other error is equivalent to #NAME! as argument", and that seems to break the essence / goal of the ERROR.TYPE() function for when the argument is actually #NAME!. Moreover, resulting in "5" for _other_ errors doesn't seem to be the way the ODF standard describes the function, nor how other spreadsheet tools interpret it. In any case, you are correct: I am confused. Probably I am not expressing myself correctly. In ERROR.TYPE(Err:nnn) Err:nnn is not an invalid argument to the ERROR.TYPE() function. When Err:nnn is compiled it results in a token with a #NAME! error, a #NAME! error passed to ERROR.TYPE() results in 5. |