In excel I want to get the last 5 non zero values from a row (or column) and
get an average.

On Wed, 24 Aug 2005 11:27:01 -0700, "Larry L" <Larry
wrote:

In excel I want to get the last 5 non zero values from a row (or column) and
get an average.

For Rows:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:10)*(COLU MN(1:1)),{1,2,3,4,5})-1)))

For Columns:

=AVERAGE(TRANSPOSE(OFFSET(A1,LARGE((A1:A655350)*( ROW(A1:A65535)),{1,2,3,4,5})-1,0)))


These are array formulas so after typing them in, hold down <ctrl<shift while
hitting <enter. Excel will place braces {...} around the formula.

Since these are array formulas, they cannot reference an entire column.


--ron

I could not get the formulae to work. I may have of not put the row
information in correctly. My data is in rows cells C535 to T535 for the first
person. There are 16 rows of data.

Other info ...
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).





"Ron Rosenfeld" wrote:

On Wed, 24 Aug 2005 11:27:01 -0700, "Larry L" <Larry
wrote:

In excel I want to get the last 5 non zero values from a row (or column) and
get an average.

For Rows:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:10)*(COLU MN(1:1)),{1,2,3,4,5})-1)))

For Columns:

=AVERAGE(TRANSPOSE(OFFSET(A1,LARGE((A1:A655350)*( ROW(A1:A65535)),{1,2,3,4,5})-1,0)))


These are array formulas so after typing them in, hold down <ctrl<shift while
hitting <enter. Excel will place braces {...} around the formula.

Since these are array formulas, they cannot reference an entire column.


--ron