updating cell range depending on input value

I'm creating a spreadsheet to process golf scores and calculate handicaps.

I've identified the best 10 (AD4:AD13)scores from the past 20 (AB4:AB23).
What I now need to do is cater for the next score input which will be in AB24
and move the range to account for the new score (AB5:AB24). I also need to
tell s/s not to move the range if input value is zero (column 'U'). That's
just a start but would appreciate any help offered. Thanks.

Not sure I understand, but try base any calculations of a dynamic named
range.
http://www.ozgrid.com/Excel/DynamicRanges.htm



--
Regards
Dave Hawley
www.ozgrid.com
"handicapper" wrote in message
...
I'm creating a spreadsheet to process golf scores and calculate handicaps.

I've identified the best 10 (AD4:AD13)scores from the past 20 (AB4:AB23).
What I now need to do is cater for the next score input which will be in
AB24
and move the range to account for the new score (AB5:AB24). I also need
to
tell s/s not to move the range if input value is zero (column 'U').
That's
just a start but would appreciate any help offered. Thanks.

Hi Dave,

Thanks for your reply. I'll expand on what I'm trying to do. A handicap is
calculated by averaging the best 10 scores from the last 20 rounds played.
All this is fine for the initial calculation but when the player plays his
21st. round the cell range of his last 20 scores has to be updated (ie 2-21
instead of 1-20).

So I'd like to write a formula which moves the cell range accordingly when a
new score is input. But does'nt move it if zero is returned on the 21st
playing day. I'm not sure due to my ignorance whether your initial reply
covers this (my fault if it does'nt).

Handicapper

"ozgrid.com" wrote:

Not sure I understand, but try base any calculations of a dynamic named
range.
http://www.ozgrid.com/Excel/DynamicRanges.htm



--
Regards
Dave Hawley
www.ozgrid.com
"handicapper" wrote in message
...
I'm creating a spreadsheet to process golf scores and calculate handicaps.

I've identified the best 10 (AD4:AD13)scores from the past 20 (AB4:AB23).
What I now need to do is cater for the next score input which will be in
AB24
and move the range to account for the new score (AB5:AB24). I also need
to
tell s/s not to move the range if input value is zero (column 'U').
That's
just a start but would appreciate any help offered. Thanks.

I'll try to approach this from a different angle. This formula moves the
range down 1 cell if the entry in "U25" is greater than "0".
=SMALL(IF(U250,$AB$5:$AB$24,$AB$4:$AB$23),ROW(A2) ). I now want to account
for the next score input (U26) and move the range down another cell if U26
is greater than 0. The formula sits in AD4:AD13, the 10 lowest scores in the
target range.

How can i modify the formula to move the range down 1 cell to account for
input in U26,U27 etc. etc. but keeping the range at 20 cells? Thanks.

"handicapper" wrote:

Hi Dave,

Thanks for your reply. I'll expand on what I'm trying to do. A handicap is
calculated by averaging the best 10 scores from the last 20 rounds played.
All this is fine for the initial calculation but when the player plays his
21st. round the cell range of his last 20 scores has to be updated (ie 2-21
instead of 1-20).

So I'd like to write a formula which moves the cell range accordingly when a
new score is input. But does'nt move it if zero is returned on the 21st
playing day. I'm not sure due to my ignorance whether your initial reply
covers this (my fault if it does'nt).

Handicapper

"ozgrid.com" wrote:

Not sure I understand, but try base any calculations of a dynamic named
range.
http://www.ozgrid.com/Excel/DynamicRanges.htm



--
Regards
Dave Hawley
www.ozgrid.com
"handicapper" wrote in message
...
I'm creating a spreadsheet to process golf scores and calculate handicaps.

I've identified the best 10 (AD4:AD13)scores from the past 20 (AB4:AB23).
What I now need to do is cater for the next score input which will be in
AB24
and move the range to account for the new score (AB5:AB24). I also need
to
tell s/s not to move the range if input value is zero (column 'U').
That's
just a start but would appreciate any help offered. Thanks.