For convenience, the range B5:B16 isnameddata.
There are several ways to approach this problem depending on what version of Excel is available.
Regardless of the approach, all solutions below depend on the LARGE function.
LARGE function
TheLARGE functionis designed to return thenthlargest value in a range.
For example:
Normally, LARGE returns just one value.
This depends on what Excel version is available.
One of these functions isSEQUENCE, which is designed to generate arrays on the fly.
To workaround this problem, you’re able to use the more advanced formula below.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
Related videos
The SEQUENCE function
How to get nth values with SMALL and LARGE
