Conditional Format based upon if statement and max

[CARFL97]

What I'm attempting to do is write a formula within a conditional format that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great resource!

 

[T. Valko]

Try this:

Select the range of cells F5:F33
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND($A$5="eligible",F5=MAX(F$5:F$33))
Click the Format button
Select the desired style(s)
OK out

 

[CARFL97]

Bill,

Thank you for your response. I inadvertently left out part of what I was
attempting to accomplish. Your formula works perfect if cell $A$5 equals
eligible, however I am trying to conditional format cells F5:F33 based upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates eligible for
a corresponding individual in column "B", then I would like to conditional
format column "F" based upon the eligible individuals from column "A"

Once again, thank you in advance for your assistance. I been reading
various posts as well as the help feature in Excel and I am not able tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd

 

[T. Valko]

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

 

[CARFL97]

Bill - I thought max would return the value in column "F" with the largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column "A" who
are eligible, but I also need to identify the individual with the largest
amount in column "F" based upon meeting eligible criteria in column "A".

 

[T. Valko]

Let's try this. Based on the data below what cell(s) should be highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

 

[CARFL97]

The cell in which 100 relates to Lisa and the cell that pertains to 100 for
Tom. Thank you very much for your patience with my minimal knowledge, it's
greatly appreciated!

 

[T. Valko]

Ok, I'm officially confused!

The cell in which 100 relates to Lisa and the
cell that pertains to 100 for Tom.

Why would "Tom 100" be highlighted? Tom is not "eligible".

 

[CARFL97]

I'm sorry, I only looked at the cell value of 100 for Tom not whether he was
eligible. You are correct, the only cell that I would want to be
conditionally formatted would be the 100 that relates to Lisa.

 

[T. Valko]

Try this:

=AND(A5="eligible",F5=MAX(IF(A$5:A$33="eligible",F$5:F$33)))

 

[CARFL97]

Bill - That worked perfect! Thank you for all your help and patience!

 

[T. Valko]

You're welcome. Thanks for the feedback!