Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
I posted this under a different heading, but am posting it under the Golf
Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
This array formula** will extract the lowest 10 from the last 20 scores from
the range A1:A50. Array entered** in J6 and copied down to J15: =SMALL(INDEX(A$1:A$50,LARGE(ROW(A$1:A$50)*ISNUMBER (A$1:A$50),20)):A$50,ROWS(J$6:J6)) No error checking! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Don" wrote in message ... I posted this under a different heading, but am posting it under the Golf Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
Thank you Biff. That worked perfect after I changed the column from A to H.
I extended it down to about H350. I guess I could extend it to any value, but realistically I'll keep it arount 350 for now. Thanks for the array. Don D. "T. Valko" wrote in message ... This array formula** will extract the lowest 10 from the last 20 scores from the range A1:A50. Array entered** in J6 and copied down to J15: =SMALL(INDEX(A$1:A$50,LARGE(ROW(A$1:A$50)*ISNUMBER (A$1:A$50),20)):A$50,ROWS(J$6:J6)) No error checking! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Don" wrote in message ... I posted this under a different heading, but am posting it under the Golf Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Don" wrote in message ... Thank you Biff. That worked perfect after I changed the column from A to H. I extended it down to about H350. I guess I could extend it to any value, but realistically I'll keep it arount 350 for now. Thanks for the array. Don D. "T. Valko" wrote in message ... This array formula** will extract the lowest 10 from the last 20 scores from the range A1:A50. Array entered** in J6 and copied down to J15: =SMALL(INDEX(A$1:A$50,LARGE(ROW(A$1:A$50)*ISNUMBER (A$1:A$50),20)):A$50,ROWS(J$6:J6)) No error checking! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Don" wrote in message ... I posted this under a different heading, but am posting it under the Golf Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
Would it be possible for you to send me your handicap formula? i have been
fighting Excel trying to get one to work. Thanks in advance if you are able. "Don" wrote: I posted this under a different heading, but am posting it under the Golf Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
MJ
Browse through this 'net search http://www.alltheweb.com/search?cat=...&_sb_lang=pref Gord Dibben MS Excel MVP On Tue, 29 Sep 2009 14:15:02 -0700, MJ wrote: Would it be possible for you to send me your handicap formula? i have been fighting Excel trying to get one to work. Thanks in advance if you are able. "Don" wrote: I posted this under a different heading, but am posting it under the Golf Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
MJ, I can send you my version of the golf handicap. Where do you want me to
send it to? Don D. "MJ" wrote in message ... Would it be possible for you to send me your handicap formula? i have been fighting Excel trying to get one to work. Thanks in advance if you are able. "Don" wrote: I posted this under a different heading, but am posting it under the Golf Handicap in hopes of finding someone who has a spreadsheet that will calculate Handicaps. I wrote a spreadsheet in Quattro Pro X3 to calculate Golf Handicaps. I have imported it to Excel 2007 but I can't get one formula to work. The formulas for calculating Index, Differential and Handicap work fine but the formula to find the loewst 10 scores from the last 20 rounds played doesn't work. In the spreadsheet I have calculated the differentials in column 'H' starting at row 6 and am trying to put the lowest 10 scores from the last 20 played in Column 'J6 thru J15'. The following Quattro Pro formula is what is used in columns J6 thru J15. ),"-",@SMALLEST(@@(@OFFSET(H$6,@MAX(0,@PURECOUNT(A ))) It works fine in QP but when I imported this to Excel, it came out as: =IF(COUNT(H$1:H$16960)<ROWS(H$6:H6),"-",SMALL(INDIRECT(OFFSET(H$6,MAX(0,COUNT(H$1:H$1696 0)-20),0,20,1)),ROWS(H$6:H6))) When I import the Quattro Pro spreadsheet to Excel I get errors in H6-H15 "#VALUE!" and in the "I" column it says: "A value used in the formula is of the wrong data type." I am brand new to Excel and have modified the =IF statement several times without success. The references to H$1 were changed to H$6 because labels are above H6, but I am stumped. Have I given enough information for anyone to help me? Thanks |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Golf Handicap question
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another golf handicap question | Excel Discussion (Misc queries) | |||
Golf Handicap Anomoly.... | Excel Worksheet Functions | |||
golf handicap | Excel Discussion (Misc queries) | |||
golf handicap | Excel Discussion (Misc queries) | |||
Golf Handicap | Excel Worksheet Functions |