Prev: Unable to set the FormulaArrary property of the range class
Next: Array Formula Help Needed
From: handicapper on 13 Apr 2010 05:08
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.
From: ozgrid.com on 13 Apr 2010 05:26
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" <handicapper(a)discussions.microsoft.com> wrote in message
news:6F39E555-FEBE-4EF6-AE25-6BC8231FFC44(a)microsoft.com...
> 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.

From: handicapper on 14 Apr 2010 05:14
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" <handicapper(a)discussions.microsoft.com> wrote in message
> news:6F39E555-FEBE-4EF6-AE25-6BC8231FFC44(a)microsoft.com...
> > 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.
>
From: handicapper on 17 Apr 2010 00:50
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(U25>0,$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" <handicapper(a)discussions.microsoft.com> wrote in message
> > news:6F39E555-FEBE-4EF6-AE25-6BC8231FFC44(a)microsoft.com...
> > > 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.
> >
 | 
Pages: 1
Prev: Unable to set the FormulaArrary property of the range class
Next: Array Formula Help Needed