Calculation problem

[_Nebur_]

Below you can see a very simple formula (the ALS-formula is just the Dutch translation of the IF-formula) and calculation:

Excel.png

How is this possible? Why is the value of cell A3 not zero?

[JohnTopley]

Please post a file (not least because many of us cannot see images) and we need data to work with.

[_Nebur_]

Here you go.

Krediet.xlsx

[JohnTopley]

See

https://blogs.office.com/2008/04/10/...wrong-answers/

[joeu2004]

How is this possible? Why is the value of cell A3 not zero?

The simple answer is: when you expect an arithmetic result to be accurate to some degree of precision, you should explicitly round to that number of decimal places.

In your example, =IF(C6>0,C11-G3,0) should be =IF(C6>0,ROUND(C11-G3,2)) in Mensualiteit!H3.

Note: I use comma to separate parameters; you use semicolon.

Of course, you should make similar changes elsewhere.

To explain....

This is a common(!) anomaly of 64-bit binary floating-point, which Excel uses to represent numbers and perform arithmetic.

Most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of 2 ("bits") times an exponential factor. For example, 0.1 (1/10) is represented by 1/16 + 1/32 + 0/64 + 0/128 + 1/256 + ....

Moreover, because there are only 53 bits to represent the integer part as well, the number of bits available for the approximation of the decimal fraction varies. Consequently, the approximation of the decimal fraction is not the same in the representation of all values.

For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!) because the approximation of 0.1 in 10.1 is not the same as the approximation of 0.1 itself. In 10.1, 4 bits are used to represent 10, leaving only 49 bits to represent 0.1. But in 0.1 itself, all 53 bits are used to form the approximation.

Your example is similar. In 124.15 in Mensualiteit!G3, 6 bits are used to represent 124, leaving 47 bits to approximate 0.15. But in 100,124.15 in Mensualiteit!C11, 16 bits are used to represent 100,124, leaving 37 bits to approximate 0.15.

Compounding the problem: the result of the formula in C11 is not properly rounded to 2 decimal places, highlighting the fallacy of using CEILING(...,0.01) with that intent. Generally, CEILING is unreliable when the second parameter has a decimal fraction (other than a power of 2). In this case, it is better to use ROUNDUP(...,2).

So these are the exact values in the Mensualiteit worksheet:

Please Login or Register  to view this content.

Note: I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats, an arbitrary limit.

Note that H3 would be exactly 10000 if you use ROUNDUP in C11. But that is only a coincidence. It is still prudent to use ROUND(...,2) in H3.

[_Nebur_]

Okay, thanks for the replies. :-)