Cells look the same and equal but are not!

[drgkt]

Why B4 is not equal to C4?

[joeu2004]

Why B4 is not equal to C4?

First, you should format them as Custom [h]:mm:ss, since you are computing a sum of time. Then you would see that the number of hours is 51, not 3.

Second, for debugging purposes, if you temporarily format them as Number with 14 decimal places, you would see the difference: 2.13710648148149 in B4 v. 2.13710648148148 in C4. And that is only approximate; the actual difference (B4-C4) is about 8.88E-15.

It is very common to encounter such infinitesimal differences. The problem arises because Excel time is stored as a fraction of a day, which is a non-integer. And most non-integers cannot be represented exactly in 64-bit binary floating-point, which Excel uses internally to represent numbers.

To remedy this, change the formulas as follows:

B4: =--TEXT(SUM(B6:B438),"[h]:mm:ss")
C4: =--TEXT(SUM(C6:C438),"[h]:mm:ss")

The TEXT function ensures that time is rounded to the same internal binary representation as the time constant that you might write. The double negate ("--") converts text to number.

[drgkt]

W O W !
Did not even occur to me to question the fact that the number of hours was too little! I would look like a fool reporting this sum! Thanks

Now I changed B4 as per your formula and C1 turned to TRUE before I did the same to C4. Why is that?

BUT MOST IMPORTANT...

Elsewhere I utilize this formula =SUMPRODUCT($I$18:$I$452;--($A18:$A452="SomeCategory");--($H18:$H452=0)) which I suspect is wrong since column I contains cells representing time.
How should I modify this one?

Thanks again

[joeu2004]

Elsewhere I utilize this formula =SUMPRODUCT($I$18:$I$452;--($A18:$A452="SomeCategory");--($H18:$H452=0)) which I suspect is wrong since column I contains cells representing time. How should I modify this one?

Similar to before:

=--TEXT(SUMPRODUCT($I$18:$I$452;--($A18:$A452="SomeCategory");--($H18:$H452=0));"[h]:mm:ss")

formatted as Custom [h]:mm:ss.

But you might not need to do that. It depends on how you use the result of the formula.

If you compare or add it to or subtract it from other times, it is prudent to round it as demonstrated above.

However, if you do nothing with the result other than display it, you might get away without rounding it.

Now I changed B4 as per your formula and C1 turned to TRUE before I did the same to C4. Why is that?

Are you asking: why did you only need to change B4, not also C4, even though it is prudent to change both?

The answer is: Excel is trying (too hard) to simplify things for you. In this case, it misleads you into thinking B4 and C4 are equal, when in fact they are not.

The formula in C1 is essentially =(B4=C4), which returns TRUE. But if the formula were =(B4-C4=0), the result is FALSE(!).

[EDIT] Another example: =MATCH(B4,C4,0) returns a #N/A error, indicating there is truly no match.

Of course, the two formulas are the same algebraically; so the result should be the same.

The second formula is the correct result, numerically. That is, if you change only B4, it truly is different from C4. Note that =SUM(B4,-C4) formatted as Scientific displays about -2.66E-15.

The difference in the first formula is: sometimes, if two values are "close enough", Excel arbitrarily treats them as equal. The operative word is "sometimes". Excel is not consistent about when it applies that rule.

So again, IMHO, it is prudent to round all time formulas used in comparisons (for example), rather than take a chance with Excel's inconsistently applied rule.