On Tue, 27 Apr 2004 08:21:07 -0700, "rleonard" wrote:

I am trying to develop a formula that will sum last 5 values entered in a column of numbers.

The values are golf scores which i need to total so i can come up with an average based on last 5 games. problem is that
a person may not have a score for every date, for example:
Row a b
1 Date score
2 5/12/04 50
3 5/15/04 49
4 5/20/04 Did not play
5 5/29/04 50
6 6/01/04 45
7 6/05/04 50
8 6/10/04 Did not play
9 6/20/04 49

count(A2:A9)=6
Sum(A2:A9)=293

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

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

row(indirect("1:5")) returns the array you cite.

The purpose of using indirect is so the 1:5 won't change if the user inserts
or deletes a row within that range. (this would happen if you just used
row(a1:a5) or row($a$1:$a$5)

Indirect("1:5") returns a reference to rows 1 to 5, but within the context
of which you speak, the behavior is to display an array of the cells in
reference; rows 1 to 5 (5 x 256 = 1280 cells), which is too big to display.


--
Regards,
Tom Ogilvy


"JWolf" wrote in message
. ..
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

On Tue, 27 Apr 2004 14:01:50 -0500, JWolf wrote:

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")?

In the event either factor needs to be modified, it can be simple, with this
formulation, to perform a text concatenation. For example:

=row(indirect("1:"& min(count(score),5))) would return an array
1;2;...n where n is the lesser of the number of scores in count, or five.



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.

If there is no need for modifications, even simpler would be to just use the
array constant {1,2,3,4,5}.

So:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(
LARGE(ISNUMBER(score)*ROW(score),
{1,2,3,4,5}),COLUMN(score)))))

I have not had a problem with array formulas randomly crashing my Excel
installation.

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

An array formed by performing the INDIRECT function on every cell in the range
A1:IV5.


--ron

Thanks Ron and Tom.

On Tue, 27 Apr 2004 15:30:13 -0400, "Tom Ogilvy" wrote:

The purpose of using indirect is so the 1:5 won't change if the user inserts
or deletes a row within that range. (this would happen if you just used
row(a1:a5) or row($a$1:$a$5)


Of course! <sound of hand slapping forehead. And I've read that before; it
just didn't stick. Thanks for pointing it out.




--ron

Ron
Thanks for help,formula does the job of finding the last 5 golf score totals

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

How would I handle this problem, as it might occur

Also where can I get more info re *array-entered* formula's? This was first time in writing excell code that I had to use an *array-entered* formula

Thanks
Bob Leonar

On Tue, 27 Apr 2004 21:56:03 -0700, "rleonard" wrote:

Ron,
Thanks for help,formula does the job of finding the last 5 golf score totals.

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

How would I handle this problem, as it might occur?

It depends on what you want to do if the problem should occur?

=IF(COUNT(score)<5,"Less than five entries",SUM(TRANSPOSE(INDIRECT(ADDRESS(
LARGE(ISNUMBER(score)*ROW(score),{1,2,3,4,5}),COLU MN(score))))))



Also where can I get more info re *array-entered* formula's? This was first time in writing excell code that I had to use an *array-entered* formula!


I started with Excel HELP and the excel newsgroups. I'm not sure where else it
is written about.


--ron