Ron:
Really great formula, but I have two questions:
The portion of the formula: {=row(indirect("1:5"))} returns an array
{1;2;3;4;5}
The portion of the formula: {=indirect("1:5")} returns an array too
large to display.

1) What is the purpose of this formulation, i.e. row(indirect("1:5")?

I only ask this because the array {1;2;3;4;5} can be returned with
{=row(a1:a5)} directly and would seem to take less overhead. My concern
with overhead is that array formulas seem to randomly crash excel,
especially as the quantity of array formulas in a workbook increase.

2) What is in the array created with {indirect("1:5")}?

Ron Rosenfeld wrote:


i need a formula that wii sum just the last 5 scores, which in this case would be 243.

Thanks for any help!
Bob Leonard


The *array-entered* formula:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(
LARGE(ISNUMBER(score)*ROW(score),ROW(
INDIRECT("1:5"))),COLUMN(score)))))

To *array-enter* a formula, after typing or copying it in, hold down
<ctrl<shift while hitting <enter. XL will place braces {...} around the
formula.

In the above formula, "score" is a named range equal to B2:B9, but it can be an
single column range.

Also, if there are less than five numeric entries, the above formula will give
an error. If this is a problem, let me know.




--ron
555