Golfsheet function

[TBD]

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.

 

[Bob Phillips]

=SUM(LARGE(B2:Z2,{2,3}))

 

[Ron Coderre]

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

 

[TBD]

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

 

[Biff]

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<>"",COLUMN(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<>"",COLUMN(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

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

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

 

[TBD]

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.

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<>"",COLUMN(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<>"",COLUMN(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

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

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

 

[TBD]

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.

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<>"",COLUMN(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<>"",COLUMN(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

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

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

 

[TBD]

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]

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

 

[Ron Coderre]

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<>"",COLUMN(A:R)),6)),{2,3,4,5}))

Remember to [Ctrl]+[Shift]+[Enter]

Ron

 

[TBD]

Bingo! They both work, thanks Ron and Biff.