(Excel) Conditional Formatting, Change Colour for Highest Value in Column A, if Column B = "x"

Question

Essentially I'm trying to create a table for tracking fitness goals. I would like to use conditional formatting to highlight personal bests, according to the type of exercise done. So, for example, if I've done HIIT for 1 hour, and that's the longest time I've done it for, the row containing the data turns blue. If I've been running for distance, and managed to run 5.5km and beaten my previous record, the 5.5km cell turns green.

e.g.

Table with example values

How I'd like the table to look if I can highlight personal bests

So far just tackling getting the table to highlight when it's the highest value for distance is causing a problem.

I can use conditional formatting to highlight the top value using: =C3>=LARGE($C$3:$C$20,1)

I can use conditional formatting to highlight values where the exercise type is "Distance" using:=$E3="Distance"

I've tried combining the two into an IF/AND formula like: =IF(AND(C3>=LARGE($C$3:$C$20,1),$E3="Distance")) and receive an error message saying there's a problem with the formula.

I have also tried: =IF(AND(C3>=LARGE($C$3:$C$20,1),$E3="Distance"),"","") which is accepted as a formula, but does not do anything.

Is what I'm trying to do possible, and if so, what am I doing wrong with the formulas here?

Answer 1

Distance Formula

=AND($E3="Distance",$C3=MAX(IF($E$3:$E$20="Distance",$C$3:$C$20)))

HIIT Formula

=AND($E3="HIIT",$D3=MAX(IF($E$3:$E$20="HIIT",$D$3:$D$20)))