Calc division gives 'significantly different (smaller) result' to Excel - one must be incorrect!

English
#1

Calc division gives ‘significantly different (smaller) result’ to Excel
… therefore ‘one must be incorrect’ !

Edit:
For clarification…
This problem can be replicated using just 3 cells:

314.906088434789 / 104,975

IE. The two source numbers above, and the division formula.

If you make the division in Calc, and similar division in other calculators … you will see if there is any discrepancy in the results.

Also…
By attempting to subtract different results, you may also see differing results.
End of edit

Background
I’m working on a set of data that requires many two cell divisions (small number/large number).
The grid is 44 columns x 11 rows = 484 cells.
The sum contents of these cells = 1

Each cell in a row, is then divided by 5 to create 5 new rows (one row of cells is divided by 10).
IE. The initial small numbers get much smaller.

The new grid is 44 columns x 60 rows = 2640 cells
The sum contents of these cells should = 1
However…
Summing the 2640 cells = 0.9781466396134060

Investigating Division
I picked a random division formula cell

314.906088434789 / 104,975 = 0.00299981983871304 (Libre Calc)

I ran the same division at calculator.net, Windows calculator, and Excel
314.906088434789 / 104,975 = 0.0029998198469616 ___(calculator.net)
314.906088434789 / 104,975 = 0.0029998198469616 ___(win calculator)
314.906088434789 / 104,975 = 0.002999819846961550 _(Excel)

If we round the Excel result by one decimal place, all 3 reference calculations agree =
0.0029998198469616 (the 3 reference calculations)
0.0029998198387130 (Libre Calc)

The Libre Calc result is significantly smaller than the Excel result.

Investigating Subtraction
To find the difference between division results in Calc & Excel, I subtracted Calc from Excel.
This subtraction excercise was done in both Calc & Excel
0.00299981984696155 - 0.00299981983871304

= 0.000000000008248512983755060 (Excel)
= 0.000000000008248509510000000 (Calc)

Excel produces more decimal places; but we can round the result, for comparison:

= 0.00000000000824851298 (Excel)
= 0.00000000000824850951 (Calc)

Once again the Calc result is smaller than the Excel result.

Other Potential Issues
I have not yet investigated ‘addition’, nor ‘multiplication’.

In fact, the 2640 cells are tasked with multiplying a number.
They are selected by row >= <= and column >= <= and summed
The resulting total multiplies a given number.

Using this method, I selected all 2640 cells (should equal 1)
… and multiplied 104,975,000

The result = 102,680,944
The calculation was incorrect by 2,294,056 (2.3 million)

Finding the error
Referring back to my opening statements:

The sum contents of these (2640) cells should = 1
However…
Summing the 2640 cells = 0.9781466396134060

Using a web calculator rapidtables.com
104,975,000 x 0.9781466396134060 = 102,680,943

The ‘selection sum’ to multiply, and rapidtables.com, both produce the same result.

Conclusion
The problem appears to be the result of…
'division in Calc, producing results that are numerically smaller than other methods of division.

Is this a known issue?

I’m at a loss as to what to do, because the division calculation is the most basic arithmetic.
can anyone cast light on this issue … or maybe outline a fix?

:slight_smile:

#2

Your results are interesting. Can you upload a very basic (not 2000-cell) example spreadsheet with fixed results from other sources and calculated results from Calc so that it can be tested on different systems?Obviously, logarithms, etc., may be in use in the underlying system, so everything from the CPU to the OS to Calc itself might be in play unless a developer comes on and clarifies where Calc gets its math from.

#3

Thanks for taking an interest joshua4 :slight_smile:

I understand your request, but it may be that, by my stating the background scenario, this clouded the underlying question that relates to simple arithmetic calculation in Calc.

The number of cells involved, merely highlighted the ‘seeming’ error.
I then carried out due diligence … again, perhaps clouding the issue.

The real problem lies here:

Similarly:

Therefore, from just 3 cells (Number A, Number B, and formula A/B) we have the error, as compared to the three reference calculators.

Subtracting, to find the error difference, produces another error.

Therefore…
No need for a complex data set … simply divide my example values yourself, and then divide them by other means.

See what you get, and report back.

If your results are different to mine, and they agree with other calculators; then we can consider O/S’s and computing architecture (or whatever) :crazy_face:

#4

Actually, I was thinking of the example upload in order to lower the bar for getting people to try it out. It’s sort of like having a self-addressed stamped envelope. Easy enough to write an address, but people will wait weeks before mailing if they have to.

But in any case what is happening here isn’t really a matter of precision so much as how a system or program decides to get that precision. Take the example

The correct answer is 824851/100000000000000000. That’s all there can be…it’s just subtraction. In @spreadit 's results Excel seems willing to overshoot the binary fraction convergence while Calc undershoots the convergence. That’s interesting, but any digit after the 824851 is just Gibb’s ringing of a sort as we use (or don’t) 1/2, 1/4, 1/8, 1/16, … to approximate the mantissa.

#5

Using LibreOffice Calc version 7.2.5.2/64, Norwegian language (comma is decimal sign), on Windows 10/64

Formula …

=314,906088434789/104975

… yields result …

0,00299981984696155

This is exactly the same as the Excel result you have given. Casio’s online calculator, set to 30 decimals’ precision, confirms this result.

Did you type in the numbers exactly in all the tests, or are your Calc numbers results from other calculations?

If the divisor in the Calc calculation is really 104975,00029 or thereabout, you will get the result you provided. If that is the cause, the error is not in the software. :wink:

Which solution is appropriate depends on the significance of the numbers in question. What do they represent, how did you arrive at them, and what are you trying to determine?

#6

Same on Ubuntu 20.04.

#7

Thanks keme1 :slight_smile:
I didn’t type … I used traditional ‘copy & paste’

OMG Why me?
… It’s because your here lad :wink:

Here is the screenshot:

Calc_Division_Error
Calc_Division_Error1292×265 11.3 KB

I have the formula cell highlighted.(edit: the formula cell is to the far right … it’s been a long day)
In the formula bar, the formula can be seen (Click on the image to expand it).

Thoughts anyone?

Maybe it will all be right in the morning (though I doubt it) :frowning_face:

#8

… and if you alter the format for C24 to display 4+ decimals?

#9

.
All that will do is auto-round the display of the number … it won’t change the number, it will only change the display to a rounded up version.
.
The only way to see the number, is to add decimal places until zeros appear.
That way, you can see the number that the spreadsheet sees.

#10

Oh hang on… FFS !!!
I just realised that you were talking about 104,975.
.
You are not gonna believe this, because I had expanded the decimals previously, but amazingly NOT ENOUGH!
.
Get this:
104,975.00028864600
.
What are the damned chances of that?
Normally, when expanding decimals … if you see 3 zeros, there’s nothing else coming.
.
OMG, I need to test further tomorrow
… and if all is fine … then look at why I’m getting the final sum error.
.
Thanks keme1 for your alert thinking :+1:
More on this tomorrow :slight_smile:

#11

Pretty high, when the result difference you observe is around 0.0000000000082485..., which is about 1/(3.65*108) of the expected/obtained result :wink:

#12

and now you may realize, why you are asked to provide a test-file from your source…
If we all create our own set, we may all talk of slightly different figures to no avail.

2 Likes
#13

All points taken :slight_smile:

This was just the perfect storm.
I’ve now got to find out why my data is summing to less than one.

Thanks everyone :slight_smile:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.