The use of INDIRECT in the example generates a reference to entire rows 1:10 ... having the reference in text as "1:10", and passing it through INDIRECT is desired in this case, in order to prevent any accidental alteration of the range used. The range contents are not important, as we are only using the reference to generate the numbers 1,2,3,4,5,6,7,8,9,10 in order to select the 10 largest numbers from a list.
The formula passes the list of numbers to the LARGE function (note that this is the reason that this is being generated as an Array Formula, entered using Ctrl+Shift+Enter, in order that all cases will be calculated at the same time ... LARGE will usually take a single number).
The LARGE function will generate a list of the 10 largest numbers from the list, and pass that to the AVERAGE function.
King Of Excel
Copyright © 2021 Glenn Bamford - All Rights Reserved.
Powered by GoDaddy Website Builder