ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Golf Handicap Anomoly.... (https://www.excelbanter.com/excel-worksheet-functions/123640-golf-handicap-anomoly.html)

Bill Foley
Golf Handicap Anomoly....
 
Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and .96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest 10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of the
last 20. Any way to drop the lowest of those 10 and only take the remaining
9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just paranoid
that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com



Gary''s Student
Golf Handicap Anomoly....
 
1. copy your last 20 scores from column G to column H
2. sort column H
3 use =AVERAGE(H2:H10)
--
Gary's Student


"Bill Foley" wrote:

Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and .96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest 10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of the
last 20. Any way to drop the lowest of those 10 and only take the remaining
9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just paranoid
that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com




Bill Foley
Golf Handicap Anomoly....
 
That will work for the first time, but as I add more scores, I will have to
manually do this every time. Guess I could if there isn't some sort of
function to do it.

THANKS!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com
"Gary''s Student" wrote in message
...
1. copy your last 20 scores from column G to column H
2. sort column H
3 use =AVERAGE(H2:H10)
--
Gary's Student


"Bill Foley" wrote:

Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and
.96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest
10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of
the
last 20. Any way to drop the lowest of those 10 and only take the
remaining
9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just
paranoid
that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

Teethless mama
Golf Handicap Anomoly....
 
=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G),,-20),ROW(INDIRECT("1:9"))))

ctrl+shift+enter (not just enter)



"Bill Foley" wrote:

Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and .96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest 10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of the
last 20. Any way to drop the lowest of those 10 and only take the remaining
9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just paranoid
that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com




Bill Foley
Golf Handicap Anomoly....
 
That looks like it is working. I will play with it by adding a few numbers
here and there to see how it changes. If you have a minute would you mind
posting back a little verbage as to difference between my second option (-1,
2:10) and yours (,, 1:9). Just trying to understand instead of just doing.

THANKS!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com
"Teethless mama" wrote in message
...
=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G),,-20),ROW(INDIRECT("1:9"))))

ctrl+shift+enter (not just enter)



"Bill Foley" wrote:

Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and
.96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest
10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of
the
last 20. Any way to drop the lowest of those 10 and only take the
remaining
9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just
paranoid
that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

T. Valko
Golf Handicap Anomoly....
 
=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))

That formula is correct.

Test it on these 20 values:

82,88,84, 87,80,81, 86,79,77, 82,88,90, 84,75,80,80,80,84,83,83

Assume those values are in the range G1:G20

Extract the 10 smallest:

A1:A10 = 1,2,3,4,5,6,7,8,9,10
B1 = formula copied down to B10:

=SMALL(G:G,A1)

Now: =AVERAGE(B2:B9) = 80.11111

Your formula:

=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))

= 80.11111

Biff

"Bill Foley" <pttincatitexasdotnet wrote in message
...
Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and .96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest 10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of
the last 20. Any way to drop the lowest of those 10 and only take the
remaining 9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just
paranoid that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

T. Valko
Golf Handicap Anomoly....
 
Typo:

Now: =AVERAGE(B2:B9)

Should be:

Now: =AVERAGE(B2:B10)

Biff

"T. Valko" wrote in message
...
=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))

That formula is correct.

Test it on these 20 values:

82,88,84, 87,80,81, 86,79,77, 82,88,90, 84,75,80,80,80,84,83,83

Assume those values are in the range G1:G20

Extract the 10 smallest:

A1:A10 = 1,2,3,4,5,6,7,8,9,10
B1 = formula copied down to B10:

=SMALL(G:G,A1)

Now: =AVERAGE(B2:B9) = 80.11111

Your formula:

=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))

= 80.11111

Biff

"Bill Foley" <pttincatitexasdotnet wrote in message
...
Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and
.96 fudge factor and comes up with a handicap. Problem at our course is
they use their own system and throws in a twist. They still take the
lowest 10 of the last 20, but they drop the lowest of those 10 scores and
use the remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of
the last 20. Any way to drop the lowest of those 10 and only take the
remaining 9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just
paranoid that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

Bill Foley
Golf Handicap Anomoly....
 
Thanks. I'll check that out as well. I appreciate everyone's help.

MERRY CHRISTMAS!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com
"T. Valko" wrote in message
...
Typo:

Now: =AVERAGE(B2:B9)

Should be:

Now: =AVERAGE(B2:B10)

Biff

"T. Valko" wrote in message
...
=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))

That formula is correct.

Test it on these 20 values:

82,88,84, 87,80,81, 86,79,77, 82,88,90, 84,75,80,80,80,84,83,83

Assume those values are in the range G1:G20

Extract the 10 smallest:

A1:A10 = 1,2,3,4,5,6,7,8,9,10
B1 = formula copied down to B10:

=SMALL(G:G,A1)

Now: =AVERAGE(B2:B9) = 80.11111

Your formula:

=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))

= 80.11111

Biff

"Bill Foley" <pttincatitexasdotnet wrote in message
...
Hey Gang,

Excel 2003

I have created (with help from my friends here) a little spreadsheet
that tracks my handicap at my golf club using the array below. I
created the worksheet function that does the following:

Averages the lowest 10 of the last 20 scores, factors in the slope and
.96 fudge factor and comes up with a handicap. Problem at our course is
they use their own system and throws in a twist. They still take the
lowest 10 of the last 20, but they drop the lowest of those 10 scores
and use the remaining 9 to figure a handicap.

Here is the current function that I have been using for the lowest 10 of
the last 20. Any way to drop the lowest of those 10 and only take the
remaining 9 with the formula below?

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}

I tried using:

{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}

but the number just didn't seem consistent. Maybe it is and I just
paranoid that my handicap is too dang low! HA!

TIA!

--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com


All times are GMT +1. The time now is 10:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com