I need to create a "scrolling" table where the first number in the series of
numbers "drops out" of the array when a new number is added ... for example a
golf handicap where, when the 20th score is entered, the first score is
replaced, keeping on the latest scores.

I have no clue how to structure/create such a table, but I understand
formulas, functions.

Say you want to average the last 20 entries in an array in Column A, from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A 100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Coach" wrote in message
...
I need to create a "scrolling" table where the first number in the series of
numbers "drops out" of the array when a new number is added ... for example
a
golf handicap where, when the 20th score is entered, the first score is
replaced, keeping on the latest scores.

I have no clue how to structure/create such a table, but I understand
formulas, functions.

In addition, if it is for golf handicap you will want to average the lowest 10
from the last 20.

=AVERAGE(SMALL(OFFSET(A1,COUNTA(A:A)-1,0,-20,1),ROW(INDIRECT("1:10"))))

Also an array formula.

Now all you have to do is figure out your "Real" Handicap from the Course Rating
and Slope Rating at your course.

Following is an example for determining a differential using an adjusted gross
score of 95 made on a course with a USGA Course Rating of 71.5 and a USGA
Slope Rating of 125:

Adjusted Gross Score - USGA Course Rating: 95 - 71.5 = 23.5
Difference x Standard Slope: 23.5 x 113 = 2655.5
Result / USGA Slope Rating: 2655.5 / 125 = 21.24
Handicap Differential (rounded): 21.2


Gord Dibben MS Excel MVP


On Sat, 13 Oct 2007 09:27:27 -0700, "RagDyeR" wrote:

Say you want to average the last 20 entries in an array in Column A, from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1: A100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Thanks for the prompt reply.

I thought I had a pretty good handle on functions until I saw this one!
First, I assume this formula, with different references, will work across
columns as well. The structure of the table I am visualizing will contain
different values/records in the rows and new scores added down (over time)
the columns

Second, I do not understand the INDEX and LARGE parts of the formula. I
have not worked with arrays before. Could you please break down/explain the
parts of this formula. Finally, can you suggest an online resource for
understanding arrays. I have a dozen books and there is little info on these
functions or the use of arrays.

Finally, thank you for the quality of your answer ... minimum geek speak,
no abbreviated, short cut communication and patience.

"RagDyeR" wrote:

Say you want to average the last 20 entries in an array in Column A, from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A 100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Coach" wrote in message
...
I need to create a "scrolling" table where the first number in the series of
numbers "drops out" of the array when a new number is added ... for example
a
golf handicap where, when the 20th score is entered, the first score is
replaced, keeping on the latest scores.

I have no clue how to structure/create such a table, but I understand
formulas, functions.

Great formula and info handicap. You've seen this problem before. I
don't understand the SMALL AND OFFSET part of the formula, but will do some
more looking.

Actually, I am not computing a handicap, but linking some player perfomance
statistics collected from a shot by shot self-rating (0-10) "scorecard".
Data collected on the scorecard worksheet then gets linked in a summary or
consolidation area on another worksheet where the oldest scores drop off.
The treated data tells me a player's "performance/confidence" level and what
% of the game a particular shot affects. I then construct my coaching plan
based on what needs the most coaching relative to how important the problem.

"Gord Dibben" wrote:

In addition, if it is for golf handicap you will want to average the lowest 10
from the last 20.

=AVERAGE(SMALL(OFFSET(A1,COUNTA(A:A)-1,0,-20,1),ROW(INDIRECT("1:10"))))

Also an array formula.

Now all you have to do is figure out your "Real" Handicap from the Course Rating
and Slope Rating at your course.

Following is an example for determining a differential using an adjusted gross
score of 95 made on a course with a USGA Course Rating of 71.5 and a USGA
Slope Rating of 125:

Adjusted Gross Score - USGA Course Rating: 95 - 71.5 = 23.5
Difference x Standard Slope: 23.5 x 113 = 2655.5
Result / USGA Slope Rating: 2655.5 / 125 = 21.24
Handicap Differential (rounded): 21.2


Gord Dibben MS Excel MVP


On Sat, 13 Oct 2007 09:27:27 -0700, "RagDyeR" wrote:

Say you want to average the last 20 entries in an array in Column A, from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1: A100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Check out this link of Chip Pearson's for a good intro to arrays:

http://www.cpearson.com/excel/ArrayFormulas.aspx

As to Index ... it can be complicated, since it can return *either* a value
or a reference, depending on it's use in a formula.
And it can reference either a one dimensional array, a two dimensional
array, or a number of different, non-adjacent arrays.

Say D6 to D10 contained the numbers 3 to 7 respectively;
E6 to E10 contained 500 to 900 respectively, and
F6 to F10 contained 2000 to 6000 respectively.

In it's simplest form of returning values:

From a one dimensional array,
=Index(D6:D10,2)
Would return the value 4,

=Index(D6:F6,2)
Would return the value 500

For 2 dimensions:
=Index(D6:F10,3,2)
Returns 700

=Index(D6:F10,2,3)
Returns 3000

Needles to say, arriving at the 2nd and 3rd arguments (,3,2 or ,2,3) is
usually accomplished using other functions.
This is mostly seen in the Index - Match combination, which is commonly used
in place of Vlookup, where the lookup value is *not* in the left-most
position of a datalist.:

=Index(D6:D10,Match(700,E6:E10,0))
To return 5.

=Index(D6:F10,Match(5000,F6:F10,0),Match(500,D6:F6 ,0))
To return 800.

As to it's use as a reference, which is how it's being used in this thread:

=Sum(D7:F9)
Returns 14115

=Sum(D7:Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115

=Sum(Index(D6:D10,Large(D6:D10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115

AND, of course, we could just as easily replace the Sum() function with the
Average() function:

=Average(Index(D6:D10,Large(D6:D10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Which returns 1568.3
The same as:
=Average(D7:F9)

*NOTE* ... the Large() function in the above example formula is *NOT* being
used in the same way as in the Average formula in this thread.

You'll notice that *none* of the above formulas are *array* formulas,
needing a CSE entry.

I would suggest that you read through Chip's link on arrays before you
continue with the rest of this explanation.

In the formula we're discussing here, we're simply trying to determine how
to arrive at one of the arguments in the Index function which, in this case,
is being used to return one of the references for the Average function.
We started off with using A100 at the beginning of the Average formula.
It could just as well have been placed at the end of the formula, which
would be it's normal placement in the usual course of events.

=AVERAGE(INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< ""),20)):A100)

I use it there simply to easily visually mark the end of the range.

Since I assume you now have some understanding of Index, we're now looking
at this part of the formula:

INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<""),20))

To see how we arrived at the second argument in Index, which determines the
starting location (reference) in the Average function.

Select the cell containing the entire formula.
In the formula bar, select:
Row(1:100)
And hit <F9

You'll see the array of 1 to 100.
Now hit <Esc, so that you don't destroy the formula.

Select
(A1:A100<"")
And hit <F9

You'll see an array of True and False, depending on which rows have, or
don't have, data in them.
Hit <Esc again.

Now select both together,
Row(1:100)*(A1:A100<"")
And hit <F9

You'll see an array of row numbers that contain data, and zeroes for those
that don't.

This is what the Large function is looking at and working on (not the values
in those rows), and why you must use CSE, so that these arrays can be
accessed.

So, count the 20th largest *row* number, and you'll see how Index arrives at
the row reference that starts the range for Average to calculate.
Of course, less then 20 rows of data starts the Average function at the
beginning of the referenced range (A1).
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Coach" wrote in message
...
Thanks for the prompt reply.

I thought I had a pretty good handle on functions until I saw this one!
First, I assume this formula, with different references, will work
across
columns as well. The structure of the table I am visualizing will contain
different values/records in the rows and new scores added down (over time)
the columns

Second, I do not understand the INDEX and LARGE parts of the formula. I
have not worked with arrays before. Could you please break down/explain
the
parts of this formula. Finally, can you suggest an online resource for
understanding arrays. I have a dozen books and there is little info on
these
functions or the use of arrays.

Finally, thank you for the quality of your answer ... minimum geek speak,
no abbreviated, short cut communication and patience.

"RagDyeR" wrote:

Say you want to average the last 20 entries in an array in Column A,
from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A 100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Coach" wrote in message
...
I need to create a "scrolling" table where the first number in the
series of
numbers "drops out" of the array when a new number is added ... for
example
a
golf handicap where, when the 20th score is entered, the first score is
replaced, keeping on the latest scores.

I have no clue how to structure/create such a table, but I understand
formulas, functions.

Thanks for the help. I think I know what to do. The reference to the
pearson website was also a help

Coach

"Ragdyer" wrote:

Check out this link of Chip Pearson's for a good intro to arrays:

http://www.cpearson.com/excel/ArrayFormulas.aspx

As to Index ... it can be complicated, since it can return *either* a value
or a reference, depending on it's use in a formula.
And it can reference either a one dimensional array, a two dimensional
array, or a number of different, non-adjacent arrays.

Say D6 to D10 contained the numbers 3 to 7 respectively;
E6 to E10 contained 500 to 900 respectively, and
F6 to F10 contained 2000 to 6000 respectively.

In it's simplest form of returning values:

From a one dimensional array,
=Index(D6:D10,2)
Would return the value 4,

=Index(D6:F6,2)
Would return the value 500

For 2 dimensions:
=Index(D6:F10,3,2)
Returns 700

=Index(D6:F10,2,3)
Returns 3000

Needles to say, arriving at the 2nd and 3rd arguments (,3,2 or ,2,3) is
usually accomplished using other functions.
This is mostly seen in the Index - Match combination, which is commonly used
in place of Vlookup, where the lookup value is *not* in the left-most
position of a datalist.:

=Index(D6:D10,Match(700,E6:E10,0))
To return 5.

=Index(D6:F10,Match(5000,F6:F10,0),Match(500,D6:F6 ,0))
To return 800.

As to it's use as a reference, which is how it's being used in this thread:

=Sum(D7:F9)
Returns 14115

=Sum(D7:Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115

=Sum(Index(D6:D10,Large(D6:D10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115

AND, of course, we could just as easily replace the Sum() function with the
Average() function:

=Average(Index(D6:D10,Large(D6:D10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Which returns 1568.3
The same as:
=Average(D7:F9)

*NOTE* ... the Large() function in the above example formula is *NOT* being
used in the same way as in the Average formula in this thread.

You'll notice that *none* of the above formulas are *array* formulas,
needing a CSE entry.

I would suggest that you read through Chip's link on arrays before you
continue with the rest of this explanation.

In the formula we're discussing here, we're simply trying to determine how
to arrive at one of the arguments in the Index function which, in this case,
is being used to return one of the references for the Average function.
We started off with using A100 at the beginning of the Average formula.
It could just as well have been placed at the end of the formula, which
would be it's normal placement in the usual course of events.

=AVERAGE(INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< ""),20)):A100)

I use it there simply to easily visually mark the end of the range.

Since I assume you now have some understanding of Index, we're now looking
at this part of the formula:

INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<""),20))

To see how we arrived at the second argument in Index, which determines the
starting location (reference) in the Average function.

Select the cell containing the entire formula.
In the formula bar, select:
Row(1:100)
And hit <F9

You'll see the array of 1 to 100.
Now hit <Esc, so that you don't destroy the formula.

Select
(A1:A100<"")
And hit <F9

You'll see an array of True and False, depending on which rows have, or
don't have, data in them.
Hit <Esc again.

Now select both together,
Row(1:100)*(A1:A100<"")
And hit <F9

You'll see an array of row numbers that contain data, and zeroes for those
that don't.

This is what the Large function is looking at and working on (not the values
in those rows), and why you must use CSE, so that these arrays can be
accessed.

So, count the 20th largest *row* number, and you'll see how Index arrives at
the row reference that starts the range for Average to calculate.
Of course, less then 20 rows of data starts the Average function at the
beginning of the referenced range (A1).
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Coach" wrote in message
...
Thanks for the prompt reply.

I thought I had a pretty good handle on functions until I saw this one!
First, I assume this formula, with different references, will work
across
columns as well. The structure of the table I am visualizing will contain
different values/records in the rows and new scores added down (over time)
the columns

Second, I do not understand the INDEX and LARGE parts of the formula. I
have not worked with arrays before. Could you please break down/explain
the
parts of this formula. Finally, can you suggest an online resource for
understanding arrays. I have a dozen books and there is little info on
these
functions or the use of arrays.

Finally, thank you for the quality of your answer ... minimum geek speak,
no abbreviated, short cut communication and patience.

"RagDyeR" wrote:

Say you want to average the last 20 entries in an array in Column A,
from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A 100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Coach" wrote in message
...
I need to create a "scrolling" table where the first number in the
series of
numbers "drops out" of the array when a new number is added ... for
example
a
golf handicap where, when the 20th score is entered, the first score is
replaced, keeping on the latest scores.

I have no clue how to structure/create such a table, but I understand
formulas, functions.

You're welcome, and appreciate your feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Coach" wrote in message
...
Thanks for the help. I think I know what to do. The reference to the
pearson website was also a help

Coach

"Ragdyer" wrote:

Check out this link of Chip Pearson's for a good intro to arrays:

http://www.cpearson.com/excel/ArrayFormulas.aspx

As to Index ... it can be complicated, since it can return *either* a
value
or a reference, depending on it's use in a formula.
And it can reference either a one dimensional array, a two dimensional
array, or a number of different, non-adjacent arrays.

Say D6 to D10 contained the numbers 3 to 7 respectively;
E6 to E10 contained 500 to 900 respectively, and
F6 to F10 contained 2000 to 6000 respectively.

In it's simplest form of returning values:

From a one dimensional array,
=Index(D6:D10,2)
Would return the value 4,

=Index(D6:F6,2)
Would return the value 500

For 2 dimensions:
=Index(D6:F10,3,2)
Returns 700

=Index(D6:F10,2,3)
Returns 3000

Needles to say, arriving at the 2nd and 3rd arguments (,3,2 or ,2,3) is
usually accomplished using other functions.
This is mostly seen in the Index - Match combination, which is commonly
used
in place of Vlookup, where the lookup value is *not* in the left-most
position of a datalist.:

=Index(D6:D10,Match(700,E6:E10,0))
To return 5.

=Index(D6:F10,Match(5000,F6:F10,0),Match(500,D6:F6 ,0))
To return 800.

As to it's use as a reference, which is how it's being used in this
thread:

=Sum(D7:F9)
Returns 14115

=Sum(D7:Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115

=Sum(Index(D6:D10,Large(D6:D10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115

AND, of course, we could just as easily replace the Sum() function with
the
Average() function:

=Average(Index(D6:D10,Large(D6:D10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Which returns 1568.3
The same as:
=Average(D7:F9)

*NOTE* ... the Large() function in the above example formula is *NOT*
being
used in the same way as in the Average formula in this thread.

You'll notice that *none* of the above formulas are *array* formulas,
needing a CSE entry.

I would suggest that you read through Chip's link on arrays before you
continue with the rest of this explanation.

In the formula we're discussing here, we're simply trying to determine
how
to arrive at one of the arguments in the Index function which, in this
case,
is being used to return one of the references for the Average function.
We started off with using A100 at the beginning of the Average formula.
It could just as well have been placed at the end of the formula, which
would be it's normal placement in the usual course of events.

=AVERAGE(INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< ""),20)):A100)

I use it there simply to easily visually mark the end of the range.

Since I assume you now have some understanding of Index, we're now
looking
at this part of the formula:

INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<""),20))

To see how we arrived at the second argument in Index, which determines
the
starting location (reference) in the Average function.

Select the cell containing the entire formula.
In the formula bar, select:
Row(1:100)
And hit <F9

You'll see the array of 1 to 100.
Now hit <Esc, so that you don't destroy the formula.

Select
(A1:A100<"")
And hit <F9

You'll see an array of True and False, depending on which rows have, or
don't have, data in them.
Hit <Esc again.

Now select both together,
Row(1:100)*(A1:A100<"")
And hit <F9

You'll see an array of row numbers that contain data, and zeroes for
those
that don't.

This is what the Large function is looking at and working on (not the
values
in those rows), and why you must use CSE, so that these arrays can be
accessed.

So, count the 20th largest *row* number, and you'll see how Index arrives
at
the row reference that starts the range for Average to calculate.
Of course, less then 20 rows of data starts the Average function at the
beginning of the referenced range (A1).
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Coach" wrote in message
...
Thanks for the prompt reply.

I thought I had a pretty good handle on functions until I saw this one!
First, I assume this formula, with different references, will work
across
columns as well. The structure of the table I am visualizing will
contain
different values/records in the rows and new scores added down (over
time)
the columns

Second, I do not understand the INDEX and LARGE parts of the formula.
I
have not worked with arrays before. Could you please break
down/explain
the
parts of this formula. Finally, can you suggest an online resource for
understanding arrays. I have a dozen books and there is little info on
these
functions or the use of arrays.

Finally, thank you for the quality of your answer ... minimum geek
speak,
no abbreviated, short cut communication and patience.

"RagDyeR" wrote:

Say you want to average the last 20 entries in an array in Column A,
from A1
to A100.

Try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A 100<""),20)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead
of
the
regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Coach" wrote in message
...
I need to create a "scrolling" table where the first number in the
series of
numbers "drops out" of the array when a new number is added ... for
example
a
golf handicap where, when the 20th score is entered, the first score
is
replaced, keeping on the latest scores.

I have no clue how to structure/create such a table, but I understand
formulas, functions.