[Libreoffice-bugs] [Bug 44132] FILESAVE XLSX XLS =ISEVEN() formula does not work when saving in .xls and .xlsx formats

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=44132

Eike Rathke <erack at redhat.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |WONTFIX

--- Comment #18 from Eike Rathke <erack at redhat.com> ---
This is nothing about the IF() function. It is ISEVEN() (and others, like
ISODD, GCD, LCM, ...) that in older Excel versions exist only as Add-In
functions, hence are stored differently in .xls BIFF and apparently when Excel
writes that in an (older?) localized version it confuses the hell out of that
and writes the localized name (in attachment 54802 as ES.PAR in LBL - Internal
Defined Name, or in attachment 164716 as PÁROSE in EXTERNNAME - Externally
Defined Name record, seems different Excel versions even do it differently)
which of course Calc doesn't know. Nothing we can do about other than adding a
pile of translations for the old Add-In functions just to satisfy a legacy
document format.

Conclusion: do not use .xls BIFF to store roundtrip documents.

Additionally, older Excel versions don't do an automatic conversion from
boolean to number (or vice versa), so an expression like
ISEVEN(E2)=1
is *always* FALSE because ISEVEN() returns boolean and boolean=number is never
equal. Modern Excel versions handle that. Don't ask me for numbers when that
started. For older versions you'll need to write
ISEVEN(E2)=TRUE()
or
N(ISEVEN(E2))=1
but in this case a simple
ISEVEN(E2)
would had been sufficient anyway.

The only case left then is
(2)LibreofficeTestLinux.xls - saved in LO as .xls - #MACRO? error
but that doesn't happen anymore (tried in LO7 and LO5.3), loading the .ods and
saving as .xls and reloading calculates fine, even with the Add-In function
ISEVEN_ADD() (only the automatic BOOLEAN format on A2 and A3 is lost if no
number format was assigned).

ISEVEN_ADD() also isn't stored to .xlsx OOXML, stored is ISEVEN() instead, so
we are good there.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200826/f7f6011f/attachment-0001.htm>