The need to determine the maximum and the minimum values of a range of cells, is common to almost all types of excel projects.
But in statistics the MIN and MAX functions are not enough. The need to calculate, for example, the third largest or smallest value of a range of cells, is very common.
Luckily excel provides with functions to do just that.
Practice File:
Transcript
The LARGE function which returns the k’th largest value from an array of numeric values and the SMALL function which returns the k’th smallest value from an array of numeric values.
In the current worksheet we have already calculated the third largest and smallest values using the functions.
Their syntax is straightforward. We select the range of cells and then the index of the largest or smallest value we need.
We should make sure that all the numbers in the cell range are numeric values and not text representations of numbers. If this is the case the functions might produce the wrong result since they ignore text values.
They might even throw a NUM error if there are no numeric values or the actual numeric values are less than the index supplied.
In this case we have mistakenly set the value of cell B17 as a text. By fixing our error, we see that the 3rd largest value is now correctly calculated.
Let’s calculate the 10th largest and smallest values now in cells F10 and f11 respectively.
First we type the name of the function followed by the cell range, and then the number 10. That gives us the 10th largest value in our data set.
We follow the same procedure for the 10th smallest. The cell range and 10.
Pretty easy, isn’t it?