According to: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#refTable3 =ERROR.TYPE(#NULL!) should be "1". * Calc results in #N/A, which is an incorrect result for this function. According to: https://help.libreoffice.org/7.5/en-US/text/scalc/01/func_error_type.html Error value "Err:511" Returns "1", but this is incorrect documentation. * It should be "#NULL!" Returns "1".
#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.
https://support.microsoft.com/en-us/office/error-type-function-10958677-7c8d-44f7-ae77-b9a9ee6eefaa
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.