conditional formatting help

J

Joe

I'm trying to setup conditional formatting in Excel and not having much
luck.

My criteria is where any cell from d3:d28 is greater than 45, than tell me
the highest value from i3:28 and color the winning cell in "i" blue.

If possible, instead of coloring the winning cell in "i" blue, I'd like to
color the entire row it resides in.

Thanks
 
B

Biff

My criteria is where any cell from d3:d28 is greater than 45, than tell me
the highest value from i3:28 and color the winning cell in "i" blue.
Click to expand...

Need some clarification.

So, if D25 = 50 and I5 is "the highest value from i3:28" color cell I5 or
"the entire row it resides in."

Is the entire row A5:IV5?

Biff
 
M

Martin P

This is for the conditional formatting part.
In A3 let the condition be
Formula is =AND(SUMIF($D$3:$D$28,">"&45)>0,$I3=MAX($I$3:$I$28))
Copy the format to the entire range.
 
B

Bernie Deitrick

Joe,

Select all rows 3 to 28 (or cells that you actually want shaded, so that
cell A3 is the activecell), and use Format / CF... with a "Formula is" of

=AND(MAX($D$3:$D$28)>45,$I3=MAX($I$3:$I$28))

Set your format for a fill of blue, and you're done. Of course, if there
are two or more values that match then highest value in column A, they will
all be colored.

HTH,
Bernie
MS Excel MVP
 
J

Joe

Bernie,

In a nutshell, this is a spreadsheet with softball averages. Some of the
people in Column D had 2 at bats, and bat .1000 in Column I.
I want to filter out people that had less than 45 at bats and have the
highest batting average for the person over 45 at bats.

With the formula you gave me, it ended up selecting the person with 1 at bat
that is batting .1000, but it did highlight the entire row.
How can we change it so it filters out people with less than 45 at bats?
 
T

Teethless mama

Highlight your range
Conditonal Formatting
Formula Is: AND(D3>45,I3=MAX($I$3:$I$28)
Format any color you like
 
B

Bernie Deitrick

Joe,

That is a more complicated situation, one that requires that you use another
cell with a formula that returns the Maximum value for column I where column
D of the same row is over 45. In cell I2, array enter (enter using
Ctrl-Shift-Enter) the formula

=MAX((D3:D28>45)*I3:I28)

Actually, I would recommend that you use

=MAX((D3:D28>I1)*I3:I28)

and then enter the 45 into cell I1. That way, you can easily change the
at-bat-level as the season progresses. Anyway, this formula will ignore the
values in column I, even if they are the actual max, if column D from that
row is less than 45 (or, the value in cell I1).

Then, with a cell in row 3 the active cell, use this CF formula:

=AND($D3>45,$I3=$I$2)

HTH,
Bernie
MS Excel MVP
 
Top