I'm working with a baseball statistics spreadsheet in Google Sheets, and I want to highlight the maximum value in my batting average column, but only if that value is for a player who has satisfied the proper criteria to qualify as a leader — their plate appearances must be greater than or equal to the maximum number of games played in the Games Played Column multiplied by 2.1.
In my sheet, Column B=Games Played, Column E=Plate Appearances, Column O=Batting Average.
I think I've got the logic to isolate the games played and plate appearance condition:
$E:$E>=max(D2:D1000)*2.1
But I don't know how to show only the max value of that subset. I tried an AND function:
=AND($E:$E>=max(D2:D1000)*2.1)+($O:$O=max(O:O))
...but that gets everyone who meets that condition and the current max in that column.
I then tried to write logic to show the max IF:
=max(O2:O100)=IF($E:$E>=max(D2:D1000)*2.1)
...but I think this probably will make me look silly because I'm only just starting to learn how to do this! :)
Would be so appreciative of your help.