<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><span class="vcard"><a class="email" href="mailto:erack@redhat.com" title="Eike Rathke <erack@redhat.com>"> <span class="fn">Eike Rathke</span></a>
</span> changed
<a class="bz_bug_link
bz_status_RESOLVED bz_closed"
title="RESOLVED WONTFIX - FILESAVE XLSX XLS =ISEVEN() formula does not work when saving in .xls and .xlsx formats"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=44132">bug 44132</a>
<br>
<table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>What</th>
<th>Removed</th>
<th>Added</th>
</tr>
<tr>
<td style="text-align:right;">Status</td>
<td>NEW
</td>
<td>RESOLVED
</td>
</tr>
<tr>
<td style="text-align:right;">Resolution</td>
<td>---
</td>
<td>WONTFIX
</td>
</tr></table>
<p>
<div>
<b><a class="bz_bug_link
bz_status_RESOLVED bz_closed"
title="RESOLVED WONTFIX - FILESAVE XLSX XLS =ISEVEN() formula does not work when saving in .xls and .xlsx formats"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=44132#c18">Comment # 18</a>
on <a class="bz_bug_link
bz_status_RESOLVED bz_closed"
title="RESOLVED WONTFIX - FILESAVE XLSX XLS =ISEVEN() formula does not work when saving in .xls and .xlsx formats"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=44132">bug 44132</a>
from <span class="vcard"><a class="email" href="mailto:erack@redhat.com" title="Eike Rathke <erack@redhat.com>"> <span class="fn">Eike Rathke</span></a>
</span></b>
<pre>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 <span class=""><a href="http://bugs.documentfoundation.org/attachment.cgi?id=54802" name="attach_54802" title="saved in MS2010 as .xls - #ADDIN? error">attachment 54802</a> <a href="http://bugs.documentfoundation.org/attachment.cgi?id=54802&action=edit" title="saved in MS2010 as .xls - #ADDIN? error">[details]</a></span> as ES.PAR in LBL - Internal
Defined Name, or in <span class=""><a href="http://bugs.documentfoundation.org/attachment.cgi?id=164716" name="attach_164716" title="The example file saved with Excel as XLS">attachment 164716</a> <a href="http://bugs.documentfoundation.org/attachment.cgi?id=164716&action=edit" title="The example file saved with Excel as XLS">[details]</a></span> 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.</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>