Configuring EXCEL spreadsheet for a men's golf association BLITZ format #2

Anonymous
2017-11-08T17:38:25+00:00

I need another formula that take the individual's score on a hole, converts it to a STABLEFORD format (ex: DOUBLE BOGEY = 0 pts; BOGEY = 1 pt; PAR = 2 pts; BIRDIE = 3 pts; EAGLE = 4 pts; DOUBLE EAGLE = 8 pts).  So if an individual shoots a "4" on a PAR 4 hole, he would get "2 pts" for his team.

So the formula would have to identify the following(all within the same formula because the 3 or 4 team members could shoot different scores:

(PAR = required total number of strokes for that hole)

  • 2 below PAR = 4 pts
  • 1 below PAR = 3 pts

EVEN PAR = 2 pts

+1 over PAR = 1 pt

+2 over PAR = 0 pts

EXAMPLE on spreadsheet:

TEAM #1               PAR (4)           TM PTS

Player A                   4                        6

Player B                   3                        7

Player C                   5                        1

Player D                  6                         0

Formula needs to populate the "TM PTS" cells with the STABLEFORD scores established from the PAR cells score

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2017-11-09T16:07:53+00:00

You could use a formula like this for column C, where the scores are given in rows 3 to, let's say, 20, and names are in column A.

=IF(COUNTIF(C3:C20,MIN(C3:C20))=1,INDEX($A3:$A20,MATCH(MIN(C3:C20),C3:C20,False),"")

If two people shoot the same lowest score the cell will remain blank. IF you have more rows (or fewer) adjust the 20 in all places.

You can copy this formula to the other 17 columns to identify the best player.

As for the low net, is that adjusted for handicap, or is it on a straight lowest strokes basis? If you have a column where the net is summed for each player, then the formula above will give the name of the lowest based on that column.

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2017-11-08T20:40:18+00:00

Either this:

=144-SUM(A2:A4)/2

or

=(144-SUM(A2:A4))/2

depends on what you divide by two - the sum of the team handicap, which is then subtracted from 144, or 144 minus  the sum of the team handicap.

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2017-11-08T20:19:16+00:00

Cell D8 is the rollup for the hole for the 4 team members:

=IF(D4-D$1>=2,0,IF(D4-D$1<=-3,8,D$1-D4+2))+IF(D5-D$1>=2,0,IF(D5-D$1<=-3,8,D$1-D5+2))+IF(D6-D$1>=2,0,IF(D6-D$1<=-3,8,D$1-D6+2))+IF(D7-D$1>=2,0,IF(D7-D$1<=-3,8,D$1-D7+2))

Which is copied to the right for the other 17 holes (E8:U8), and B8 just has

=SUM(D8:U8)

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2017-11-08T19:21:48+00:00

Here's a file with a working formula and layout:

https://1drv.ms/x/s!AsKdy7Nfg\_FbgmqX3eyOEAhX6kaF

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2017-11-08T17:54:08+00:00

If you have different pars on different holes, then you need to have a column for those 18 values.  

Let's say that par for the hole 1 is in B2, hole 2 in C2 (and so on across the row) and the first player's scores start in cell B3 and continue across Row 3, second player is in row 4, and so on...

This formula will give the points for the first player on hole 1, and can be copied across to match all 18 holes, and down to match all your players, converting all the scores to points:

=IF(B3-B$2>=2,0,IF(B3-B$2<=-3,8,B$2-B3+2))

Was this answer helpful?

1 person found this answer helpful.
0 comments

56 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-11-16T20:32:31+00:00

    let's see if I can make it less confusing.

    In my "TEAM SCORING" box I have the formula correct for 1st, 2nd, and 3rd place on FRONT and BACK and I have the 1st place team identified by name TEAM5 on FRONT and TEAM 3 on BACK.

    Challenge: 

    1. I don't need the score attached to the Team Name. I tried taking that out of the formula but then it messes up the winning team name
    2. when I try to put ( ,2 or ,3) at the end of the formula to identify 2nd and 3rd place team names that are associated with the 2nd and 3rd place scores it continues to show the name of the 1st place team

    For the "INDIVIDUAL SKINS" box, there is only one winner regardless if he is an OPEN or a SENIOR player.

    Challenge:

    1) how to identify if the low score on that hole "PAYED" to participate in the INDIVIDUAL SKINS pot. 

    2) I added a column(s) at the end of each row (associated with individual names and scores) with a "Y" for paid and a "N" for not paid. "Y" equals they are participating for the INDIVIDUAL SKINS. An "N" would eliminate them from winning.

    1. How to link the individual name with the "below par" score for that particular hole and link with the "Y" or "N" and determine who wins the INDIVIDUAL skins and continue to place the name in the INDIVIDUAL SKINS cells associated with holes

    The 9/9 LOW NET OPEN FRONT & BACK and the 18 LOW NET OPEN and the same for the SENIORS

    1. I also need the "Y" or "N" in the cells identified 9/9 and 18 at the end of each row associated with the names associated with the lowest scores and placed where they currently are that determines if the winners paid to participate or not

    basically an "N" would eliminate that person from competing in the Individual skins and/or the 9/9 & 18

    Was this answer helpful?

    0 comments