Search notes:

Excel array formulas: row and large

This example uses an array formula to sort a range of random numbers.

The random numbers are filled into the first column. The array formula is inserted into the second column. It uses the row() function to create the numbers of 1 to 10 and combines it with large() to get the nth largest number of the random numbers.

option explicit

sub main() ' {
 '
 '  Clear active sheet's data:
 '
    activeSheet.usedRange.clearContents

    rnd(-280870)
    randomize(1)
    dim r as long
    for r = 1 to 10
        cells(r, 1) = cLng(rnd(r) * 900 + 100)
    next r

    range(cells(1,2), cells(10,2)).formulaArray = "=large(r1c1:r10c1, row(r1:r10))"

end sub ' }

Github repository about-Excel, path: /formulas/array/row/large.bas

When run, the example produces:

Excel array formulas: row and large

See also