Conditional Format based upon if statement and max

C

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

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
 
C

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

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"
 
C

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

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
 
C

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

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".
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top