Arithmetic Accuracy

Question

In the article

https://learn.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result discusses calculation errors in Excel related to the subtraction of close floating point numbers. But decimal arithmetic, implemented in Excel based on the IEEE754 standard, does not guarantee correct calculations over the entire range of declared decimal values, even for simple arithmetic operations.

Here are some examples:

Addition

A1 = 39579678828828700000

A2 = 100000000000451000

A1 + A2 = 39679678828829100000

While the correct answer is 39679678828829200000

Multiplication

A1=100000000052251

A2 = 999

A1*A2 = 99900000052198800

While the correct answer is 99900000052198700

Division

A1=100000000052252

A2=193

A1/A2 = 518134715296.643000000

While the correct answer is 518134715296.642

The subtraction operation is the most dangerous in Excel. This applies not only to the subtraction of close numbers. A subtraction of even distant numbers in the sequence of arithmetic operations is likely to result in an error. For example

A1=1234567890.11

A2 = 1230000000

(A1-A2)*999 = 4563322219.88990

While accurate to 15 significant digits, the correct answer is 4563322219.89000

The question arises, is it possible to use Excel for arithmetic calculations?

The solution to this problem in https://www.techrxiv.org/articles/preprint/The\_arithmetic\_of\_binary\_equivalents\_of\_decimal\_numbers/19294511

Answer 1

Thank you, Shakiru, for your honesty.

The problem with the accuracy of calculations in Excel is the problem of binary calculations for decimal numbers, which are based on the IEEE754 standard. I describe this problem in my article https://www.techrxiv.org/articles/preprint/The\_arithmetic\_of\_binary\_equivalents\_of\_decimal\_numbers/19294511

In this article, I propose a solution that allows binary arithmetic to perform decimal calculations with decimal precision. With the same accuracy as if the calculations were carried out manually, as taught in school.

Glad to chat

Iouri

Answer 2

Hi Yuri S!

Thank you for the feedback.

I understand you better now.

I have got no idea about this, maybe the users would.

I wait to learn from other users.

Kind Regards, Shakiru

Answer 3

Hi Shakiru!

Thanks for responding to my post. However, you gave me an example with the same result as in my post:

A1 = 39579678828828700000

A2 = 100000000000451000

A1 + A2 = 39679678828829100000

If you calculate this amount manually, the answer should equal 39679678828829151000. If you round this number to 15 significant figures, we get the number 39679678828829200000, which differs from the response received in Excel by 100000.

Answer 4

Hi Yuri S!

Thank you for writing to the Microsoft Answer Community Forum. I am Shakiru, a user like you, and I am glad to be helping you out today.

Based on my knowledge of excel, Yes, you can perform arithmetics calculations in Excel, even what you posted above, it is possible to do arithmetics of 15-digit numbers even more as shown in the link below.

You will need to convert the value in the cells to a Number in the format cells

Kind Regards, Shakiru