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:


But I don't know how to show only the max value of that subset. I tried an AND function:


...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:


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



=ARRAYFORMULA(MAX(IF(E$2:E>=MAX(D$2:D)*2.1, O$2:O)))


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.