Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Golfsheet function
I tried some different functions like an array that I thought would work
but didn't. I realize that I will have to make another function to figure avg, but would like to do the following in one cell. A B C D E F G H I J K L M 1 Name: Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8 Wk9 Wk10 Total 2 Anybody 45 40 38 40 43 42 40 35 What I would like to do is take the last 6 scores, throw out the high and low and take the total of the remaining 4. Taking into consideration that some people will not be there some weeks. I did a google search and came up with some array formulas, that took the last 3 scores (didn't work) but had no way to delete out the high and low anyway. |
#2
|
|||
|
|||
=SUM(LARGE(B2:Z2,{2,3}))
-- HTH Bob Phillips "TBD" wrote in message ... I tried some different functions like an array that I thought would work but didn't. I realize that I will have to make another function to figure avg, but would like to do the following in one cell. A B C D E F G H I J K L M 1 Name: Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8 Wk9 Wk10 Total 2 Anybody 45 40 38 40 43 42 40 35 What I would like to do is take the last 6 scores, throw out the high and low and take the total of the remaining 4. Taking into consideration that some people will not be there some weeks. I did a google search and came up with some array formulas, that took the last 3 scores (didn't work) but had no way to delete out the high and low anyway. |
#3
|
|||
|
|||
If I understand you correctly, you want to start at the latest score and
count back to only include the last 6 scores, allowing for some golfers to skip a week. Consequently, you may go back 6 weeks to get 6 scores for some golfers, but go back 7 or 8 for others. Then eliminate the high and low from that period and sum the remaining 4 scores. If that is correct, I think this formula will work: =SUM(LARGE(OFFSET(K2,0,-11+LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6),1,12-LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6)),{2,3,4,5})) (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] Does that help? Ron |
#4
|
|||
|
|||
Thanks, That's excatly what I want, I'm getting a num error, but I have
to run the column out to R2, so I'm trying a few things and see if I can get it worked out Ron Coderre wrote: If I understand you correctly, you want to start at the latest score and count back to only include the last 6 scores, allowing for some golfers to skip a week. Consequently, you may go back 6 weeks to get 6 scores for some golfers, but go back 7 or 8 for others. Then eliminate the high and low from that period and sum the remaining 4 scores. If that is correct, I think this formula will work: =SUM(LARGE(OFFSET(K2,0,-11+LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6),1,12-LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6)),{2,3,4,5})) (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] Does that help? Ron |
#5
|
|||
|
|||
Hi!
I love golf! Just one question. What if a player doesn't have 6 scores? What if they only have 5 or even 4? This will do what YOU ASKED FOR. Assume the scores are in the range B2:R2. That's a total of 17 weeks. T2 = formula for Total Entered with the key combo of CTRL,SHIFT,ENTER: =SUM(LARGE(R2:INDEX(A2:R2,LARGE(IF(A2:R2<"",COLUM N(A:R)),6)),{2,3,4,5})) If you want the average: (also array entered) =AVERAGE(LARGE(R2:INDEX(A2:R2,LARGE(IF(A2:R2<"",C OLUMN(A:R)),6)),{2,3,4,5})) If you think that's difficult, you should try to calculate handicaps STRICTLY following the USGA guidelines! Yoi! Here's a sample file that you might find useful: http://www.xl-logic.com/pages/formulas.html Scroll down to item 27. Biff "TBD" wrote in message ... Thanks, That's excatly what I want, I'm getting a num error, but I have to run the column out to R2, so I'm trying a few things and see if I can get it worked out Ron Coderre wrote: If I understand you correctly, you want to start at the latest score and count back to only include the last 6 scores, allowing for some golfers to skip a week. Consequently, you may go back 6 weeks to get 6 scores for some golfers, but go back 7 or 8 for others. Then eliminate the high and low from that period and sum the remaining 4 scores. If that is correct, I think this formula will work: =SUM(LARGE(OFFSET(K2,0,-11+LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6),1,12-LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6)),{2,3,4,5})) (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] Does that help? Ron |
#6
|
|||
|
|||
Thanks to you all, this will give me some things to work with.
If a player only has 3 scores then we use 80% handicap for the first 4 weeks. Those players left over from the year before, use last years handicap, and continue from the prior year. I guess I could do that by adding 4 weeks at the beginning and just filling in those scores. Biff wrote: Hi! I love golf! Just one question. What if a player doesn't have 6 scores? What if they only have 5 or even 4? This will do what YOU ASKED FOR. Assume the scores are in the range B2:R2. That's a total of 17 weeks. T2 = formula for Total Entered with the key combo of CTRL,SHIFT,ENTER: =SUM(LARGE(R2:INDEX(A2:R2,LARGE(IF(A2:R2<"",COLUM N(A:R)),6)),{2,3,4,5})) If you want the average: (also array entered) =AVERAGE(LARGE(R2:INDEX(A2:R2,LARGE(IF(A2:R2<"",C OLUMN(A:R)),6)),{2,3,4,5})) If you think that's difficult, you should try to calculate handicaps STRICTLY following the USGA guidelines! Yoi! Here's a sample file that you might find useful: http://www.xl-logic.com/pages/formulas.html Scroll down to item 27. Biff "TBD" wrote in message ... Thanks, That's excatly what I want, I'm getting a num error, but I have to run the column out to R2, so I'm trying a few things and see if I can get it worked out Ron Coderre wrote: If I understand you correctly, you want to start at the latest score and count back to only include the last 6 scores, allowing for some golfers to skip a week. Consequently, you may go back 6 weeks to get 6 scores for some golfers, but go back 7 or 8 for others. Then eliminate the high and low from that period and sum the remaining 4 scores. If that is correct, I think this formula will work: =SUM(LARGE(OFFSET(K2,0,-11+LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6),1,12-LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6)),{2,3,4,5})) (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] Does that help? Ron |
#7
|
|||
|
|||
Biff,
Oh yes, I wouldn't even try the USGA handicap, then your into the scenarios of best 5 of 10 till you get to best 10 of 20 against the slope or course rating. Probably easier if you always are on the same course, but if you go to different ones it would be a nightmare. Biff wrote: Hi! I love golf! Just one question. What if a player doesn't have 6 scores? What if they only have 5 or even 4? This will do what YOU ASKED FOR. Assume the scores are in the range B2:R2. That's a total of 17 weeks. T2 = formula for Total Entered with the key combo of CTRL,SHIFT,ENTER: =SUM(LARGE(R2:INDEX(A2:R2,LARGE(IF(A2:R2<"",COLUM N(A:R)),6)),{2,3,4,5})) If you want the average: (also array entered) =AVERAGE(LARGE(R2:INDEX(A2:R2,LARGE(IF(A2:R2<"",C OLUMN(A:R)),6)),{2,3,4,5})) If you think that's difficult, you should try to calculate handicaps STRICTLY following the USGA guidelines! Yoi! Here's a sample file that you might find useful: http://www.xl-logic.com/pages/formulas.html Scroll down to item 27. Biff "TBD" wrote in message ... Thanks, That's excatly what I want, I'm getting a num error, but I have to run the column out to R2, so I'm trying a few things and see if I can get it worked out Ron Coderre wrote: If I understand you correctly, you want to start at the latest score and count back to only include the last 6 scores, allowing for some golfers to skip a week. Consequently, you may go back 6 weeks to get 6 scores for some golfers, but go back 7 or 8 for others. Then eliminate the high and low from that period and sum the remaining 4 scores. If that is correct, I think this formula will work: =SUM(LARGE(OFFSET(K2,0,-11+LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6),1,12-LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6)),{2,3,4,5})) (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] Does that help? Ron |
#8
|
|||
|
|||
Hi Ron,
The formula you sent me works great as is, however when I made modifications to the sheet it somehow went awry. The columns I used were the team # in B4, team name in C4, the 16 weeks in D4:R:4 and the total in S4. The modifications I made were to change yours to reflect Row 4. =SUM(LARGE(OFFSET(R4,0,-16+LARGE(IF(B4:R4<"",COLUMN(B4:R4)),6),1,17-LARGE(IF(B4:R4<"",COLUMN(B4:R4)),6)),{2,3,4,5})) I assumed R4 was to define the last week (16), and the -16 to reflect how many columns to use and 17-, I'm not sure off. Using the above formula it seems to pull the last 6 numbers, but not eliminate the high and low score. Ron Coderre wrote: If I understand you correctly, you want to start at the latest score and count back to only include the last 6 scores, allowing for some golfers to skip a week. Consequently, you may go back 6 weeks to get 6 scores for some golfers, but go back 7 or 8 for others. Then eliminate the high and low from that period and sum the remaining 4 scores. If that is correct, I think this formula will work: =SUM(LARGE(OFFSET(K2,0,-11+LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6),1,12-LARGE(IF(B2:K2<"",COLUMN(B2:K2)),6)),{2,3,4,5})) (Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] Does that help? Ron |
#9
|
|||
|
|||
Try this variation in Cell S4:
=SUM(LARGE(OFFSET(R4,0,-18+LARGE(IF(D4:R4<"",COLUMN(D4:R4)),6),1,19-LARGE(IF(D4:R4<"",COLUMN(D4:R4)),6)),{2,3,4,5})) (remember to commit that array formula by pressing [Ctrl]+[Shift]+[Enter]) Does that work for you? Ron "TBD" wrote: Hi Ron, The formula you sent me works great as is, however when I made modifications to the sheet it somehow went awry. The columns I used were the team # in B4, team name in C4, the 16 weeks in D4:R:4 and the total in S4. The modifications I made were to change yours to reflect Row 4. =SUM(LARGE(OFFSET(R4,0,-16+LARGE(IF(B4:R4<"",COLUMN(B4:R4)),6),1,17-LARGE(IF(B4:R4<"",COLUMN(B4:R4)),6)),{2,3,4,5})) I assumed R4 was to define the last week (16), and the -16 to reflect how many columns to use and 17-, I'm not sure off. Using the above formula it seems to pull the last 6 numbers, but not eliminate the high and low score. |
#10
|
|||
|
|||
One more thing.....
While my formula works, I didn't continue to refine it to it's simplest form. Biff did and I'd recommend his response (less moving parts). Here it is adjusted: Cell S4: =SUM(LARGE(R4:INDEX(A4:R4,LARGE(IF(A4:R4<"",COLUM N(A:R)),6)),{2,3,4,5})) Remember to [Ctrl]+[Shift]+[Enter] Ron |
#11
|
|||
|
|||
Bingo! They both work, thanks Ron and Biff.
Ron Coderre wrote: One more thing..... While my formula works, I didn't continue to refine it to it's simplest form. Biff did and I'd recommend his response (less moving parts). Here it is adjusted: Cell S4: =SUM(LARGE(R4:INDEX(A4:R4,LARGE(IF(A4:R4<"",COLUM N(A:R)),6)),{2,3,4,5})) Remember to [Ctrl]+[Shift]+[Enter] Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |