Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
golf handicap 4 of last 5 scores | Excel Discussion (Misc queries) | |||
Excel formula for golf score to handicap | Excel Worksheet Functions | |||
Excel-based golf handicap tool | Excel Discussion (Misc queries) | |||
golf handicap | Excel Discussion (Misc queries) | |||
Golf Handicap Using Last 5 Scores | Excel Worksheet Functions |