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.

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.

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.

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.

Blimey, not happy with a solution, you want an explanation as well.

You should try it, you'll get a direct answer that way. input one number,
two, ten, eleven and see what happens.

The thing is that it sets up a little array of 1,...,n where n is a count of
the numbers in H2:H21, ROW(INDIRECT("1:"&MIN(COUNT(H2:H21),10))), but it
restricts that count to 10 with the MIN(...,10). This array is then passed
to SMALL(H2:H21 to get the 10 smallest numbers, or 4 smallest if there is
only 4 numbers in the range. So, no matter how many items you have it will
pick up the (upto) ten smallest and average them.

--
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.

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.

Thank you. I tried it and it works. Very impressive.....

PS: Thanks for the explaination as well. It took me a while but I
understand it now....

"Bob Phillips" wrote:

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.