handicap system for golf league

**i3putt** · 03-18-2007, 10:14 AM

Just starting to use excel and have a problem I hope someone can solve. Trying to set up handicap system for golf league. Column A is players. Column B shows average of last 5 rounds played and rounded to nearest whole number. column C shows the same without rounding. column D and beyond show scores with column D being the most recent. Have this all this working well with one problem. Each week I insert new column D, enter scores, and everything is recalculated. How do I deal with absent players? Is there a way to enter perhaps "A" for absent and excel will know to count the last 5 cells with numerical values only? Somebody please help. I am pretty much a computer moron so please keep as simple as possible. Any help is appreciated. Thanks.

**oldchippy** · 03-18-2007, 03:39 PM

Hello there,

As no one has picked this up yet, rather than me trying to work out what you are trying to do, if you can provide a small zipped sample of your data together with a explanation of what you are trying to do, then I’m sure it will be easier for me (or someone else) to help you.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this to your post.

**i3putt** · 03-19-2007, 04:03 AM

Originally Posted by i3putt

Just starting to use excel and have a problem I hope someone can solve. Trying to set up handicap system for golf league. Column A is players. Column B shows average of last 5 rounds played and rounded to nearest whole number. column C shows the same without rounding. column D and beyond show scores with column D being the most recent. Have this all this working well with one problem. Each week I insert new column D, enter scores, and everything is recalculated. How do I deal with absent players? Is there a way to enter perhaps "A" for absent and excel will know to count the last 5 cells with numerical values only? Somebody please help. I am pretty much a computer moron so please keep as simple as possible. Any help is appreciated. Thanks.

Tried zipping file and am having no luck. Told you I'm a computer moron. I guess what I'm trying to do in simpler terms is average the last 5 scores entered regardless of what columns they appear in or without naming a specific range. Can this be done? Appreciate any help.

**PeterB** · 03-19-2007, 07:34 AM

With the layout below and the equations listed I got the following results:

______ColA_ColB_ColC_ColD_ColE_ColF_ColG_ColH_ColI_ColJ_ColK
Row1__Plyer_RAv_Avg_Scor_Scor_Scor_Scor_Scor_Scor_Scor_Scor
Row2__Pete_80__79.8__78__82____0___76___80___0___83___89
Row3____________7____1____2___2____3____4___4____5____6

Col B Row 2 equation - =ROUND(C3,0)
Col C Row 2 equation - =SUMIF(OFFSET(C4,-1,1,1,C4),"<>0",OFFSET(C4,-1,1,1,C4))/5
Col C Row 3 equation - =MATCH(5,D4:P4,0)
Col D Row 3 equation - =COUNTIF(D3,"<>0")
Copy equation in Col D Row 3 across Row 3 Cols E, F onwards.

Repeat Rows 2 and 3 for other players.
Hide Rows 3, 5, etc to make the sheet look a bit tidier.

Sorry, something I did not make clear when I first posted this -

Place a 0 in any cell where you wish to identify a player as having not played. Firstly,in golf, you cannot score 0 for a round. Secondly, using 0 allows easy detection of the fact that a round has not been played, and allows the equations to sum the correct cells as part of the averaging process.

You cannot simply insert a new column, but you can Insert Cells, Shift Right for all the Players rows, then copy the equations in the intermediate rows further to the right (although this latter would only be necessary if a player is absent many times).

**daddylonglegs** · 03-19-2007, 10:06 AM

A little convoluted but you could use this formula in B2 copied down

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER (so that curly braces appear around the formula in formula bar)

This will give you a rounded average of the last 5 scores and won't be disrupted by additional columns added at column D.

If there are no scores it shows a blank, if there are less than 5 it averages those

**oldchippy** · 03-19-2007, 10:30 AM

Originally Posted by i3putt

Tried zipping file and am having no luck. Told you I'm a computer moron. I guess what I'm trying to do in simpler terms is average the last 5 scores entered regardless of what columns they appear in or without naming a specific range. Can this be done? Appreciate any help.

I'm sorry that I'm not into golf, but maybe this will help. It will allow you to insert a column at D and still average the scores for columns D:H each time.

**PeterB** · 03-20-2007, 08:05 AM

Hi OldChippy,

How are you adjusting the averaging process to account for players not having played a round? ...

I3Putt needs to average the first five columns (starting from Column D) that contain a valid score, not simply Columns D to H.

PeterB

**PeterB** · 03-20-2007, 08:24 AM

DaddyLongLegs

Like the formula. However, using the data I posted with my solution I have tried a couple of tests (using a blank cell for no score, using 0 for no score and using A for no score) and the average does not appear to be worked out correctly.

PeterB

**daddylonglegs** · 03-20-2007, 04:30 PM

As the formula stands it will include zeroes in any average, but should work OK if you use blanks or any text, assuming the formula is confirmed with CTRL+SHIFT+ENTER

**PeterB** · 03-21-2007, 07:23 AM

Got it! - Nice one. :-))

**oldchippy** · 03-21-2007, 08:27 AM

Originally Posted by daddylonglegs

As the formula stands it will include zeroes in any average, but should work OK if you use blanks or any text, assuming the formula is confirmed with CTRL+SHIFT+ENTER

That's brilliant, but I can't get my little brainbox around that, can you explain how it works it out?

**i3putt** · 03-23-2007, 05:35 PM

Originally Posted by daddylonglegs

A little convoluted but you could use this formula in B2 copied down

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER (so that curly braces appear around the formula in formula bar)

This will give you a rounded average of the last 5 scores and won't be disrupted by additional columns added at column D.

If there are no scores it shows a blank, if there are less than 5 it averages those

Just got time to try your formula and after correcting all my errors it works perfectly. You my good friend are a genius. I hate to even ask another favor of you but here goes. How do I display this number witout rounding it off? That is carrying it to the first decimal point. Can I tweak this formula? Again, thank you so much for your help. It is GREATLY appreciated.

**daddylonglegs** · 03-23-2007, 06:10 PM

If you want the result rounded to one decimal place rather than none change the last zero in the formula to a 1

**i3putt** · 03-25-2007, 06:36 AM

Originally Posted by daddylonglegs

If you want the result rounded to one decimal place rather than none change the last zero in the formula to a 1

Everything working great. Formula in column B and scores in decending order starting in column D. Ran numerous tests and everything works perfectly. Now, to make sheet more user friendly, would like to shift all 1 cloumn to the right. That is, formula in column C and scores starting in column E. As I'm sure you know inserting column screws everything up. Tried tweaking formula to get final results in column C but am having no luck. Can you help one more time? Thanks again.

**i3putt** · 03-31-2007, 12:00 PM

Originally Posted by i3putt

Everything working great. Formula in column B and scores in decending order starting in column D. Ran numerous tests and everything works perfectly. Now, to make sheet more user friendly, would like to shift all 1 cloumn to the right. That is, formula in column C and scores starting in column E. As I'm sure you know inserting column screws everything up. Tried tweaking formula to get final results in column C but am having no luck. Can you help one more time? Thanks again.

Could really use your help again with this one DADDYLONGLEGS. Been banging my head against the wall for the last 5 hours trying to figure out how to move this formula. Thanks.

**daddylonglegs** · 03-31-2007, 06:45 PM

I really prefer formulas which can withstand the deletion/insertion of columns without exploding but this is a little trickier.

The basic rule here is that the 253s in the formula represent the number of columns from column C to the end of the spreadsheet - and the 3 is the reverse, number of columns from the start, so to re-locate the formula to C2, counting columns from E the 253s become 252s and the 3 becomes a 4, i.e.

Please Login or Register  to view this content.

as before, confirmed with CTRL+SHIFT+ENTER

There's probably a way to make this more robust and/or shorter....but I haven't worked that out so far

**Ray Haynes** · 10-28-2010, 10:03 AM

Since none of my 3 posts are shown anymore - I guess I must have done something wrong. I did read and thought I was following the rules - however, if I made a mistake, I am sorry. However - I would like to get some help on those problems.

**DonkeyOte** · 10-28-2010, 10:25 AM

@Ray Haynes, the Moderators kindly moved your posts to your own thread http://www.excelforum.com/excel-gene...ap-system.html

Posting your own questions in the thread of another member is not permitted (per the Forum Rules you read previously)

handicap system for golf league

LinkBack

Thread Tools

Display

handicap system for golf league

Re: handicap system for golf league

Re: handicap system for golf league

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions