[Libreoffice-ux-advise] new listbox in calc options pages

[Kohei Yoshida]

Hi Astron,

On 12/07/2012 06:41 PM, Stefan Knorr wrote:
> Hi Markus,
>
>> Since 4.0 we are able to use the cached values written into OOXML
>> files to prevent a slow recalculation when opening a file. Hoewever
>> Excel and Calc don't always give the same result for formulas so we
>> (Eike, Kohei and me) agreed that we need to give the user a choice
>> wether to recalculate the formulas or not.
>
> How are users supposed to know when they need it and when they don't?

They aren't.  Heck even we the Calc developers don't know that for sure.

Ideally all formula functions generate identical results.  In the real 
world, however, they generate slightly different results due to many 
factors; different algorithms used internally, binary double precision 
numbers rounding errors, and even some bugs in the formula function 
implementations (either in Calc or in Excel).  And this is always a 
moving target as Calc and Excel both change the internal implementation 
of formula functions between releases.

> I'd assume that if users are supposed to work with the spreadsheet in
> LibO, they need to use the numbers that LibO generates ...

It's actually a little more gray than that.  I would expect the results 
to be pretty close enough between Excel and Calc, so in most casual use 
cases it shouldn't be necessary to recalculate on load every single 
time.  This is why we've decided to cache the results to speed up the 
load time.

But in some advanced use cases where numerical precision and 
tractability of the formula results is absolutely essential, this may 
not be good enough, hence the need to provide a choice to the user.

thus, what
> would sound more sensible to me would be an automatic blacklisting of
> certain formulas whose results you know to be different in LibO than in
> MSO (and then recalculate everything that depends on these, of course).

I'm afraid we can't do that with reasonable effort, especially when the 
algorithm that Excel uses is not very well defined (and is potentially a 
moving target).  Even if we decide to do it, the data may not be 
reliable enough to be usable, especially over a long haul.

Best,

Kohei

-- 
Kohei Yoshida, LibreOffice hacker, Calc