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

Format your cells as mm:ss.00 and then you will get to the second decimal
point so you can se if it is actually less than or equal to C2.

edvwvw

Horatio J. Bilge, Jr. wrote:
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

--
Message posted via http://www.officekb.com

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


"edvwvw via OfficeKB.com" wrote:

Format your cells as mm:ss.00 and then you will get to the second decimal
point so you can se if it is actually less than or equal to C2.

edvwvw

Horatio J. Bilge, Jr. wrote:
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

--
Message posted via http://www.officekb.com

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

"Horatio J. Bilge, Jr." wrote in
message ...
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

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


"David Biddulph" wrote:

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

"Horatio J. Bilge, Jr." wrote in
message ...
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

=MROUND(C18,1/24/36000) or =MROUND(C18,1/864000)
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...
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


"David Biddulph" wrote:

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

"Horatio J. Bilge, Jr." wrote
in
message ...
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

That's slick - I didn't think of using fractions. I had trouble using it in
the CF condition formula, though. The formula I used was
"J18<=MROUND($C18,1/24/36000)" and I got an error saying that you can't
reference other worksheets or workbooks in a conditional format.
I used the MROUND function on C18 instead, and it worked fine.

Thanks for the help!
~ Horatio


"David Biddulph" wrote:

=MROUND(C18,1/24/36000) or =MROUND(C18,1/864000)
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...
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


"David Biddulph" wrote:

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

"Horatio J. Bilge, Jr." wrote
in
message ...
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

I think the problem with CF is that MROUND is in the Analysis ToolPak, and
CF treats that as being another workshhet or workbook.
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...
That's slick - I didn't think of using fractions. I had trouble using it
in
the CF condition formula, though. The formula I used was
"J18<=MROUND($C18,1/24/36000)" and I got an error saying that you can't
reference other worksheets or workbooks in a conditional format.
I used the MROUND function on C18 instead, and it worked fine.

Thanks for the help!
~ Horatio


"David Biddulph" wrote:

=MROUND(C18,1/24/36000) or =MROUND(C18,1/864000)
--
David Biddulph

"Horatio J. Bilge, Jr." wrote
in
message ...
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


"David Biddulph" wrote:

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

"Horatio J. Bilge, Jr."
wrote
in
message ...
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