Ever since I found the Excel newsgroups,
my knowledge of array formulas has increased in leaps and
bounds. These pages reflect my own take on the subject.
Basic Building Blocks
Create a list (an
array) of the numbers 1,2,...,n
|
|
|
Use
=ROW(INDIRECT("1:n")),
or
=ROW(OFFSET($A$1, 0, 0, n, 1)) |
|
Sort a range of
numbers in descending order
Alternative 1: |
|
|
Use
=LARGE(aRng,ROW(INDIRECT("1:"&ROWS(aRng))))
|
|
Explanation: |
|
Alternative 2:
|
Use
=LARGE(aRng,ROW(aRng)-ROW(OFFSET(aRng,0,0,1,1))+1)
|
|
Formulas to
| Create a matrix with ones
on the cross-diagonal (cells in a NxN matrix where
the row number + the column number equals
n+1). Used to reverse the row order or the
column order of any MxN matrix. |
Harlan
Grove's post on the method |
Reverse the row-order or
column-order of elements in a matrix
|
|
|
|