Conditional Format not Behaving Properly

[brianv]

This is a simple conditional format, applies to cells K7:K41

File Link:
https://1drv.ms/x/s!ApYmeret_YzZnQeQXzk8PPQ4J2Yk

This is for a timesheet....

Condition Format Rule:
Formula: =$k7<>sum($L7:$O7)
Basically, if the total hours scheduled do not match the total hours assigned, then the cell goes RED.

Cells K7:K41 is formula referencing cells U7:U41 [=u7]
Cells U7:U41 is a formula =S7-T7:
Cells S7:S41 calculates the total hours charged based on a military time format 10:00PM (start) - 14:00PM (end) = 3.00
Cells T7:T41 calculates the lunch breaks (in hours) so U7:U41 is the total hours minus the lunch break hours.

The conditional format IS (yes IS) behaving properly on all cells K7:K41 with this exception:
If the start time is 6:30AM and ends at 7:00AM, so the value of cell K7 is 0.50 and the sum of L77 is 0.50, but the still cell goes active,

The misbehavior is ONLY for the times between 6:30AM-7:00AM

If the start time is any other time, the format behaves properly.... it only misbehaves on that specific condition.

Its got to be something simple and stupid... i just cant see what....

Thanks
BV

 

[6StringJazzer]

Your CF is OK but you have a precision problem in your math. In Row 12, for example, the figure in column L is exactly 0.5. However, the figure in column S is

0.500000000000001

So they are not equal. This is a very common problem when working with real numbers on computers.

To fix this you should round the formula in column S to 2 decimal places, since that the precision in your display.

=ROUND(IF($F12<$C12,(($F12-$C12)*24)+24,($F12-$C12)*24),2)

I haven't done the analysis to figure out why this only happens during those particular times, but this is definitely your problem.

 

[Fluff]

At a guess your values are greater than 0.5
On one of the problem cells increase the number of decimals shown to about 20.
What does the value show

 

[brianv]

yes, rounding does the trick... I thought about the precision issue, so i increased my decimal to 10, but apparently not enough. I didn't think to round though because of that and that it didnt make sense that it only affected that specific time condition.

It just doesn't make sense why there should be that level of discrepancy for an relatively even number on the time.

Thanks for your input!

 

[6StringJazzer]

It just doesn't make sense why there should be that level of discrepancy for an relatively even number on the time.

This happens because is it not a relatively even number on the time. It only seems even to a human.

First, I'm sure you are aware that some numbers cannot be represented exactly in our base 10 system. For example, 1/3 is written as 0.333.... We cannot write this fraction as an exact decimal number. No matter how many decimal places you write, it will always be just a little bit smaller than 1/3. The same is true in any base system. In base 2, for example, you cannot exactly represent 1/10. Computers use base 2.

In Excel, time is represented as real number fraction of a day. So, for example, 7:00 AM is stored as 7/24. The result of this is 0.291666... so cannot be represented exactly as a decimal number. This is not a nice, round number, even though it is an exact hour of the day. It cannot be represented exactly in binary either. So when you do arithmetic with this number, you get rounding on the last digit of precision. It shows up as 0.291666666666667.

So that is why you sometimes get this tiny error when you do floating point arithmetic on a computer. Programmers know that you should never do tests for exact equality on two real numbers, that you need to take into account a tolerance (e.g., rounding).

 

[brianv]

That is a great explanation, thank you.... i hadn't thought that time being a fraction of day, its is, i just hadnt thought of it in regards to how excel treats it.... that make sense.

I appreciate it.