Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
I would like to use the 10 lowest scores from the last 20 rounds played. I
dont want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example €“ scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or
a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
I am using Exvel 2003.
The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
Ok, try this array formula**.
This is what it'll do: If there are 0 scores entered it will leave the cell blank. If there are <10 scores it will average whatever scores are available. If there are =10 and <=20 scores it will average the lowest 10 scores. If there are 20 scores it will average the lowest 10 scores out of the last 20 scores. Important: if a golfer doesn't play that day/week (whatever) *leave the cell empty*. Don't enter a 0 or a dash "-". Don't enter anything! The formula is kind of long so to save a few keystrokes create this defined name: Goto the menu InsertNameDefine Name: Array Refers to: ={1,2,3,4,5,6,7,8,9,10} OK After you've created that name go back and make sure Excel hasn't changed it. Excel has a habit of adding quotes to named strings like that. If it added any quotes remove them then cuss loudly at Excel! OK, assuming the range of scores will be in B2:AB2... Array entered** : =IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)<=20,AVERAGE(SMALL(B2:AB2 ,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2: AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array))))) ** 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 "Larry L" wrote in message ... I am using Exvel 2003. The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
ThanKs -- I will try it
FYI - this is the formula I am using now for avaraging the last 5 scores =AVERAGE(TRANSPOSE(OFFSET(A501,0,LARGE((501:5010) *(COLUMN($1:$1)),{1,2,3,4,5})-1))) It is also an array formula. The data it is averaging is in row 501. I have this for each of the 32 golfers in the league (I got help on it a couple of years ago and the league want to change how we calculate averages) "T. Valko" wrote: Ok, try this array formula**. This is what it'll do: If there are 0 scores entered it will leave the cell blank. If there are <10 scores it will average whatever scores are available. If there are =10 and <=20 scores it will average the lowest 10 scores. If there are 20 scores it will average the lowest 10 scores out of the last 20 scores. Important: if a golfer doesn't play that day/week (whatever) *leave the cell empty*. Don't enter a 0 or a dash "-". Don't enter anything! The formula is kind of long so to save a few keystrokes create this defined name: Goto the menu InsertNameDefine Name: Array Refers to: ={1,2,3,4,5,6,7,8,9,10} OK After you've created that name go back and make sure Excel hasn't changed it. Excel has a habit of adding quotes to named strings like that. If it added any quotes remove them then cuss loudly at Excel! OK, assuming the range of scores will be in B2:AB2... Array entered** : =IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)<=20,AVERAGE(SMALL(B2:AB2 ,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2: AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array))))) ** 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 "Larry L" wrote in message ... I am using Exvel 2003. The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
Hello Larry,
Here's another option =IF(COUNT(501:501),AVERAGE(SMALL(INDEX(501:501,LAR GE(IF(ISNUMBER (501:501),COLUMN(501:501)),MIN(COUNT(501:501),20)) ):IV501,ROW(INDIRECT ("1:"&MIN(COUNT(501:501),10))))),"") |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
There is a problem with the formula. It doesn't like ISNUMBER.
Is this an array formula that requires CTRL+SHIFT+ENTER? "barry houdini" wrote: Hello Larry, Here's another option =IF(COUNT(501:501),AVERAGE(SMALL(INDEX(501:501,LAR GE(IF(ISNUMBER (501:501),COLUMN(501:501)),MIN(COUNT(501:501),20)) ):IV501,ROW(INDIRECT ("1:"&MIN(COUNT(501:501),10))))),"") |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
On Jan 4, 6:03*pm, Larry L wrote:
There is a problem with the formula. It doesn't like ISNUMBER. Is this an array formula that requires CTRL+SHIFT+ENTER? "barry houdini" wrote: Hello Larry, Here's another option =IF(COUNT(501:501),AVERAGE(SMALL(INDEX(501:501,LAR GE(IF(ISNUMBER (501:501),COLUMN(501:501)),MIN(COUNT(501:501),20)) ):IV501,ROW(INDIRECT ("1:"&MIN(COUNT(501:501),10))))),"")- Hide quoted text - - Show quoted text - Yes, Larry, it's also an array formula that needs CSE. Some spaces got inserted in the formula when posting. There are no spaces in the formula so any spaces after ISNUMBER and after ROW (INDIRECT need to be removed....formula should then work. Should work better below perhaps..... =IF(COUNT(501:501),AVERAGE(SMALL(INDEX(501:501,LAR GE(IF(ISNUMBER (501:501),COLUMN(501:501)),MIN(COUNT(501:501),20)) ):IV501,ROW(INDIRECT ("1:"&MIN(COUNT(501:501),10))))),"") |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
Thank you very much, the last formula correctly calculates the averages based
on some limited testing. "barry houdini" wrote: On Jan 4, 6:03 pm, Larry L wrote: There is a problem with the formula. It doesn't like ISNUMBER. Is this an array formula that requires CTRL+SHIFT+ENTER? "barry houdini" wrote: Hello Larry, Here's another option =IF(COUNT(501:501),AVERAGE(SMALL(INDEX(501:501,LAR GE(IF(ISNUMBER (501:501),COLUMN(501:501)),MIN(COUNT(501:501),20)) ):IV501,ROW(INDIRECT ("1:"&MIN(COUNT(501:501),10))))),"")- Hide quoted text - - Show quoted text - Yes, Larry, it's also an array formula that needs CSE. Some spaces got inserted in the formula when posting. There are no spaces in the formula so any spaces after ISNUMBER and after ROW (INDIRECT need to be removed....formula should then work. Should work better below perhaps..... =IF(COUNT(501:501),AVERAGE(SMALL(INDEX(501:501,LAR GE(IF(ISNUMBER (501:501),COLUMN(501:501)),MIN(COUNT(501:501),20)) ):IV501,ROW(INDIRECT ("1:"&MIN(COUNT(501:501),10))))),"") |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
Biff- When I tried the formula you provided with some live data I was getting some incorrect results. However I am having some luck with the formula Barry sent. Thank you for your help. "T. Valko" wrote: Ok, try this array formula**. This is what it'll do: If there are 0 scores entered it will leave the cell blank. If there are <10 scores it will average whatever scores are available. If there are =10 and <=20 scores it will average the lowest 10 scores. If there are 20 scores it will average the lowest 10 scores out of the last 20 scores. Important: if a golfer doesn't play that day/week (whatever) *leave the cell empty*. Don't enter a 0 or a dash "-". Don't enter anything! The formula is kind of long so to save a few keystrokes create this defined name: Goto the menu InsertNameDefine Name: Array Refers to: ={1,2,3,4,5,6,7,8,9,10} OK After you've created that name go back and make sure Excel hasn't changed it. Excel has a habit of adding quotes to named strings like that. If it added any quotes remove them then cuss loudly at Excel! OK, assuming the range of scores will be in B2:AB2... Array entered** : =IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)<=20,AVERAGE(SMALL(B2:AB2 ,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2: AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array))))) ** 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 "Larry L" wrote in message ... I am using Exvel 2003. The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
It works for me and was thoroughly tested.
Barry's also works and returns identical results to my suggested formula but his uses a volatile function which I always try to avoid if possible. As long as you have something that works is all that counts! Thanks for the feedback! -- Biff Microsoft Excel MVP "Larry L" wrote in message ... Biff- When I tried the formula you provided with some live data I was getting some incorrect results. However I am having some luck with the formula Barry sent. Thank you for your help. "T. Valko" wrote: Ok, try this array formula**. This is what it'll do: If there are 0 scores entered it will leave the cell blank. If there are <10 scores it will average whatever scores are available. If there are =10 and <=20 scores it will average the lowest 10 scores. If there are 20 scores it will average the lowest 10 scores out of the last 20 scores. Important: if a golfer doesn't play that day/week (whatever) *leave the cell empty*. Don't enter a 0 or a dash "-". Don't enter anything! The formula is kind of long so to save a few keystrokes create this defined name: Goto the menu InsertNameDefine Name: Array Refers to: ={1,2,3,4,5,6,7,8,9,10} OK After you've created that name go back and make sure Excel hasn't changed it. Excel has a habit of adding quotes to named strings like that. If it added any quotes remove them then cuss loudly at Excel! OK, assuming the range of scores will be in B2:AB2... Array entered** : =IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)<=20,AVERAGE(SMALL(B2:AB2 ,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2: AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array))))) ** 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 "Larry L" wrote in message ... I am using Exvel 2003. The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
You are correct -- your formula works. Sorry about that (Its a good thing I don't do this for a living!) However, I will still have a problem I now see with either formula. The row that I am Averaing is a derived (= to another cell). Therefore, I will be getting - in the cells without scores. For example: On my spread sheet I am using row 501 for the 1st golfers scores and cell B501 on my sheet =cell B36. Whould you suggest that I make cell B501 a function =if(B360,B36,"")? I assume if cell B36 is blank this would still work. "T. Valko" wrote: It works for me and was thoroughly tested. Barry's also works and returns identical results to my suggested formula but his uses a volatile function which I always try to avoid if possible. As long as you have something that works is all that counts! Thanks for the feedback! -- Biff Microsoft Excel MVP "Larry L" wrote in message ... Biff- When I tried the formula you provided with some live data I was getting some incorrect results. However I am having some luck with the formula Barry sent. Thank you for your help. "T. Valko" wrote: Ok, try this array formula**. This is what it'll do: If there are 0 scores entered it will leave the cell blank. If there are <10 scores it will average whatever scores are available. If there are =10 and <=20 scores it will average the lowest 10 scores. If there are 20 scores it will average the lowest 10 scores out of the last 20 scores. Important: if a golfer doesn't play that day/week (whatever) *leave the cell empty*. Don't enter a 0 or a dash "-". Don't enter anything! The formula is kind of long so to save a few keystrokes create this defined name: Goto the menu InsertNameDefine Name: Array Refers to: ={1,2,3,4,5,6,7,8,9,10} OK After you've created that name go back and make sure Excel hasn't changed it. Excel has a habit of adding quotes to named strings like that. If it added any quotes remove them then cuss loudly at Excel! OK, assuming the range of scores will be in B2:AB2... Array entered** : =IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)<=20,AVERAGE(SMALL(B2:AB2 ,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2: AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array))))) ** 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 "Larry L" wrote in message ... I am using Exvel 2003. The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averages - Golf League Scoring
On Jan 4, 7:25*pm, Larry L wrote:
You are correct -- your formula works. Sorry about that (Its a good thing I don't do this for a living!) However, I will still have a problem I now see with either formula. The row that I am Averaing is a derived (= to another cell). Therefore, I will be getting - in the cells without scores. For example: On my spread sheet I am using row 501 for the 1st golfers scores and cell B501 on my sheet =cell B36. Whould you suggest that I make cell B501 a function =if(B360,B36,"")? I assume if cell B36 is blank this would still work. "T. Valko" wrote: It works for me and was thoroughly tested. Barry's also works and returns identical results to my suggested formula but his uses a volatile function which I always try to avoid if possible. As long as you have something that works is all that counts! Thanks for the feedback! -- Biff Microsoft Excel MVP "Larry L" wrote in message ... Biff- When I tried the formula you provided with some live data I was getting some incorrect results. However I am having some luck with the formula Barry sent. Thank you for your help. "T. Valko" wrote: Ok, try this array formula**. This is what it'll do: If there are 0 scores entered it will leave the cell blank. If there are <10 scores it will average whatever scores are available. If there are =10 and <=20 scores it will average the lowest 10 scores. If there are 20 scores it will average the lowest 10 scores out of the last 20 scores. Important: if a golfer doesn't play that day/week (whatever) *leave the cell empty*. Don't enter a 0 or a dash "-". Don't enter anything! The formula is kind of long so to save a few keystrokes create this defined name: Goto the menu InsertNameDefine Name: Array Refers to: ={1,2,3,4,5,6,7,8,9,10} OK After you've created that name go back and make sure Excel hasn't changed it. Excel has a habit of adding quotes to named strings like that. If it added any quotes remove them then cuss loudly at Excel! OK, assuming the range of scores will be in B2:AB2... Array entered** : =IF(COUNT(B2:AB2)=0,"",IF(COUNT(B2:AB2)<10,AVERAGE (B2:AB2),IF(COUNT(B2:AB2)*<=20,AVERAGE(SMALL(B2:AB 2,Array)),AVERAGE(SMALL(AB2:INDEX(B2:AB2,LARGE((B2 :*AB2<"")*(COLUMN(B2:AB2)),20)-COLUMN(B2)+1),Array))))) ** 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 "Larry L" wrote in message ... I am using Exvel 2003. The 1st column will be the Golfer's name. The second column will contain all the scores for a particular week. The next column will contain the next week scores and so on. I am trying to get 20 columns that are greater than 0 and take the 10 lowest scores. It may take 22 weeks of scores if a golfer was absent 2 of the weeks. I presently have a formula that averages the last 5 scores. We wouuld like to change this to the low 10 of the last 20 golfed. "T. Valko" wrote: If a golfer doesn't play that day *leave the cell empty*. Don't enter a 0 or a dash "-", don't enter anything! What version of Excel are you using? Trying to put all these conditions into a single formula is pushing the nested function limit to the max! What is the first column where the scores are entered? -- Biff Microsoft Excel MVP "Larry L" wrote in message ... I would like to use the 10 lowest scores from the last 20 rounds played. I don't want to consider the weeks not played. So I may have a row with 26 cells and with 2 cells that are blank (or 0) because the golfer did not show up to golf those weeks. I would like to go from right to left in the row and consider the first 20 cells that are not 0 or blank and then take the lowest 10 of these 20 scores and average those 10 cells. (The reason for going right to left is that I will add a new score to the end of the row when the golfer play another round the following week (after the 45 in the example below) For example - scores over the past 25 weeks: 41, 40, 42, 38, 46, 44, 41, -, 39, 40, 41, 40, 43, 42, 41, 40, 42, -, 40, 41, 42, 38, 40, 42, 45 I would like to begin considering the score of 45 in week 25 and consider the previous 20 nonzero scores going the left (in this example it would take be to the 38 in week 4). From those 20 nonzero scores, take the 10 lowest scores and develop an average. Assuming the golfer showed-up in week 26 I would repeat the process beginning with the score made in week 26 and going to the 46 in week 5. Other considerations: For players with between 10 & 20 scores, take the lowest 10 nonzero scores and average them. For players with less than 10 scores, take an average of all nonzero scores. Please help with the formula to accomplish this- Hide quoted text - - Show quoted text - Hello Larry, I believe that you could adapt either formula to work with zeroes but it would probably be better to adjust the B501 formula as you suggest. I believe that will work with my suggestion and Biff's |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
golf scoring | Excel Discussion (Misc queries) | |||
Help With A Golf League | Excel Discussion (Misc queries) | |||
PING: Bernie Deitrick - Calloway Golf Scoring | Excel Discussion (Misc queries) | |||
golf league schedule | Excel Discussion (Misc queries) | |||
Golf League Schedule | Excel Worksheet Functions |