Excel Calculation Error

mco5044

Board Regular
Joined
Jul 23, 2012
Messages
51
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
Joined
Aug 18, 2015
Messages
8,994
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
Joined
Jul 2, 2014
Messages
2,696
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
Joined
Jul 23, 2012
Messages
51
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
Joined
Aug 18, 2015
Messages
8,994
Experience is definitely the best teacher!

Glad we could help.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top