Forum Discussion
EXCEL FORMULA
I am working on a scoring web sheet for our golf club. I would like to enter the scores by hole and get the following results: If par is 4 and the player ,makes a 3, the player gets 5 points. If the player makes 4, 3 points, 5 then 1 point 6 or greater then 0. Par could be 3, 4, or 5 but I would like the formula the same.
Par
3 - 2 =5 3=3 4=1 5=0
The same result for any par.
I could really use some advice on how to come up with the correct formulas.
21 Replies
CURTIS775 Perhaps the attached schedule does what you ask for.
Created a table with points given for every score in relation to par. Par -1 = 5 pts, Par = 3 pts, Par +1 = 1 pt, Par +2 = 0 pts.
Then, if your Excel supports XLOOKUP you could calculate total points for the entire round in one single formula (in D28). But, perhaps you also want to show each player's points per hole. If XLOOKUP is not recognised by your Excel, you can use HLOOKUP. In both cases I have used named ranges to keep the formulae easier to read/maintain.
Note: You did not indicate how many points a player gets if he/she scores better then 1 under par on a hole. In the current set-up it will result in 0 or #NA!, but that can easily be fixed once you determine what the result should be.
Riny_van_Eekelen CURTIS775 wouldn't you be able to use a formula like:
=MAX(3 - 2 * (SCORE - PAR), 0)
That would give you:
Pts Score
0 >=2 over par (double bogie)
1 1 over par (bogie)
3 par
5 1 under par (birdie)
and in this case even better scores would follow as:
7 2 under par (eagle)
9 3 under par (double eagle)
but CURTIS775 needs to say if these would be correct scores or not.
Hi CURTIS775
I'm not sure that something like this can even be done with a simple formula, because there doesn't seem to be any logic that leads from the par to the points with the player results in the mix.
Your best approach might be to create a lookup table with columns for par, play and points. Enter all the possible combinations for par and play into the first two columns, then enter the points for each of these combinations. Then you can use a lookup formula to look up the combination of par and play and return the results.
Assuming that there will be only unique combinations of Par and Play, the formula could be something like this scenario. The lookup table with Par, Play and Points is in columns A, B and C. I've only entered two rows, you will need to add the other ones.
=SUMIFS(C:C,A:A,E2,B:B,F2)
In the table, will I need to set up for every combination? such as if the score on a par 3 hole is greater than 4 the points will be 0. Could you show me that formula?
Thank you for your help
CURTIS775
I have to do standard deviation, coefficient of variation and medium in excel but dont know how formula is for that? Tell me please. I dont know how to ask by only my post here not to reply? So that why I am writing here.
