Excel MAX Function (Table of Contents)
MAX in Excel
- MAX Function in Excel is an inbuilt or pre-built function in excel that is categorized under Statistical functions. It is used as a worksheet & VBA function in excel.
- To find the largest number in a range of cells is very difficult, Sorting on values from high to low to find out the highest sales is not viable if there is a huge number of datasets. In this scenario, Max function in Excel will help out to quickly find the highest numeric value in a group of selected cells or range.
- Most commonly used in a statistical & financial analysis, where it helps out to calculate the maximum or highest score in the academics, the fastest time in race, most expensive product, maximum orders for a company, highest share price of specific company, highest-recorded temperature in a particular location & best-performing sales executive, etc.
- MAX Function in excel is flexible, easy to use & very simple function with few arguments.
- MAX Function in excel is also used or integrated with other excel functions (E.g. MAX along with IF function with single or multiple criteria) to get the desired output.
Definition
MAX Function in Excel returns the largest or maximum number in a given list of arguments.
MAX Formula in Excel
Below is the MAX Formula in Excel:
The MAX Formula in Excel has the parameter as below :
- number1, [number2]: It is the number or cell reference or range arguments which can be one or more numeric values from which you want to find the maximum value in Excel.
number1 is a compulsory parameter or argument, whereas remaining subsequent numbers are an optional argument. From excel 2007 & later version, it can take up to 255 arguments. The Formula for ‘MAX’ function is exactly the similar or the same as that for ‘MIN’ function. We can mix literal values along with cell references to find out the maximum value.
e.g. =MAX(D8:D19,100) here literal value in the function is 100.
How to Use MAX Function in Excel?
MAX Function in Excel is very simple and easy to use. Let understand the working of MAX Function in Excel by some MAX Formula in Excel example.
MAX Function can be used as a worksheet function and as VBA function.
Example #1 – To Find out the Highest sales in Supermarket
In the below-mentioned table, it contains a category of item in column C (C8 to C19) & sale value in column D (D8 to D19). I need to find out which category has maximum sales in the supermarket. MAX Function in Excel is used on the range of values representing sales by category to extract the highest value.
Let’s apply Max Function in cell “D20”. Select the cell “D20” where Max Function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MAX” in the search for a function box, MAX Function will appear in select a function box Double click on MAX Function.
A dialog box appears where arguments for MAX Function needs to be filled or entered i.e. =MAX(number1, [number2], … )
=MAX(D8: D19) Here the sales data is present in the range (D8: D19). To enter Number 1 argument, click inside cell D8 and you’ll see the cell selected, then Select the cells till D19. So that column range will get selected i.e. D8: D19. Click ok, after entering the number1 argument.
=MAX(D8: D19) i.e. returns the maximum sales value 6679 as the result.
MAX Function has returned maximum available sales value within defined range i.e. 6679 under the utensil’s category.
Example #2 – To Find out the Highest Score
To calculate the maximum or highest score in the academics for a specific subject (Mathematics).
In the below-mentioned table, it contains the Name of the student in the column H (H2 to H18) & score of each student (I2 to I18). I need to find out which student has scored a maximum or highest score in mathematics subject. Here, MAX Function in Excel is used on the range of values representing score to extract the highest value.
Let’s apply Max Function in cell “I19”. Select the cell “I19” where Max Function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MAX” in the search for a function box, MAX Function will appear in select a function box. Double click on MAX Function.
A dialog box appears where arguments for MAX Function needs to be filled or entered i.e. =MAX(number1, [number2], … )
=MAX(I2: I18) Here the score data is present in the range (I2: I18). To enter Number 1 argument, click inside cell I2 and you’ll see the cell selected, then Select the cells till I18. So that column range will get selected i.e.I2: I18. Click ok, after entering the number1 argument.
=MAX(I2: I18) i.e. returns the maximum score value 97 as the result.
Here, MAX Function has returned maximum score value within defined range i.e. 97 under the score category.
Example #3 – To Find out Highest Salary for Employee
To calculate the maximum or highest Salary for an employee in the company.
In the below-mentioned table, it contains Name of the employee in column C (C27 to C43) & Salary of an employee (D27 to D43). I need to find out which employee is getting the highest or maximum salary in the company. Here, MAX Function in Excel is used on the range of values representing the salary of an employee to extract the highest or maximum value.
Let’s apply Max Function in cell “D44”. Select the cell “D44” where Max Function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “MAX” in the search for a function box, MAX Function will appear in select a function box. Double click on MAX Function.
A dialog box appears where arguments for MAX Function needs to be filled or entered i.e. =MAX(number1, [number2], … )
=MAX(D27: D43) Here the salary data is present in the range (D27: D43). To enter Number 1 argument, click inside cell D27 and you’ll see the cell selected, then Select the cells till D43. So that column range will get selected i.e. D27: D43. Click ok, after entering the number1 argument.
=MAX(D27: D43) i.e. returns the maximum salary of employee i.e. 95,916 as the result.
Here, MAX Function has returned the maximum or highest salary of an employee within a defined range i.e. 95,916.
Things to remember about the MAX Function in Excel
- Argument type can be cell reference, range reference, column reference row reference, numbers, multiple columns/rows & cell and range names.
- Max Function in Excel ignores numbers entered as text values, empty cells & the logical values TRUE and FALSE & it should not have any error value (#N/A, VALUE, DIV).
- If there are no numbers in specified range or arguments it will return 0.
- If there is an error value in the argument or text, it will return #N/A.
- MAX Function in Excel is the same as LARGE function, both return the biggest number.
- #VALUE! error: It Occurs if any non-numeric values that are supplied directly to the Max Function.
Recommended Articles
This has been a guide to MAX in Excel. Here we discuss the MAX Formula in Excel and how to use MAX Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
