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