The formulae gave me a wrong calculation. The specific data I had is below:

45 42 45 46 40 41 44 44 44 41 38 39 43

The last 5 scores totalled 205 = 41.00 average

The formulae calculated 42.25

The fomulae I used =
{=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535< "",COLUMN(C535)-1),5)))}

The input data started in cell T535 and ended in cellT535 (18 weeks of
data). What did I do incorrectly?








"Biff" wrote:

Hi!

That formula does work.

This one is a little less complicated.....

Assume your scores are in row 1 and you add a new score weekly (or not, if
you miss that week). Your golf season is 25 weeks long. Cell A1 is for the
players name. The weekly scores start in cell B1 and the last cell for the
25th week is cell Z1.

To get the average of the last 5 scores enter this formula using the key
combo of CTRL,SHIFT,ENTER:

=AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<"",COLUMN( B1:Z1)-1),5)))

Biff

"Larry L" wrote in message
...
I need help with developing an average function for a golf league. We use
an
avarage of the last 5 scores posted to develop the person's handicap. Each
week a new score is added to the data and a new average calculated with
the
5th oldest score being dropped and newest score being included in the
average. The data is kept on the worksheet in rows. Because a golfer may
miss
a week, some rows may have blanks that should not be considered.

For example a person's scores may look like this for the season:
45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and 49
is
the latest score). I want the formulae to consider the 5 non zero scores
from
the oldest - 49 (right to left).

I was given this formulae:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:10)*(COLU MN(1:1)),{1,2,3,4,5})-1)))
+ control/shift & enter

However I could not get it to work.