This post will guide you how to use Excel MAX function with syntax and examples in Microsoft excel.
The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.
The MAX function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.
The MAX function is available in Excel 2016, excel 2013, excel 2010, excel 2007, excel 2003, excel XP, excel 2000, excel 2011 for Mac.
The syntax of the MAX function is as below:
Where the MAX function arguments are:
- num1 -This is a required argument. A number, or a reference to a number or a range that contains numeric values
- Num2,…Numn – This is an optional argument. A number, or a reference to a number or a range that contains numeric values
- The num1 arguments can be numbers, arrays, defined names or references that contain numeric values.
- IF the arguments contains non numeric value, it returns 0.
- The value returned by the MAX function is still a numeric value.
- If the argument is an array or reference, only numbers in that array are used. Empty string, logical values or text of numbers are ignored.
- You can provide up to 255 number arguments in excel 2007 or later. If you are using excel 2003, you can only provide up to 30 number arguments.
Excel MAX Function Examples
The below examples will show you how to use Excel MAX Function to get the largest value form the numbers in a range.
#1 To get the largest value from a range B1:B4, just using the following excel formula:
- Get nth Largest Value with Duplicates
If you want to get the largest unique value in a data set with duplicates, you can create a new excel array formula based on the MAX function and the IF function..…
- Finding the Max and Min value in an Alphanumeric Data
if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.….
- Limit Formula Result to Maximum or Minimum Value
You can use the MIN function or Max function to create a formula to limit a number within a minimum or maximum value. .….
- Converting Week Number to Date
You need to create a formula based on the MAX function, the DATE function and the WEEKDAY function to convert week number to a date in Excel.…
- Changing Negative Number to Zero in Excel
If you want to change all negative numbers to zero value from a cell in Excel, you can use a formula based on the MAX function or IF function.…
- Return Larger or Smaller of Two Values
To get the larger value from two values, you need to create a formula based on the MAX function, To get the smaller of two values, you can use the MIN function.…