Excel MAX Function (Table of Contents)
MAX in Excel
Max function in excel is the simplest form of mathematical function which just returns the maximum values out of the selected values or array. We can selected values one by one or in one go we can select a complete range of numbers if available in a sequence. It also neglects the blank cells, logical values and text if any of these comes in a selected range.
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.
4.8 (3,685 ratings)
View Course
Example #1
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 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 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
- 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 –
