Conditional Formatting in Excel based on two values

Question

So I have a small question and I have scoured the net only to come up with nothing

I want to use conditional formatting to format a row based on values in other cells.

Lets say I have the following info

NAME     points    age
BOB      8         12
JANE     6         8
JOE      10        9

I can get to the point where I can apply the format to the last row if lets say I use the max function to see who has the most points. I can also highlight the first row if I apply the max function to see who has the highest age

My question is I want to highlight the row with the person that has the highest age but the lowest points for that age

If I do a min on the age it currently returns the row for 'JANE' and then a MAX on the age I get the row for 'BOB' and if I use the AND function I get no formatting due to the fact that both conditions are met but not for the same row?

Please can someone point me in the right direction I really dont know what I am looking for but I assume that the AND is causing the issue.

Side note OR doesn't help as that will format both rows and I am looking for one only.

Given that no row matches both conditions, what are you expecting to happen? — Rory
– Rory, Commented Feb 7, 2019 at 10:58
That's exactly why I asked the question I was hoping someone could point me to a function or something that would return true for the highest age but the lowest points for that age — Jacques Ramsden
– Jacques Ramsden, Commented Feb 7, 2019 at 11:04
If you want to highlight the row with the highest age and lowest points for that highest age (which was the key bit you left out originally), you'll need something like =AND(age_cell=MAX(age_range),points_cell=MIN(IF(age_range=max(age_range),points_Range,""))) type formula. — Rory
– Rory, Commented Feb 7, 2019 at 11:07
Shot will give it a go in a few min and feed back will update the question with the missing info also. — Jacques Ramsden
– Jacques Ramsden, Commented Feb 7, 2019 at 11:09
Works like a charm please add it as an answer so I can accept — Jacques Ramsden
– Jacques Ramsden, Commented Feb 7, 2019 at 12:28

Rory · Accepted Answer · 2019-02-07 12:33:01Z

1

If you want to highlight the row with the highest age and lowest points for that highest age, you'll need something like this formula:

=AND(age_cell=MAX(age_range),points_cell=MIN(IF(age_range=max(age_range),points_Range,"")))

adjusted to match your actual ranges.

answered Feb 7, 2019 at 12:33

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional Formatting in Excel based on two values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related