Open attachment 166372 [details] from bug 137488 in Calc, save as XLSX, and try opening the result in Excel. => Excel fails to open a file. The reason is the erroneous formula in C1: =COUNTIF(BASE(1;14);"vv") This gives the following result in Calc: Err:504 - Error in parameter list In Calc such invalid formula is both accepted when entered and can be saved, but when trying to enter the same in Excel, it doesn't let the user, and also fails opening such files. Two relevant pieces of code: - enum class FormulaError with the error codes https://opengrok.libreoffice.org/xref/core/include/formula/errorcodes.hxx - XclTools::GetXclErrorCode(...) that converts Calc error codes to Excel ones https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xltools.cxx In some cases the formula is fine to write in Excel, eg. division by zero, but not in others.
It looks good, Excel accepts using: =COUNTIF(INDIRECT("value");"vv"), resulting in a #!REF¡ but doesn't accept =COUNTIF("hello";"VV") =COUNTIF(1;"VV") =COUNTIF(TEXT(1;"#");"VV") Asking Gemini, could it be that Excel does not check the value, but rather the type of value or the return type of the function used?
Aron Budea committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bebe7a0b270506b4d1a32bd99740865fbfbea3a3 tdf#170515 sc: formula with invalid parameters saved into XLSX It will be available in 26.8.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.
(In reply to m_a_riosv from comment #1) > Asking Gemini, could it be that Excel does not check the value, but rather > the type of value or the return type of the function used? I'd think so, a #REF! will be counted as an error value of cell/range reference type that can be propagated further. Thanks for the observations!