The MAX and MIN functions in Excel - not surprisingly - help us find the minimum and maximum values in a group of numbers or cells. While MAX and MIN are pretty simple on the surface, knowing all the details of how they work can add some serious firepower to your Excel arsenal.
The MIN function in Excel returns the smallest value from a group of numbers. MIN is written as follows:
=MIN(number_1, [number_2], [number_3], ...)The MIN function requires at least one argument (values separated by commas) but can take up to 255 arguments. Arguments can be numbers, cell references, or ranges of cells. MIN will ignore text, True/False, and empty cells. If none of the provided cells contain numerical values, MIN will simply return 0.
The MAX function in Excel returns the largest value from a group of numbers. MAX is written as follows:
= MAX(number_1, [number_2], [number_3], ...)The MAX function requires at least one argument (values separated by commas) but can take up to 255 arguments. Arguments can be numbers, cell references, or ranges of cells. MAX will ignore text, True/False, and empty cells. If none of the provided cells contain numerical values, MAX will simply return 0.
As you can tell, the MAX and MIN function in Excel only find the largest and smallest values, respectively. If you want to find the 2nd smallest number, or the 3rd largest number, then you can check out Excel's LARGE and SMALL functions.
The MIN Function in Excel can take several different types of arguments, such as numbers, cells, and ranges. Take the following examples based on the table below:
| A | B | |
|---|---|---|
| 1 | 5 | |
| 2 | 6 | |
| 3 | 1 | |
| 4 | Excel! |
=MIN(5, 6, 8, 10, 1)This function would obviously return 1.
The MIN function also takes cell references and ranges as arguments. The following two functions would both return 1:
=MIN(A1:A4)=MIN(A1, A2, A3, A4)Because MIN ignores text values, A4 would simply be ignored in both cases and the function would return the lowest numerical value, which is 1.
If the provided arguments do not contain any numerical values, MIN will simply return 0. For example, take the following function based on the table above:
=MIN(A4:B4)This function would return 0 because A4 is a text value and B4 is an empty cell.
The MAX Function, like the MIN function, can take several different types of arguments, such as numbers, cells, and cell ranges. Take the following examples based on the table below:
| A | B | |
|---|---|---|
| 1 | 5 | |
| 2 | 6 | |
| 3 | 1 | |
| 4 | Excel! |
= MAX(5, 6, 8, 10, 1)This function would obviously return 10.
The MAX function also takes cell references and ranges as arguments. The following two functions would both return 6:
=MAX(A1:A4)=MAX(A1, A2, A3, A4)Because MAX ignores text values, A4 would simply be ignored in both cases and the function would return the highest numerical value, which is 6.
If the provided arguments do not contain any numerical values, MAX will simply return 0. For example, take the following function based on the table above:
=MAX(A4:B4)This function would return 0 because A4 is a text value and B4 is an empty cell.
Say a group of friends went golfing, and because they are nerds they recorded their scores in a table in Excel.
| A | B | |
|---|---|---|
| 1 | Golfer | Score |
| 2 | Player 1 | 64 |
| 3 | Player 2 | 63 |
| 4 | Player 3 | 58 |
| 5 | Player 4 | 60 |
The friends want to find out what the best score was, so they write a formula to find the minimum of the whole range:
=MIN(B2:B5)Next, they want to find out what the worst score was, so they write a formula to find the maximum value of the whole range:
=MAX(B2:B5)This is great, but the friends want to play golf every weekend, and other friends will occasionally join. If more players join, then their functions with the range B2:B5 will no longer be satisfactory, as it may not contain every player's score.
To fix this, the friends can re-write their MAX and MIN functions to look at the entire B column. MAX and MIN will ignore cell B1 (because it is text) as well as any empty cells, so each player's score will be analyzed, no matter how many players there are.
=MIN(B:B)=MAX(B:B)