Hello Excellers and welcome to my regular blog spot of #formulafriday.  Today is really a double edition as I show you two great ways to find the minimum number in an Excel data set using two Excel functions.

The two Excel options we will use are MIN and SMALL.

Let’s take an example data set of student papers where we have a range of students and their corresponding marks for Math and Geography.  We need to identify the minimum value of each of the subjects.

Solution 1. Using the MIN Function.

First, let’s take a look at using the MIN function.  This is a simple one to use.  It returns the smallest number in a set of values.

The syntax is as follows

=MIN(number1, [number2], …)

The MIN function syntax has the following arguments:

• Number1, number2, …    Number1 is optional, subsequent numbers are optional. 1 to 255 numbers for which you want to find the minimum value.

Some points to note about the MIN function.

 

• The argument can be numbers, arrays of numbers, or references that contain numbers.  All are possible and you see it in my screenshot below.  They all give the same correct answer.

• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored
• If the arguments contain no numbers, MIN returns 0
• Arguments that are error values or text that cannot be translated into numbers cause errors.

 

Ok, so let’s go ahead build the formula and find the minimum score that was achieved by students in the Math assessment.  We have used an array as the argument that contains an array reference of B2: B20.

The lowest or minimum score is 14 out of 100.  Easy as that.

Solution 2. Using the SMALL Function.

Next, let’s take a look at using the SMALL function to find the smallest value or the lowest assessment score in the second data set that is the geography scores. SMALL returns the k-th smallest value in a data set.

The syntax is as follows

=SMALL(array, k)

The SMALL function syntax has the following arguments:

• Array    Required. An array or range of numerical data for which you want to determine the k-th smallest value.
• K    Required. The position (from the smallest) in the array or range of data to return.

Let’s go ahead and type our Excel Formula to use SMALL to find the lowest Geography score by a student.  The array we used is C2:C20 and we used the value 1 for the argument K.  We want the score that is 1 away from the smallest in the array, in this case, the smallest.  The result is an an assessment score of 10.

Some points to note about the SMALL function.

• If the array is empty, SMALL returns the #NUM! error value.
• If k ≤ 0 or if k exceeds the number of data points, SMALL returns the #NUM! error value.
• If n is the number of data points in the array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.

 

So there are two really quick and easy ways to find the smallest assessment score in our data set.  The SMALL function is slightly more flexible if you want to find out the 10th, or the 2nd score etc, but MIN is easier to remember in my opinion.  Which one would you use?.  Please share in the comments below or feel free to ask a question.

 

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.

How To Excel At Excel – Formula Friday Blog Posts.

 

 

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue,  where you can get help with Excel FAST. Why not check it out?.