BTW it is the control-shift-enter that is effectively the loop, which is
controlled by the ROW(INDIRECT... values.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fedude" wrote in message
...
Bob,

Yikes! This formula is going to take me some time to understand, but at
first glance it looks very interesting....

I don't understand how this will give me the average of 10 numbers. Won't
this give me the average of the tenth smallest number in the range? Is
the
COUNT function like a loop?

Will this work if there are less than ten numbers in the list as well?

I probably don't understand how the INDIRECT function works. I'm going to
look it up. Can you help me decypher this?

"Bob Phillips" wrote:

or even like this


=FLOOR(AVERAGEA(SMALL(H2:H21,ROW(INDIRECT("1:"&MIN (COUNT(H2:H21),10)))))*0.9
6,0.1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fedude" wrote in message
...
That's what I was already doing, but I find myself editing a lot of
cell
formulas. I'm trying to calculate golf handicaps and the rule is to
take
the
lowest 10 of the last 20 scores to figure it out, so I need a formula
which
evaluates 1-20 rows depending upon how many scores I have and then
once I
reach 20 I need a rolling range (i.e. not locked). I don't want to
use a
macro to do this.

Here is the formula I've been using (This is an example of the 4th
score
in
the list):


=FLOOR(AVERAGEA(SMALL(H13:H21,1),SMALL(H13:H21,2), SMALL(H13:H21,3))*0.96,0.1
)

"Bob Phillips" wrote:

Lock the start cell with absolutes

$A$1:A3

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"fedude" wrote in message
...
Is there a way to define a range based upon the current cell
location?
For
instance When I'm calculating a formula in C3 I want to use the
range
A1:A3,
but when I move down to C4, I want the range to be A1:A4.

The top of the range will remain constant, but the bottom of the
range
will
move based upon the row I'm currently in.