# Google Sheets conditional formatting for Max value that meets a second condition

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.

• can you share a copy of your sheet? – user0 May 30 '19 at 2:45
• Sure thing. Here's a link to an editable copy (not my main sheet since I'll delete this after) but it has the same formatting and data that I'm working with now: docs.google.com/spreadsheets/d/… – stats_guy May 30 '19 at 15:31

``=AND(E2>=MAX(D2:D)*2.1,O2=MAX(O2:O),O2<>"")`` ``=ARRAYFORMULA(MAX(IF(E\$2:E>=MAX(D\$2:D)*2.1, O\$2:O)))`` 