# Excel Calculation Error

#### mco5044

##### Board Regular
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

##### MrExcel MVP
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!

Last edited:

#### SpillerBD

##### Well-known Member
maybe you are meaning this formula....
Code:
``=ROUND(IF(AND(0<=B2-J7,B2-J7<=0.1,H7>0),E7+U2),2)``

#### mco5044

##### Board Regular
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

##### MrExcel MVP
Experience is definitely the best teacher!

#### joeu2004

##### Well-known Member
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:
Rich (BB code):
``````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.

Last edited:

#### joeu2004

##### Well-known Member
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:
Rich (BB code):
``````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
Rich (BB code):
``````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.

1,082,295
Messages
5,364,359
Members
400,792
Latest member
Dxmiian

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...