Many of us using Microsoft Excel for years already know the functions MAX() and MIN(). Did you know that Excel allows you to calculate the kth largest value **and the **kth smallest value, where k is some integer? You can calculate this by using the function LARGE(array, k) where array is a range or a list of values and k is the position within array that you want to return. When k equals 1 LARGE returns the same value as MAX()
Similarly, the function SMALL() takes in an array or a range of values and a value for the position within the array.
Example of using these functions LARGE and SMALL on a set of data
If you wanted to sum up the top three values in a data set or take the average of the top 10 values, you can combine the LARGE function and the appropriate arithmetic function such as SUM() in a formula. We could, likewise combine an arithmetic function such as AVERAGE and SMALL
Example of performing calculations on the top k values and the bottom k values. In this illustration, k = 3
XLOOKUP is a powerful function that can do the work of both VLOOKUP and HLOOKUP, and does not have the limitations of these functions.