error with conditional formatting

[Horatio J. Bilge, Jr.]

I am having trouble with some conditional formatting of times. I set up a
rule so that if a value in D2:M2 is less than or equal to the value in C2, it
will be highlighted (yellow background). The problem is that C2 is based on
an average. For example, C2 says 0:43.2, but it is actually 0:43.19. Cell J2
is 0:43.2 (exactly), and I want it to be highlighted, but it isn't.

If it helps, here is more detail on the workbook setup...
Sheet1 through Sheet3 have times to the tenth of a second in D2 through M2
(i.e., m:ss.0), and N2=AVERAGE(D2:M2). On Sheet4, I calculate the minimum of
the averages in C2=MIN('Previous:May-08'!N2). Sheet4 is the one with the
conditional formatting.

Thanks,
~ Horatio

 

[David Biddulph]

You could use the ROUND function (or MROUND) in your CF condition formula.

 

[Horatio J. Bilge, Jr.]

I'm only working with times to the nearest tenth of a second, so I need the
number to behave as it is displayed (0:43.19 would round up to 0:43.2, and I
need it to act as if it is exactly 43.2).

~ Horatio

 

[Horatio J. Bilge, Jr.]

I tried playing around with ROUND and MROUND, but I'm stumped.

I tried ROUND(C18,6), and that fixed my initial problem, but created
problems in other cells. I also tried ROUND(C18,5) and ROUND(C18,7) - both of
which had problems.

I then tried MROUND. The value for one tenth of a second (i.e., 0:00.1) is
0.00000115740740740741... So to round to the nearest tenth of a second I
tried MROUND(C18,0.00000115740740740741), and it didn't fix the problem.

Is there a way to round directly to a tenth of a second, instead of using
the long decimal value? Or am I doing it wrong?

~ Horatio