On Tue, 27 Apr 2004 02:46:24 -0500, Rico
wrote:

Thanks Bob, but I don't think it is that easy or, perhaps I haven't
explained it well enough.
Say row 3 has dates starting from column G and added to each day that
there is a competition.
Column A has competitors names and columns b,c,d,e,f have various other
stats such as year to date best score and number of times competitor
has competed.
F3 may have the function that looks for the best 3 of the last 5 scores
for that competitor. (That's the one below).
So it looks to the last score entered on the right and counts back 5,
then averages the best 3 of those.
I want it to ignore the entry just made but that entry must be there
for the rest of the calculations to work.
In other words, the 2nd last, 3rd last....6th last.
I have tried some functions, some of them fairly complex, but they seem
to fall over when there are blanks.
I am sure that this can be done, but perhaps I should try a redesign or
use Access?


If I understand what you are trying to do, the following **array-entered**
formula should do that:

=AVERAGE(LARGE(INDIRECT(ADDRESS(ROW(),
LARGE(ISNUMBER(G2:Z2)*
COLUMN(G2:Z2),{2,3,4,5,6}))),{1,2,3}))

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

The above formula looks for the rightmost 6 columns in which there is data;
then with regard to the leftmost five columns of that group of six, it averages
the three highest values. It assumes that if there is no data in a cell, that
cell will be blank.

As written, it requires that there be at least six entries, otherwise you will
obtain an error message. If this is an issue, the formula can be modified.


--ron