conditional formatting max
 

I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless of
month. For example for Phil it would be 98.16, for Mech it would be 96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04

conditional formatting max
 

So if the max occurred in the 60 or 90 day column, then nothing would be
highlighted?

If yes...

With the data in A1:E7 (headers in row 1)
I selected E2:E7
and then Format|conditional formatting
formula is:
=e2=max($c2:$e2)

In your test data, wouldn't all those sites get the highlight?


JH wrote:

I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless of
month. For example for Phil it would be 98.16, for Mech it would be 96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04

--

Dave Peterson

conditional formatting max
 

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04

conditional formatting max
 

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson

conditional formatting max
 

"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Ahhh. Now I think *I* understand the original post.

When I copied and pasted the data into my XL somehow I thought that *site
Mon/Yr* was one column. I started to write the post then wend back to check
the worksheet and altered it and that is why I said that the data was in
A2:E7 but my, (altered), formula references only to Column D. The LEFT()
was cutting off the Mon/Yr which I has concatenated into column A. But yes,
now that I see it you are correct.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site
regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson

conditional formatting max
 

Ah, who knows.

But between us, I think we've covered 2 of the many possibilities!

Sandy Mann wrote:

"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Ahhh. Now I think *I* understand the original post.

When I copied and pasted the data into my XL somehow I thought that *site
Mon/Yr* was one column. I started to write the post then wend back to check
the worksheet and altered it and that is why I said that the data was in
A2:E7 but my, (altered), formula references only to Column D. The LEFT()
was cutting off the Mon/Yr which I has concatenated into column A. But yes,
now that I see it you are correct.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Dave Peterson" wrote in message
...
Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site
regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson

conditional formatting max
 

If you're using xl2003 and below and conditional formatting, then you only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

Thanks Dave.
I only have three sites and was wondering if I could add something to the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then you only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

You could have 3 rules (click on the Add button on the bottom of the
Format|Conditional formatting dialog):

=E2=MAX(("phil"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("Mech"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("nua"=$A$2:$A$7)*($E$2:$E$7))

Give each a nice (different) format.

JH wrote:

Thanks Dave.
I only have three sites and was wondering if I could add something to the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then you only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

Thanks Dave. This information is great! You have been very helpful. Thanks
again.

"Dave Peterson" wrote:

You could have 3 rules (click on the Add button on the bottom of the
Format|Conditional formatting dialog):

=E2=MAX(("phil"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("Mech"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("nua"=$A$2:$A$7)*($E$2:$E$7))

Give each a nice (different) format.

JH wrote:

Thanks Dave.
I only have three sites and was wondering if I could add something to the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then you only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly identifies the
top % for each site. Would you also show me how to include a change to the
font color for the top percent for each site, for example the top % for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional formatting to
highlite the 120 day column with the highest % for each site regardless
of
month. For example for Phil it would be 98.16, for Mech it would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

Hi Dave,

I wonder if you could explain how these formulas work, or rather why it is
that if you (normally) enter the formulas in adjacent cells you, or rather
I, get TRUE each time Column A is "phil", "Mech" or "nua" as appropriate.
(Although if I highlight the formula and press f9 I get FALSE returned.)

To get TRUE only where the Conditional Formatting changes colour I need to
array enter the formulas. Why is it that you don't have to array enter them
into Conditional Formatting?

I am using XL97

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
You could have 3 rules (click on the Add button on the bottom of the
Format|Conditional formatting dialog):

=E2=MAX(("phil"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("Mech"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("nua"=$A$2:$A$7)*($E$2:$E$7))

Give each a nice (different) format.

JH wrote:

Thanks Dave.
I only have three sites and was wondering if I could add something to the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then you
only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have
to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly
identifies the
top % for each site. Would you also show me how to include a change
to the
font color for the top percent for each site, for example the top %
for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in
Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional
formatting to
highlite the 120 day column with the highest % for each site
regardless
of
month. For example for Phil it would be 98.16, for Mech it
would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

I don't think I can explain the behavior of having to array enter the formula in
a cell, but using the same formula works in the conditional formatting dialog.

"That's just the way excel works" is as good as I can say.



Sandy Mann wrote:

Hi Dave,

I wonder if you could explain how these formulas work, or rather why it is
that if you (normally) enter the formulas in adjacent cells you, or rather
I, get TRUE each time Column A is "phil", "Mech" or "nua" as appropriate.
(Although if I highlight the formula and press f9 I get FALSE returned.)

To get TRUE only where the Conditional Formatting changes colour I need to
array enter the formulas. Why is it that you don't have to array enter them
into Conditional Formatting?

I am using XL97

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Dave Peterson" wrote in message
...
You could have 3 rules (click on the Add button on the bottom of the
Format|Conditional formatting dialog):

=E2=MAX(("phil"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("Mech"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("nua"=$A$2:$A$7)*($E$2:$E$7))

Give each a nice (different) format.

JH wrote:

Thanks Dave.
I only have three sites and was wondering if I could add something to the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then you
only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd have
to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly
identifies the
top % for each site. Would you also show me how to include a change
to the
font color for the top percent for each site, for example the top %
for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in
Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional
formatting to
highlite the 120 day column with the highest % for each site
regardless
of
month. For example for Phil it would be 98.16, for Mech it
would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

conditional formatting max
 

Thank you anyway Dave.

When I was writing my version of the formula I was writing it in a cell
because I find it easier to enter it there than the Conditional Formatting
dialog box and I found that I had to array enter it to get them to work
correctly. When trying to enter it in the Conditional Formatting naturally
I array entered it but the curly braces did not appear. I was going to tell
JH to array enter it nevertheless but when I tried it normally entered it
worked so I thought that I must have been *confused* before and put it out
of my mind. Then when I was testing your formulas, again in a cell, I found
that it was true that it had to be array entered in a cell.

mmmmm......

I just tried it again with a made-up formula that needed array entering in a
cell but doesn't in Conditional Formatting could it be the case that
Conditional Formatting *automatically* array enters formulas I wonder?

Perhaps someone with in-depth understanding of the inner workings of XL can
tell us.

--
Even more Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
I don't think I can explain the behavior of having to array enter the
formula in
a cell, but using the same formula works in the conditional formatting
dialog.

"That's just the way excel works" is as good as I can say.



Sandy Mann wrote:

Hi Dave,

I wonder if you could explain how these formulas work, or rather why it
is
that if you (normally) enter the formulas in adjacent cells you, or
rather
I, get TRUE each time Column A is "phil", "Mech" or "nua" as appropriate.
(Although if I highlight the formula and press f9 I get FALSE returned.)

To get TRUE only where the Conditional Formatting changes colour I need
to
array enter the formulas. Why is it that you don't have to array enter
them
into Conditional Formatting?

I am using XL97

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Dave Peterson" wrote in message
...
You could have 3 rules (click on the Add button on the bottom of the
Format|Conditional formatting dialog):

=E2=MAX(("phil"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("Mech"=$A$2:$A$7)*($E$2:$E$7))
=E2=MAX(("nua"=$A$2:$A$7)*($E$2:$E$7))

Give each a nice (different) format.

JH wrote:

Thanks Dave.
I only have three sites and was wondering if I could add something to
the
code to say if A2 = Phil then the font color should be blue.

"Dave Peterson" wrote:

If you're using xl2003 and below and conditional formatting, then
you
only get 3
formats (plus the normal format).

If you have more than 3 sites and want more than 3 colors, you'd
have
to use a
different approach--maybe some sort of event macro??

JH wrote:

Thanks to you both for your response. Your formula correctly
identifies the
top % for each site. Would you also show me how to include a
change
to the
font color for the top percent for each site, for example the top
%
for Phil
would be red and the top % for Mech would be blue.
Thanks again.

"Dave Peterson" wrote:

Ahhh. Now I think I understand the original post.

Is there a reason you used left() and not column E?

I selected E2:E7 and used this as the format|Conditional
Formatting|formula is:

=E2=MAX((A2=$A$2:$A$7)*($E$2:$E$7))
(with E2 the activecell)


Sandy Mann wrote:

With the data you provided in A2:E8, this worked for me in
Conditional
Formatting:

=D3=MAX((LEFT($A$3:$A$8,3)=LEFT(A3,3))*$D$3:$D$8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"JH" wrote in message
...
I am using excel 2003 and would like to use conditional
formatting to
highlite the 120 day column with the highest % for each
site
regardless
of
month. For example for Phil it would be 98.16, for Mech it
would be
96.42.
Any help would be much appreciated. Thanks.

site Mon/Yr 60 day 90 day` 120 day
Phil Jan-06 89.61 95.54 96.75
Mech Jan-06 85.44 93.54 95.02
Nua Jan-06 88.44 93.58 95.6
Phil Feb-06 92.77 96.37 98.16
Mech Feb-06 87.81 95.6 96.43
Nua Feb-06 88.89 93.71 96.04



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson