Logical Formula with comparison operator not behaving as expected

Hi all,

I am currently having a challenging problem with Excel and I think that many people on the forum will be interested with the issue. I hope that my message is not too long and I will try to be as concise as possible.

Please download the excel file with the problem from the attachment to this post.

The issue is with the logical formula, B9-(F22+TIME(0,18,0))<=TIME(0,10,0). This logical formula is part of the formula found in cells G22, F23, G23, F24, G24. Below are the formulae in the important cells of the excel file:

F20 = 03:27:00
G20 = SUM(F20+TIME(0,B23*7.5,0))
F21 = SUM(F20+TIME(0,B23*7.5,0))
G21 = F21+TIME(0,3,0)
F22 = F21+TIME(0,3,0)
G22 = IF(B1="No",F22+TIME(0,18,0),IF(B9-(F22+TIME(0,18,0))<=TIME(0,10,0),B9,F22+TIME(0,18,0)))
F23 = IF(B1="No",F22+TIME(0,18,0),IF(B9-(F22+TIME(0,18,0))<=TIME(0,10,0),"N/A",F22+TIME(0,18,0)))
G23 = IF(B1="No",B10,IF(B9-(F22+TIME(0,18,0))<=TIME(0,10,0),"N/A",B9-TIME(0,10,0)))
F24 = IF(B1="No","N/A",IF(B9-(F22+TIME(0,18,0))<=TIME(0,10,0),"N/A",B9-TIME(0,10,0)))
G24 = IF(B1="No","N/A",IF(B9-(F22+TIME(0,18,0))<=TIME(0,10,0),"N/A",B9))
F25 = IF(B1="No","N/A",B9)
G25 = IF(B1="No","N/A",B10)

H30 = (B9-(F22+TIME(0,18,0)))*60*24
H31 = (TIME(0,10,0))*60*24
H32 = B9-(F22+TIME(0,18,0))<=TIME(0,10,0)

Please check the excel file.

Cell H30 in the green area displays the part B9-(F22+TIME(0,18,0)) converted into minutes while cell H31 displays the part TIME(0,10,0) converted into minutes. Finally in cell H32, the logical expression B9-(F22+TIME(0,18,0))<=TIME(0,10,0) is displayed after comparison between the B9-(F22+TIME(0,18,0)) and TIME(0,10,0).

I have tested the result for 3 different scenarios. In each scenario, cells highlighted in yellow, B1 and B10 and violet (B23) are kept fixed. Only the cell highlighted in orange (B9) is varied. Cells highlighted in red (H20 - H25) shows the difference between cells in column G and F, in minutes.

In theory, when the cell B9 is varied, and the difference between cell B9 and F22 is more than 28 minutes, the difference between G24 and F24 should stay fix at 10 minutes. Also, the difference between G22 and F22 should stay fix at 18 minutes.

Furthermore, in theory, when the cell B9 is varied, and the difference between cell B9 and F22 is less than or equal to 28 minutes, F23, G23, F24, G24 should all output "N/A" and G22 should be equal to B9.

Now here is the issue: In practice, sometimes, when both B9-(F22+TIME(0,18,0)) and TIME(0,10,0) are equal to 10, the logical expression
B9-(F22+TIME(0,18,0))<=TIME(0,10,0) displays FALSE!!! This expression should only display FALSE only when TIME(0,10,0) < B9-(F22+TIME(0,18,0)).

Below are the results of the tests for the 3 different scenarios.

Scenario 1
B23 set to 4, B10=05:45:00AM and B9=04:27:00AM, 04:28:00AM and 04:29:00AM
Below are the screenshots for the 3 different values of B9.
1a

HTML Code: 

1b

HTML Code: 

1c

HTML Code: 

From screenshots 1a, 1b and 1c, the cells display the expected values.

Scenario 2
B23 set to 8, B10=06:10:00AM and B9=04:57:00AM, 04:58:00AM and 04:59:00AM
Below are the screenshots for the 3 different values of B9.
2a

HTML Code: 

2b

HTML Code: 

2c

HTML Code: 

From screenshots 2a, 2b and 2c, it can be seen that as far as screenshots 2a and 2c are concerned, the cells are displayed as expected. However, from screenshot 2b, cells F23, F24, G23 and G24 actually display time values even though they are expected to display "N/A". Furthermore, G22 should display 04:58:00AM but it actually displays 04:48:00AM.

Scenario 3
B23 set to 18, B10=06:18:00AM and B9=06:12:00AM, 06:13:00AM and 06:14:00AM
Below are the screenshots for the 3 different values of B9.
3a

HTML Code: 

3b

HTML Code: 

3c

HTML Code: 

From screenshots 3a, 3b and 3c, it can be seen that as far as screenshots 3a and 3c are concerned, the cells are displayed as expected. However, from screenshot 3b, cells F23, F24, G23 and G24 actually display time values even though they are expected to display "N/A". Furthermore, G22 should display 06:13:00AM but it actually displays 06:03:00AM. An important point to note is that in the difference column, cell H23, the value displayed is 0.0000000000000799361 despite the fact that cells F23 and G23 both displays 06:03:00AM.

Please see for yourself from the excel file what happens when the values are changed based on Scenarios 1,2 and 3.

Could somebody please tell me what is the problem? I badly need to know what is the problem. I have been fighting with this issue for more than 3 weeks now!

Thanks for reading

Kindest Regards
Jean Luc

FALSE is correct
=(B9-(F22+TIME(0,18,0)))*60*24 =11 which is greater than =(TIME(0,10,0))*60*24 which is 10 your formula
=(B9-(F22+TIME(0,18,0)))<=TIME(0,10,0)
is for less than or equal to

i cant test an image what is the formula in h31,h32,h33 however youll need to round the results if you are getting
0.0000000000000799361 or say 2.999999999999999 =round(your formula,1)

Please download the attachment, Problem With Formula.xls‎, for the formula of cells H30, H31 and H32. It is attached to this post.

Do you have any idea why I am getting results like 0.0000000000000799361 or 2.999999999999999? Because according to the calculation based on the formula, the results should be integers only!

you have discovered the precision problem encountered when multiplying times, its an excel thing
read here for more info
http://www.cpearson.com/excel/rounding.htm
the solution nearly always is to use round()

you could try converting to text then back with
=--TEXT(SUM(F20+TIME(0,B23*7.5,0)),"hh:mm:ss am/pm")