This tutorial explains how to use the MIN function in Microsoft Excel 2007 - 2019, find the lowest value by a condition and highlight the bottom number in your range.
Today you will learn how to use basic but quite important MIN function in Excel. You'll see the ways to get the lowest number excluding zeroes, the absolute minimum and the smallest value based on some criteria.
Furthermore, I'll show you the steps to highlight the least cell and tell you what to do if your MIN functions return an error instead of the result.
Well, let's get started. :)
MIN function - syntax and usage examples in Excel
The MIN function checks your data range and returns the smallest value in the set. Its syntax is the following:
number1, [number2], … is the series of values from where you want to get a minimum. Number1 is required while [number2] and the following are optional.
There are up to 255 arguments allowed in one function. The arguments can be numbers, cells, arrays of references, and ranges. However, arguments like logical values, text, empty cells are ignored.
Examples of using MIN formula
MIN is one of the easiest functions to apply. Let me prove it to you:
Example 1. Locating the smallest value
Let's say you have some fruits in stock. Your task is to check if you are running out of any. There are several ways to go:
Case 1: Enter each and every numeral from the Qty in stock column:
=MIN(366, 476, 398, 982, 354, 534, 408)
Case 2: Reference the cells from the Qty column one by one:
Case 3: Or simply reference the whole range:
Case 4: Alternatively, you can create a named range and use it instead to avoid any direct references:
Example 2. Looking for the earliest date
Imagine that you have a few deliveries planned and would like to be ready for the most upcoming one. How to discover the earliest date in Excel? Easy! Use MIN following the same logic from example 1:
Apply the formula and select the dates either by referencing the range directly:
Or the named range:
Example 3. Retrieving an absolute minimum
Supposing that you have a data range and need to detect not simply the lowest but the absolute minimum there. The MIN alone won't be able to handle that since it will just return the smallest number. Here you need a helper function that can convert all negative numbers to positive ones.
Is there a ready-made solution here? The question was rhetorical, there is a solution for any task in Excel. If you have any doubts, just look through our blog. :)
But let's get back to our task. The ready-made solution to this particular case is called ABS function which returns the absolute value of numbers you specify. Thus, the combination of MIN and ABS functions will do the trick. Just enter the following formula in any blank cell:
Note! Did you notice the curly brackets around the function? It's a sign that this is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. You can read more about array formulas and their usage here.
How to find the lowest value ignoring zeros
Does it seem like you know everything about locating the minimum? Don't jump to conclusions, there is plenty left to learn. For example, how would you determine the least non-zero value? Any ideas? Don't cheat and google it, just keep reading ;)
The thing is, MIN works with not only positive and negative numbers but also with zeroes. If you don't want zeros to be that minimum, you need some help from the IF function. Once you add the limitation that your range should be more than zero, the expected result won't keep you waiting. Here is a sample of the formula that returns the bottom value based on some condition:
You must have noticed the curly brackets around the array formula. Just remember that you don't enter them manually. They appear one you hit Ctrl + Shift + Enter on your keyboard.
Finding the minimum based on a condition
Let's suppose you need to locate the least sales total of a specific fruit in a list. In other words, your task is to determine a minimum based on some criteria. In Excel, conditions usually lead to using the IF function. All you need to do is make a perfect combination of MIN and IF to solve this task:
Press Ctrl + Shift + Enter so that array function to work and enjoy.
Looks pretty easy, right? And how will you spot the littlest figure based on 2 or more conditions? How to determine the minimum by multiple criteria? Maybe there is an easier formula available? Please check this article to find it out. ;)
Highlight the smallest number in Excel
And what if you don't need to return the littlest numeral is, but want to find it in your table? The easiest way to guide your eye to this cell is to highlight it. And the most straightforward way to do that is to apply conditional formatting. It's even simpler than writing functions:
- Create a new conditional formatting rule by clicking Conditional formatting -> New Rule
- Once the New Formatting Rule dialog opens, select the “Format only top or bottom ranked values” rule type
- Since the task is to highlight the one and only lowest digit, choose the Bottom option from the drop-down list and set 1 as a quantity of cells to highlight.
But what to do if there is a zero in your table again? How to ignore zeroes when highlighting the least number? No worries, there is a trick here too:
- Make a new conditional formatting rule selecting the “Use formula to determine which cells to format” option
- Enter the following formula in the Format values where this formula is true field:
Where B2 is the first cell of the range to highlight the lowest number in
- Set the color (Edit Formatting rule -> Format… -> Fill) and hit OK.
- Enjoy :)
Tip. To find the Nth lowest number with criteria, use the SMALL IF formula.
Why doesn't my MIN function work?
In the ideal world, all the formulas would work like a charm and return the correct results once you hit Enter. But in the world we live in it happens that functions return an error instead of the result we need. No worries, the error itself always hints at its possible cause. You just need to have a closer look at your functions.
Fixing the #VALUE error in MIN
Generally, you get the #VALUE! error message when at least one of the arguments used in a formula is incorrect. Regarding MIN, it may occur when one of them is corrupted e.g. something is wrong with the data the formula refers to.
For instance, #VALUE! may appear if one of its arguments is a cell with an error or there is a typo in the its reference.
What can cause the #NUM! error?
Excel shows a #NUM error when it is impossible to calculate your formula. It usually takes place when the numeric value is too big or small to be displayed. The allowed numbers are those between -2.2251E-308 and 2.2251E-308. If one of your arguments is outside this scope, you'll see #NUM! error.
I'm getting #DIV/0! error, what to do?
Fixing #DIV/0! is easy. Don't divide by zero! :) No kidding, this is the one and only solution to that issue. Check if there is a cell with #DIV/0! in your data range, fix it and the formula will return the result right away.
Looking for the littlest numeral but getting the #NAME? error?
The #NAME error means that Excel can't recognize the formula or its arguments. The most possible reason of such a result is a typo. You may either misspell the function or put incorrect arguments. Moreover, text representations of numbers will cause that error too.
The other possible cause of that problem lays in a named range. So, if you reference an unexisting range or there is a typo in it, you'll see #NAME? in the place you're expecting your result to appear.
These are the ways to find a minimum using the Excel MIN function. For you, I covered different approaches to discover the lowest value and to locate the absolute minimum. You may consider this your cheat sheet and use it whenever you need to get the smallest number based on a condition and to prevent and fix the possible errors.
That's it for today. Thank you for reading this tutorial! Please feel free to share your thoughts and questions in the comments section, I'll be glad to get feedback from you! :)