Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use LARGE and SMALL functions to display Top 8 and Bottom 8 values
LARGE Function returns the k-th largest value in a data set
LARGE Syntax =LARGE(array, k)
SMALL Function returns the k-th smallest value in a data set
SMALL Syntax =SMALL(array, k)
In this example we want to return the Top 8 and Bottom 8 Total Sales in column N
To return the Top 8 Total Sales, select P10:P17
Execute the formula in the selection: =LARGE(N4:N1042,{1;2;3;4;5;6;7;8})
Then press CTRL + SHIFT + ENTER
In the caption above, we have returned the Top 8 Total. To verify whether we are correct, we will sort the Total from Largest to Smallest.
To sort the Total, right-click any cell in the Total and select Sort from Largest to Smallest
We are correct.
To return the smallest Total Sales, select cells Q10:Q17 and execute the formula:
=SMALL(N4:N1042,{1;2;3;4;5;6;7;8})
Press CTRL + SHIFT + ENTER
We have returned the bottom 8 Total Sales. To verify, we will sort the Total from smallest to largest
Correct. That is how to use the two array functions