How to Use Excel MIN Function (7 Examples)
Prologue
In this article, you’ll learn about one of the most common functions of Excel – the MIN function. It is used to find the minimum or lowest value in a range or group of numbers.
You’ll find 7 examples on how to use the Excel MIN function.
Use Cases of Excel MIN Function
- Suppose you’re the manager of a small electronics shop. You have seven employees. You want to find the worst performing employee by finding the lowest sales generated. In this case, you can apply the MIN function to return this. You can provide additional support and training to that employee for improvement.
- If you’ve invested in several financial securities, then you can find the lowest return and discard investment from that.
- The minimum value can be an area of focus to further strengthen a business.
- You can also return the lowest temperature for a period using this function.
- The winner of a race can be found using this.
Syntax
The syntax of the MIN function is as follows. In Excel, the third bracket denotes optional arguments.
=MIN(Number1,[Number2], …)
Argument | Status | Comments |
Number1 | Required | Number1 can be a cell range, number, or cell reference. |
[Number2] | Optional | More numbers can be added to the function. |
Basic Formula
Type the following formula to find the smallest value in the range.
=MIN(B3:B8)
Formula Validation
- In ascending order, the numbers are 11, 13, 17, 19, 23, and 35.
- So, the smallest number in the range is 11.
Visualize in Google Sheets
You can check the code in the following Google Sheet file. The structure is the same as in the Excel version.
How to Use MIN Function in Excel: 7 Use Cases
Now, you’ll find 7 use cases for the MIN function in Excel.
Use Case 1: Finding Smallest Number
In this first example, we’ll use the basic formula to find the smallest price on the list.
- Firstly, use the formula in cell E10.
=MIN(E3:E8)
- Following that, it will determine the lowest price in that range, which is 0.
Use Case 2: Returning Earliest Date
You can find the earliest date using the MIN function in Excel. To do so-
- Type the formula in cell E10.
=MIN(B3:B8)
- Excel stores the date values as a serial number. That is why it can find the smallest date in a range.
Use Case 3: Getting Absolute Minimum Value
Suppose there is a data entry error and your dataset has a negative price. You can skip this sign using the ABS function. Moreover, you can incorporate this function into the MIN function to return only the absolute minimum number.
- The formula in cell E10 is-
=ABS(MIN(E3:E8))
Use Case 4: Lowest Value with Condition
You can skip zero values and return the next lowest value to zero. For that, you’ll need to incorporate the IF function with the MIN function.
- The formula for cell E10 is-
=MIN(IF(E3:E8>0,E3:E8))
- In the formula, E3:E8>0 checks if all the values are more than zero. The values that are less than zero, they are discarded. So, it’ll only keep the values that are more than 0.
Reminder: You can see a curly bracket around the function. This indicates the formula is an array formula. These formulas must be inserted by pressing Ctrl+Shift+Enter. However, this is not required for Excel in the Microsoft 365 version.
Use Case 5: Highlighting Smallest Number
You can use the Conditional Formatting feature to highlight a cell or multiple cells that satisfy a condition.
- Firstly, select the data range E3:E8 for this example.
- Then, select New Rule from the Conditional Formatting submenu.
- Then, a dialog box will appear.
- After that, use the following settings and type this formula in the formula box. Remember to use the cell referencing as it is. If you use $E$3, instead of $E3 then it will not work.
=$E3=MIN($E$3:$E$8)
- Finally, press OK.
- Then, it will color the smallest value in the selected range.
Use Case 6: Highlighting Smallest Number with Condition
Now, you’ll learn how to highlight the smallest number that is greater than zero using the Conditional Formatting feature.
- Select the cells and apply conditional formatting using the following settings. The formula is –
=$E3=MIN(IF($E$3:$E$8>0,$E$3:$E$8))
- After pressing OK, the smallest number without a zero will be highlighted.
Use Case 7: Using MIN Function in Excel VBA
In this last example, you’ll see how to use the VBA MIN function in Excel. Before doing anything, you’ll need to enable the Developer tab.
- To begin with, select the Excel tab.
- Then, click on Preferences.
- Afterward, select View.
- Then, the View window will appear.
- Finally, select the Developer tab from the In Ribbon, Show.
- Then, you’ll see the Developer tab in Excel. You can use Visual Basic to go to the VBA window. Or, you can press Alt+F11.
- You’ll type the VBA code inside a Module.
- Right Click on the canvas. Then, select Module from the Insert Page.
- Then, type the following code.
Sub MIN_VBA()
Sheets("VBA").Range("E10") = Application.WorksheetFunction.Min(Range("E3:E8"))
End Sub
- Afterward, use the Run button or press F5 to execute this VBA code.
- This is the output of the VBA code that applies the MIN function in the VBA sheet in cell E10. Then, the number1 range is E3:E8.
Fixing Common MIN Function Errors
In this section, you’ll find 5 common problems related to the MIN function in Excel and solutions to them.
- Solution to #REF! Error in Excel MIN Function
- Reason: If the MIN function is referenced to a cell that is deleted or invalid, then this error occurs.
- Solution: You should verify the cell reference and retype the formula.
- Solution to #NAME! Error
- Reason: If you misspelled any function name, then this error will show up. For example, you can see we’ve typed MAN instead of MIN, and it is throwing the #NAME! error.
- Solution: Correct the spelling of the function name.
- Solution to #NUM! Error
- Reason: If the function contains a value that is huge for Excel to calculate then it will show the #NUM! error. In our case, we’ve asked Excel to calculate the value of 500^500. So, it shows this error.
- Solution: Remove that large calculation from the MIN function.
- #VALUE! Error in Excel MIN Function
- Reason: If there is non-numeric data in the Excel MIN function, then it will show the #VALUE! error. You can see that because of the string “Apple”, the formula throws this error.
- Solution: The MIN function can’t handle any non-numeric values, so discard this from the formula or use the MINA function.
- #DIV/0! Error in MIN Function
- Reason: If the value is divided by 0, then this error occurs.
- Solution: You should remove the divided by zero calculation from the formula to fix this.
The following image shows the errors in action.
MIN vs MINA vs MINIFS Functions
The following table shows the key differences between the three functions in Excel:
Function | Objective | Syntax | Example |
MIN | Returns the minimum value from a range of numbers. | MIN(number1, [number2], [number3], …) | =MIN(3,”apple”,1) Output: #VALUE! error. |
MINA | Returns the minimum value from a range, including text and logical values. | MINA(value1, [value2], [value3], …) | =MINA(3,”apple”,7) Output: 3. |
MINIFS | Returns the minimum value from a range based on specified criteria. | MINIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …) | A3=3,A4=4,A5=6 B3=2,B4=7,B5=8 =MINIFS(A3:A5, B3:B5, “>3”) Output: 7. |
Facts About Excel MIN Function
- If there are no numbers in the range, then this will return 0.
- To include logical values, you should use the MINA function instead.
- You can ignore negative values by using =MIN(0,your_numbers). This will always return a positive minimum value.
- Maximum number of arguments: Depending on the version of Excel, the number of arguments can vary.
- 30 for Excel 2003 or earlier versions.
- 255 for Excel 2007 and later versions.
Epilogue
If you’re having any trouble understanding the discussion on this topic, feel free to reach out to us. You can also follow Excel Flash on Twitter and YouTube.
Next Read
You can read the following articles to further strengthen your knowledge of Excel.
- Excel SMALL Function
- Excel MAX Function
- LARGE Function