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