In this article, we will learn How to find the minimum and maximum values in Excel.
Scenario:
There can be many scenarios where an excel user needs to find the minimum or maximum value from the given values. For example finding the minimum and maximum sales value in the year. Or finding the minimum and maximum spend on a grocery store. Excel provides you with such mathematical functions to get the result easily and conveniently. Here first we learn MAX, MAXA, LARGE and MAXIFS function.
MAX formulas in Excel
MAX function
MAX function ignores text, boolean (TRUE/FALSE) or any other non numeric values. The function takes the arguments as arrays as explained below.
MAX formula Excel
| =MAX(array) |
MAXA function
MAXA function considers text as 0, boolean values TRUE as 1 and FALSE as 0 whereas numeric values are treated the same as MAX function. The MAXA function takes the arguments as arrays as explained below.
MAXA formula Excel
| =MAXA(array) |
LARGE function
LARGE function takes the array and a value n as arguments and the function returns the nth largest or nth highest or nth max value.
LARGE formula Excel
| =LARGE(array,n) |
array : list of numbers given as array references argument. Like values in A1 to A75 be referred as A1:A75
Example :
All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have some values and we need to get the highest of all the values. Use the MAX formula so as to get the max value
Use the MAX formula:
| =MAX(B2:B17) |
The maximum value among these negative values is -0.03. Now we try the same array with the MAXA function as explained below.
Use the MAXA formula:
| =MAXA(B2:B17) |
As you know the highest value is -0.03 but as the function considers boolean values TRUE as 1 and FALSE 0. So it returns 1 as highest (B6) in B6 cells. Let's learn how to use Large function to get the nth max value in excel
LARGE function Example
To get the nth highest value from the list. To get the highest value, use the value of n as 1. To get the 2nd highest value, use the value of n as 2 and to get the smallest value using Large function use the value of n equal to length of array.
Use the formula:
| =LARGE(B2:B17,1) # to get the max value |
Use the formula:
| =LARGE(B2:B17,2) # to get the 2nd max value |
As you can see the highest and the 2nd highest value evaluated using LARGE function in excel. Now let's get a hold on how to get the smallest values from the list.
MIN formulas in Excel
MIN function
MIN function ignores text, boolean (TRUE/FALSE) or any other non numeric values. The function takes the arguments as arrays as explained below.
MIN formula Excel
| =MIN(array) |
MINA function
MINA function considers text as 0, boolean values TRUE as 1 and FALSE as 0 whereas numeric values are treated the same as MIN function. The MINA function takes the arguments as arrays as explained below.
MINA formula Excel
| =MINA(array) |
SMALL function
SMALL function takes the array and a value n as arguments and the function returns the nth largest or nth highest or nth smallest or nth lowest value.
SMALL formula Excel
| =SMALL(array,n) |
array : list of numbers given as array references argument. Like values in A1 to A75 be referred as A1:A75
Example :
All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have some values and we need to get the highest of all the values. Use the MIN formula so as to get the minimum value
Use the MIN formula:
| =MIN(B2:B17) |
The minimum value among these positive values is 0.05. Now we try the same array with the MINA function as explained below.
Use the MINA formula:
| =MINA(B2:B17) |
As you know the lowest value is 0.05 but as the function considers boolean values TRUE as 1 and FALSE 0. So it returns 1 as lowest value in B10 cells. Let's learn how to use SMALL function to get the nth lowest value in excel
SMALL function Example
To get the nth lowest value from the list. To get the lowest value, use the value of n as 1. To get the 2nd lowest value, use the value of n as 2 and to get the largest value using a small function use the value of n equal to the length of the array.
Use the formula:
| =SMALL(B2:B17,1) # to get the min value |
Use the formula:
| =SMALL(B2:B17,2) # to get the 2nd min value |
As you can see the lowest and the 2nd lowest value evaluated using SMALL function in excel.
MAX/MIN with multiple criteria
Use the MAXIFS or MINIFS function in Excel to get the maximum value and minimum value satisfying the given multiple criteria. Learn how to use the MAXIFS function in Excel 365. Learn How to use the MINIFS function in Excel365. The above two functions are new and updated versions of Excel. find the Minimum value with IF criteria in Excel. Find maximum with if criteria in Excel for current and older version of excel.
Here are all the observational notes using the formula in Excel
Notes :
Hope this article about How to find the minimum and maximum values in Excel is explanatory. Find more articles on statistical values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.
Related Articles :
How to use the MAX function in Excel : returns the numerical largest value from the given array using the MAX function in Excel
How to use the MIN function in Excel : returns the numerical smallest value from the given array using the MIN function in Excel
How to use the LARGE function in Excel : returns the numerical largest nth value from the given array using the LARGE function in Excel.
Minimum value using IF function : Get the minimum value using the excel IF function and MIN function on array data.
Calculate max if condition match : Get the MAX value from the array having criteria.
How to Use Excel AGGREGATE Function : Use the AGGREGATE function to perform multiple operations with multiple criterias in excel.
COUNTIFS if either A or B or C Criteria : Count cells having multiple criteria match using the OR function.
How to use wildcards in excel : Count cells matching phrases using the wildcards in excel
Popular Articles :
50 Excel Shortcuts to Increase Your Productivity : Get faster at your tasks in Excel. These shortcuts will help you increase your work efficiency in Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
