conditional formatting max

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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)
 
Dave Peterson said:
Ahhh. Now I think I understand the original post.
Click to expand...

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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.
 
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??
 
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.
 
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.
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.
Click to expand...
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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 said:
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top