Created attachment 157731 [details] Image shows error in formula exported from Google Docs to XLSX format I made a rather simple formula in Google Sheets and sent the sheet as email attachment, then I opened the resulting .XLSX document in Libreoffice Calc. In Calc, the formula "IFS" is lowercase "ifs", which causes the formula to fail. Other formulae in the same cell are uppercase. The spreadsheet is simple. In the first cell (A1) and the second one (B1) I add either text or number (like A and 32) In the third column (C1), I add the following: =ifs(isblank(A1);"A1 is blank";istext(B1);"A1 is not blank and B1 contains text";isnumber(B1);"A1 is not blank and B1 is a number")
Created attachment 157732 [details] Google Sheets "Send as attachment" has lowercase "ifs()" formula
On pc Debian x86-64 with master sources updated today, I could reproduce this. However, when unzipping the xlsx, it contains the function in lowercase in xl/worksheets/sheet1.xml ifs(isblank(A1),"A1 is blank",istext(B1),"A1 is not blank and B1... Specs of XLSX show functions in uppercase (see https://docs.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/3d025add-118d-4413-9856-ab65712ec1b0) Perhaps on Windows it'll be ok because OS is case unsensitive but on Linux it will fail. IMHO, I think Google Docs is wrong here and would put "NOTOURBUG" but I may be wrong since I'm not an expert so I'll let other speak.
Opening the file show 'ifs', after editing the formula 'IFS', then after save and reopening 'IFS' But saving without editing keep the 'ifs' in the formula. Version: 6.4.0.3 (x64) Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8 CPU threads: 4; OS: Windows 10.0 Build 19559; UI render: default; VCL: win; Locale: es-ES (es_ES); UI-Language: en-US Calc: threaded
I get the same error opening the file with Excel. "ifs" in lowercase and #NAME! error after recalc.
Probably NOTOUTBUG, but I think we need devs evaluation. Maybe we have a way to make the formula UPPERCASE even if we detect it is lowercase?
Interestingly, IFS is implemented in LO 5.2 in tdf#97831 for interoperability with Excel 2016; yet, Excel 2016 does not have this function, and MS documentation only mentions Excel 2019 and 365 as having it [1]. Could it be that this function support was implemented incorrectly in LO? I only have Excel 2016; someone with Excel 2019 is needed to test and provide a reference spreadsheet with this function made in Excel. [1] https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45?ui=en-US&rs=en-US&ad=US
Created attachment 157761 [details] A sample with IFS created in Excel Online Here is a sample created in Excel Online. It seems to match what Calc does, and it looks like Google Sheets indeed is wrong here... Which is confirmed by uploading attachment 157732 [details] to the OneDrive and opening it with Excel Online to see #NAME? there - so MS Excel also thinks it's a wrong formula. Suggest closing NOTOURBUG.
Closed as NOTOURBUG per Mike's comment.