So you’ve been given a long list of numbers and asked to find the largest, smallest, second largest and second smallest values. You could sort the list into ascending or descending order but you’ve been told that the order must remain as it is.
Use the following functions to get the information:
HIGHEST: =MAX(A1:A23)
LOWEST: =MIN(A1:A23)
2nd HIGHEST: =LARGE(A1:A23,2)
2nd LOWEST: =SMALL(A1:A23,2)
The MAX function returns the highest value from the specified range
The MIN function returns the lowest value from the specified range
The LARGE function returns the Xth highest value from the specified range where X is a number, e.g. 2 means retrieve the 2nd highest value, 3 means retrieve the 3rd highest value etc
The SMALL function returns the Xth lowest value from the specified range where X is a number, e.g. 2 means retrieve the 2nd lowest value, 3 means retrieve the 3rd lowest value etc
Hi Mike,
First of all, I think that it’s easier and more efficient to work with defined names instead of with ranges.
So, I took your list of numbers (A1:A23) and gave it a name: “MyList”.
Now, here are a few tricks:
Trick No.1:
Using the LARGE function to retrieve the smallest number in a range of cells:
=LARGE(MyList,COUNT(MyList))
In our example, based on the values in “MyList” (cells A1:A23), the result was: 1259
Trick No.2:
Using the SMALL function to retrieve the largest number in a range of cells:
=SMALL(MyList,COUNT(MyList))
In our example, based on the values in “MyList” (cells A1:A23), the result was: 3009
The Count(Mylist) part of the formula tells us how many numbers there are in the list (=23). So, the LARGE function returns the least value (the number in the 23rd place).
And of course, the same number (23) applied to the SMALL function, tells us which is the largest number in the list (the number in the 23rd place), but this time the “least small” value, which is of course the largest.
One can, of course, find the value at the X-th place, by modifying the COUNT part of the formula.
For example, if one wishes to find out the second largest number:
=SMALL(MyList,COUNT(MyList)-1)
And the result would be: 2892.
The same applies if you want to find the second smallest number:
=LARGE(MyList,COUNT(MyList)-1)
And the result would be: 1330.
Trick No.3:
No let’s take this idea one step further.
Instead of using the COUNT function in order to find out how many values there are in the list, let’s use the RANK function:
=RANK(MIN(MyList),MyList,0)
Or:
=RANK(MAX(MyList),MyList,1)
Both these functions will return: 23 – the number of values in “MyList”.
Now, if we’ll “plug” them in the original formula (Trick No.1) instead of the original COUNT portion, we’ll get the same result, i.e. the smallest number in the range:
=LARGE(MyList,RANK(MIN(MyList),MyList,0))
Or
=LARGE(MyList,RANK(MAX(MyList),MyList,1))
Another 2 versions: using the SMALL instead of the LARGE function,
=SMALL(MyList,RANK(MIN(MyList),MyList,1))
Or:
=SMALL(MyList,RANK(MAX(MyList),MyList,0))
In all 4 versions, the result is the same: the smallest number (=1259).
Trick No.4:
Same idea as in Trick No.3
=LARGE(MyList,RANK(MIN(MyList),MyList,1))
=LARGE(MyList,RANK(MAX(MyList),MyList,0))
=SMALL(MyList,RANK(MAX(MyList),MyList,1))
=SMALL(MyList,RANK(MIN(MyList),MyList,0))
And the result in all 4 versions would be: 3009.
P.S.
It goes without saying that in tricks #3 and #4 we can also find the x-th number (2nd, 3rd….) as we did in tricks #1 and #2.
Wishing you a merry Christmas and a happy new year .
Meni Porat
Hi Meni
Thank you for those examples. They certainly add value to my original tutorial. The great thing with Excel is that there are often several ways to get the same result. Whether you want to keep it simple with MIN and MAX or whether you want to “get geeky” with a complex nested function like yours.
Wishing you also a merry Christmas and Happy New Year
Mike