I have a spreadsheet containing the names of cricket players who have scored more than 9999 runs in Test Cricket. I’m using this sheet as my example sheet to explain to you how to use the MAX function in Google Sheets.
In addition to the above, you can learn how to use the MAX function in filtered data in Google Sheets and also how to exclude hidden rows in MAX.
I’ve just imported the above-said sample data from a Wikipedia page with an IMPORTHTML formula. Below is that awesome Google Doc Spreadsheet Formula.
IMPORTHTML("https://en.wikipedia.org/wiki/List_of_players_who_have_scored_10,000_or_more_runs_in_Test_cricket","table",1)This imported data has so many columns. I have limited the number of columns by additionally using a Query as below. So you can use the below formula for this test.
=query(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_players_who_have_scored_10,000_or_more_runs_in_Test_cricket","table",1),"Select Col2, Col3, Col8")The below is that sample data which we were talking about.
Max Function Syntax
MAX(value1, [value2, …])
How to Use MAX Function in Google Sheets
In the above example, you can use a Max formula in Column C as below to return the highest run scorer in the list.
=MAX(C2:C)It’s that easy. You can use the arguments in the MAX function as comma-separated values as below, which would return the result 12 as it’s the highest value in the example below.
=MAX(10,5,12,4,1,5)Finally, let us see the use of the MAX function in a filtered data range in Google Sheets.
How to Use MAX Formula in Filtered Data in Google Sheets
When you want to find the maximum value in filtered data, you can use the MAX function number 104 in Subtotal as below.
=SUBTOTAL(104, C2:C14)This is applicable to hidden rows as well.
I suggest everyone should learn the Google Sheets SUBTOTAL function with function numbers. I’ve already detailed how to tame hidden rows or filtered data with the SUBTOTAL function. See the link to that tutorial.
Must Read: What is Function Numbers in Google Sheets
That’s all for now. Enjoy.
