Excel Calculation Error

[mco5044]

I'm having an issue where excel is clearly making a true statement false. Has anyone else ever experienced this before?

Here is my formula: =Round(If(AND(0<=B2-J7<=0.1,H7>0),E7+U2)

B2 = .35
J7 = .3176
h7 = 1
E7 = .81
U2 = .03

Based on these numbers the formula should result in the cell equaling .84

When I evaluate the formula it agrees that 0.0324 (b2-J7) is greater than or equal to 0 but it is claiming that it is not less than or equal to 0.1

I've checked myself a million times here and I'm pretty sure 0.0324 is in fact less than 0.1.

Am I going insane?

Any help with this issue is hugely appreciated!

Thanks

 

[Eric W]

I believe your formula got cut off. The bulletin board software sometimes interprets a < as HTML code. To prevent that, put a space before and after each one in your formula.

As to your formula, I think you need to code the AND part differently. Try:

AND(0<=B2-J7,B2-J7<=0.1,H7>0)

Excel doesn't understand multiple conditionals, you have to separate them.

Good luck!

 

[SpillerBD]

maybe you are meaning this formula....

=ROUND(IF(AND(0<=B2-J7,B2-J7<=0.1,H7>0),E7+U2),2)

 

[mco5044]

I believe your formula got cut off. The bulletin board software sometimes interprets a < as HTML code. To prevent that, put a space before and after each one in your formula.

As to your formula, I think you need to code the AND part differently. Try:

AND(0<=B2-J7,B2-J7<=0.1,H7>0)

Excel doesn't understand multiple conditionals, you have to separate them.

Good luck!

Thank you so much! I will definitely remember that forever about the conditional statements. This thing was driving me nuts!

 

[Eric W]

Experience is definitely the best teacher!

Glad we could help.

 

[joeu2004]

Here is my formula: =Round(If(AND(0<=B2-J7<=0.1,H7>0),E7+U2)
B2 = .35
J7 = .3176
h7 = 1
E7 = .81
U2 = .03
Based on these numbers the formula should result in the cell equaling .84

It is unclear what you want to return if the conditions are not met. I will assume zero (FALSE).

If B2 and J7 are always less than 1, you are safe with the following, by coincidence:

=ROUND(IF(AND(0<=B2-J7,B2-J7<=0.1,H7>0),E7+U2),2)

But if B2 and J7 might be greater than 1, I think you should write:

=ROUND(IF(AND(0<=B2-J7,ROUND(B2-J7,4)<=0.1,H7>0),E7+U2),2)

I choose to round to 4 decimal places because that is the apparent precision of J7.

The issue is: B2-J7<=0.1 might return FALSE even though the difference appears to be 0.1 on paper. For example, consider when B2 is 5.03 and J7 is 4.9300.

This is due to anomalies of 64-bit binary floating-point arithmetic, which Excel uses to represent numbers.

Most non-integers cannot be represented exactly. So they are represented by an approximation that might be infinitesimally less than or greater than the decimal fraction. For example:

5.03       5.03000000000000,024868995751603506505489349365234375
4.93       4.92999999999999,971578290569595992565155029296875
5.03-4.93  0.100000000000000,53290705182007513940334320068359375
0.1        0.100000000000000,0055511151231257827021181583404541015625

(I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel will format, rounding the 16th digit, an arbitrary limitation.)

Note that the internal representation of 5.03 is larger, and the internal representation of 4.93 is smaller. So their difference is larger than the internal representation of 0.1.

In this example, their difference rounded to 15 significant digits is 0.100000000000001, which is visibly larger than 0.1.

When B2=J7 rounded to 15 significant digits is the same as 0.1, B2-J7<=0.1 returns TRUE even if the internal representation of B2-J7 is actually larger than the internal representation of 0.1. This is a quirk of Excel. VBA would still return FALSE.

 

[joeu2004]

PS....

Most non-integers cannot be represented exactly. So they are represented by an approximation that might be infinitesimally less than or greater than the decimal fraction. For example:

5.03       5.03000000000000,024868995751603506505489349365234375
4.93       4.92999999999999,971578290569595992565155029296875
5.03-4.93  0.100000000000000,53290705182007513940334320068359375
0.1        0.100000000000000,0055511151231257827021181583404541015625

[....]
Note that the internal representation of 5.03 is larger, and the internal representation of 4.93 is smaller. So their difference is larger than the internal representation of 0.1.

I might also point out that the approximation of the same decimal fraction might vary, depending on the magnitude of the integer part. For example

9.03       9.02999999999999,93605115378159098327159881591796875
8.93       8.92999999999999,971578290569595992565155029296875
9.03-8.93  0.0999999999999996,447286321199499070644378662109375
0.1        0.100000000000000,0055511151231257827021181583404541015625

In this case, 9.03-8.93<=0.1 is TRUE, as expected, because the internal representation of 9.03-8.93 is indeed less than the internal representation of 0.1, by coincidence.

The point is: it is difficult (to say the least) to predict the outcome when B2-J7 differ by 0.1 on paper.

So as usual, it is prudent to explicitly round the result of B2-J7 to the expected precision (4), even if it might seem unnecessary for some values of B2 and J7.