Shakil Ahmed
In this article, we will learn how we can use the MAX function in Excel. We will also discuss various applications of the MAX function in different scenarios. Here, we will use the combination of the MAX function and the IF function to find maximum values based on conditions. We will also use the VBA macro feature of Excel to use the MAX function.
MAX functions can quickly find the maximum value from a set of data or a range. Whether you’re a beginner or an experienced Excel user, understanding and applying the MAX function can significantly enhance your data analysis capabilities in Excel.
Introduction to MAX Function in Excel
The MAX Function is categorized under Excel STATISTICAL functions. This function returns the largest value in a given list of arguments.
⦿ Syntax
Returns the largest value in a set of values. Ignores logical values and text.
MAX (number1, [number2], ...)⦿ Arguments
| Argument | Required/Optional | Value |
|---|---|---|
| number1 | Required | reference to a numeric value or range that contains numeric values. |
| [number2] | Optional | Additional numbers, cell references, or ranges of numeric values. |
You can provide up to a maximum of 255Â arguments.
MAX Function in Excel: 6 Common Uses
1. Finding the Maximum Value in a Row
Using the MAX function, you can find the maximum or highest value of a row.
We have brought a data table of students with their respective scores in three quizzes to show examples.
- Now, we are set to find the highest value from each row. Our formula for the first row will be
=MAX(C5:E5)Here, C5:E5 is the Cell Reference of the first row.
Our formula has provided the highest score for Angela on the quizzes.
- Drag the Fill Handle to get the maximum values for the rest of the rows
Read More: How to Use MIN Function in Excel
2. Finding the Maximum Value in a Column
Similar to the rows the MAX function can be used for the columns to find the largest value from the columns.
For example, we can find the highest score for each quiz from our previously used dataset.
- Scores for quizzes are stored in three columns. Our formula will be:
=MAX(C5:C10)
- We have found the highest score for Quiz 1. Similarly, we can find the largest for the rest of the columns by dragging the Fill Handle to the right.
Read More: How to Use LARGE Function in Excel
3. Get the Highest Value for Entire Data
If you need to find the maximum from the entire table or dataset, what will you do? No worries, simple answer: need to use the MAX function for the entire dataset.
To show you examples, we have introduced a dataset of several items with prices in different shops.
- We are aiming to find the largest value from the dataset, and our formula will be
=MAX(C5:E10)
We have found the maximum value in our table.
Similar Readings
4. Using MAX Function to Get Highest Value in Non-Adjacent Columns
You can find the maximum from the non-adjacent columns using the MAX function.
We are using the previous dataset for this example.
- Let us find the largest from Shop1Â and Shop3. The formula will be:
=MAX(C5:C10,E5:E10)
Here we have set the column references separated by a comma (,). The formula provided the maximum value for the two non-adjacent columns.
5. Utilizing Max Function to Find Recent Date
The MAX function can be used for finding the most recent date from the dataset. Here, we have a dataset of a few items from a couple of shops and delivery dates.
- Write the MAX formula for the date column.
=MAX(D5:D10)
Excel stores dates as sequential serial numbers. By default, 1 January 1900 is the 1st date, and its serial number is 1. The functions convert dates to their respective serial number before doing the operations.
The MAX function converts the dates into their respective serial numbers and finds the most recent date. Thus, the formula returned the most recent date.
6. Performing MAX Function with Criteria
You can find the maximum value by comparing any criteria. For example, we have brought a dataset of several sports items and their quantity.
- We can set our desired criteria; for example, here we have set criteria for Cricket Bats in cell G5. Â
- We need to set the condition using the IF function. Our formula will be:
=MAX(IF(C5:C13=G5,D5:D13))
The IF function checks whether the criteria have been matched or not, and if matched then it triggers the MAX operation.
Here we have found the highest quantity of Cricket Balls. If we change the criteria item, the result will be adjusted.
Note: It is an array formula; you need to press CTRL+ SHIFT + ENTER to execute the formula if you are using an older version of Excel.Â
How to Find Maximum Value with Excel VBA
Enabling Developer Tab in Excel
To use VBA, you need to enable the Developer tab first. If you already enabled the Developer option, you can skip this part.
- Right-click on any blank area of the Ribbon >> Select the Customize the Ribbon option.
- In the right side of the Excel Options dialogue box, check the box beside the Developer option >> click OK.
As a result, the Developer tab will be added to your ribbon.
Using VBA Code to Find Maximum Value
- First, go to the Developer tab >> click on the Visual Basic option.
- Go to the Insert tab in the Microsoft Visual Basic for Applications windows >> select the Module option from the drop-down.
- Write the following VBA code in the newly created Module >> click on the Save icon >> use the keyboard shortcut ALT + F11 to return back to the worksheet.
Sub maxvalue()
Dim maxVal As Variant
Set inputRng = Selection
maxVal = WorksheetFunction.Max(inputRng)
Range("C12").Value = maxVal
End Sub
- Select the range of data for finding the maximum value >> go to the Developer tab >> click on the Macros option.
- Select the maxvalue option in the Macro dialogue box >> click on Run.
Consequently, you have the maximum value in cell C12, as shown in the following image.
Quick Notes
- You can set the Cell References separated by a comma (,). We have inserted 3 cells in the function, and these are separated by a comma. And this worked fine.
- Alongside the Cell References, you can insert the number in the function. You will get the highest value among the inserted numbers.
- Numbers can be inserted along with the ranges. All you need to use comma (,) to separate range and number.
- Not only the cell references you can directly input the numbers into the function. You will get the largest value among them.
- Sometimes, your provided cells may contain text or Boolean values. The MAX function will ignore anything but the numbers and return the largest value among them.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Conclusion
That’s all for today. We have tried showing you how you can use the Excel MAX function. You can use the function to fetch the largest value from ranges or numbers. Hope you will find this helpful. Here, we have also provided an article to show you how to use MAX and MIN functions in the same formula.
Feel free to comment if anything seems difficult to understand. Let us know any of your MAX function-related scenarios where you are stuck; we are ready to help.
Related Articles
- How to Use AVERAGE Function in ExcelÂ
- How to Use AVERAGEIF Function in ExcelÂ
- How to Use Excel AVERAGEIFS Function
- How to Use CORREL Function in Excel
- How to Use Excel COUNT FunctionÂ
- How to Use COUNTA Function in ExcelÂ
- How to Use Excel COUNTBLANK Function
- How to Use COUNTIF Function in Excel
- How to Use COUNTIFS Function in ExcelÂ
- FORECAST Function in Excel
- How to Use Excel FREQUENCY Function
- How to Use Excel GROWTH Function
- How to Use INTERCEPT Function in Excel
- How to Use LINEST Function in ExcelÂ
- How to Use MEDIAN Function in Excel
- How to Use Excel NORMDIST Function
- How to Use NORMINV Function in Excel
- How to Use PERCENTILE Function in Excel
- How to Use QUARTILE Function in ExcelÂ
- How to Use Excel RANK Function
- How to Use Excel SLOPE Function
- How to Use SMALL Function in ExcelÂ
- How to Use TREND Function in Excel
- How to Use TTEST Function in Excel
- How to Use VAR Function in ExcelÂ
