LibreOffice/Calc/Excel Compatibility and Problems

LibreOffice Calc cannot properly work with Microsoft Excel's xlsm files.

---

LibreOffice Calc and Microsoft Excel often compute slightly different results.

expect (often invisible) deviations between results from Calc and Excel as they:
- apply different rounding to input values,

  '=1234567890123456 - 1234567890000000' stays that in Calc and is calculated to '123456', while Excel changes the input to 
  '=1234567890123450 - 1234567890000000' and calculates '123450' as result.

- apply different rounding / 'snap-to-zero' to results,

  '=2000000000000000+5,1' in Excel: '2000000000000000', in Calc: '2000000000000005'

- calculate sheets / ranges / operands in different orders:
while excel works 'by row' first top row from left to right and than that downwards row by row.
calc (mostly?) operates 'by column'.

As the results of some operations sometimes depend on the order of operands, differences show up.
(there are differences between 'floating point math' and 'school math'! fp-math with limited precision is not! associative)
E.g. the sum of this stack will be correct in Calc (0,52) but wrong in Excel (0,51953125):

             0,52
-8796093022207,00
 8796093022207,00

while the results are opposite (Calc wrong) for the following:

-8796093022207,00
 8796093022207,00
             0,52

[edit 2023-09-25] some time back LO Calc implemented another summation algorithm ( Kahan / Neumaier / AVX ) and avoids some of above problems, be aware that now

'=(0.52 + 8796093022207 - 8796093022207)' -> 0.51953125 
has different result than 
'=SUM(0.52,8796093022207,-8796093022207)' -> 0.52

[/edit]

The results of Calc still vary due to other influences, e.g. for 'SUM' the first 'non-zero' summand is reserved and calculated last to spot a probability that a small result should be zero. This may vary from function to function and is partly affected / broken by empty cells within the operand stack. Thus expect unpredictable 'fuzzy' results already in Calc itself.

The deviations are mostly much smaller and go unnoticed as the results are rounded to 15 digits for display, but the 'value' of the cell is not corrected accordingly and the deviations may cause boosted errors in downstream calculations. They don't only affect extreme values as chosen for the sample above but also normal 'allday' calculations. The classic sample is '=1234 + 0,12 - 1234' resulting in '0,1199999999999xx' with >=13 decimals digits display. Depending on the order of operands Calc and Excel fail differently.

(compared LibreOffice Calc dev. 7.2 / Microsoft Excel 2010 64-bit)

[bs - 2021-03-12, edited 2021-04-28]