Hi I am trying to create a formula I can use to calculate golf handicaps for a school project. Basically the formula is:

Net Score = Gross Score (Actual number of shots) - Current handicap

Standard Scratch Score - Net Score = y

New handicap = Current handicap - (y*z)

This is where my problem comes in. The categories work as follow:

Cat Handicap Range Change (z)
1 00.0 - 05.4 0.1
2 05.5 - 12.4 0.2
3 12.5 - 20.4 0.3
4 20.5 - 28.0 0.4

For Example:

Current Handicap = 11.2
Nett Score = -7
Handicap Change = 7 x 0.2 = 1.4
New Handicap = 11.2-1.4 = 9.8

Another problem arises when a handicap changes and causes it to fall into a lower category.

If a player with an Exact Handicap of 21.2 returns a score with Nett Differential of -6 (i.e. 6 strokes below his Playing Handicap of 21) his Exact Handicap is reduced as follows:

21.2 - (2 x 0.4) = 20.4
20.4 - (4 x 0.3) = 19.2

This shows that a players handicap is reduced until he reaches the lower category and then the remaining reduction is now made by the new category change.

Hope this is enough information and it is not to complicated for anybody to follow. If anybody needs any more information or anthing clarified I would be happy to assist. Any help on the subject would be greatly appreciated :D.

As it's a school project I'm not going to provide an answer.

Post your progress so far and which parts in particular you have are having difficulty with and if your lucky some king people will point you in the right direction.

Can you attach your workbook so I can take a look at it, It would be helpful if on the workbook you highlighted what cells need to be calculated with what cells etc..

Well my excel workbook is a bit rough. I have used a basic formula that kind of works but requires a lot of user input which is not good. My basic formula is:

=B2-((C10-C12)*0.1)

B2 = Current handicap
C10 = SSS
C12 = Net score
and it is multiplied by 0.1 because this is the category that my handicap of 3 falls in. It works out then that my new handicap is 2.7 is correct. However this is not good because this requires the user to find the correct category for their handicap and put this in its place and the situation is more complicated when it moves between 2 categories.

I want to create a formula that states that is B2 (current handicap) is >= 0 and =<5.4 then multiply it by 0.1 and so on for each category. I also realise that i will probably need to create a cell that displays the category so that i can create a formula with F10 for example in place of the 0.1 so it will be more user friendly.

I want to tackle this one step at a time to make it easier rather than trying to overcomplicate things right from the start.

I feel I have the basic grasp of the formula but i think the way i type it is wrong which would be why it is not working.

Thanks

Hi I have updated my Excel file and made it look a bit tidier, well in my opinion at least.

I have uploaded it to my SkyDrive if anybodys wants to view it who could assist me, the link is as follows:

http://cid-511c724bf1e03795.skydrive.live.com/browse.aspx/GSR (http://cid-511c724bf1e03795.skydrive.live.com/browse.aspx/GSR)

I have added a section to record statistics and linked cells to give a list of users and their current handicaps that update automatically. I have a few more queries that I may need assistance on later which i have only realised lately, but i want to nail the correct formula calculation first.

I have created a tab with a table which contains the max and min barriers for the handicap categories, the category and the change if you are within these parameters. My aim is to create a formula that will say will say the following:

if (Min parameter >= My Handicap <= Max parameter) then handicap category will be X.

I am sure that this should be understandable but i feel that by giving you my actual spreadsheet it may be easier to understand.

I am currently creating this in excel but I am looking into making a programe in access that will calculate this but thought it would be easier to get a better understanding in excel first and it will give me something to fall back on. Does anybody agree or disagree with this and what is anybodys opinion of whether it is viable to use access for this type of work?

Any help is appreciated again and thank you.

Hi I have updated my Excel file and made it look a bit tidier, well in my opinion at least.

I have uploaded it to my SkyDrive if anybodys wants to view it who could assist me, the link is as follows:

http://cid-511c724bf1e03795.skydrive.live.com/browse.aspx/GSR (http://cid-511c724bf1e03795.skydrive.live.com/browse.aspx/GSR)

I have added a section to record statistics and linked cells to give a list of users and their current handicaps that update automatically. I have a few more queries that I may need assistance on later which i have only realised lately, but i want to nail the correct formula calculation first.

I have created a tab with a table which contains the max and min barriers for the handicap categories, the category and the change if you are within these parameters. My aim is to create a formula that will say will say the following:

if (Min parameter >= My Handicap <= Max parameter) then handicap category will be X.

I am sure that this should be understandable but i feel that by giving you my actual spreadsheet it may be easier to understand.

I am currently creating this in excel but I am looking into making a programe in access that will calculate this but thought it would be easier to get a better understanding in excel first and it will give me something to fall back on. Does anybody agree or disagree with this and what is anybodys opinion of whether it is viable to use access for this type of work?

Any help is appreciated again and thank you.

Access is firstly a database tool and secondly a calculation tool, whereas Excel is firstly a calculation tool and very distantly second a database tool.

Therefore I would suggest that you keep to Excel, I am not saying that you will not be able to achieve what you want in Access, because you will, the questions is really why would you want to.

Just my opinion.

Well simply because I could create a database with all players and handicaps and also the details of courses played so that it would stop you having to enter the info each time. Just out of curiosity im learning Access 2007 with a book, Microsoft Access 2007 Bible. Has anybody else read this and is it any good or are there any other books/tutorials or the like that anybody could recommend. Also I am still having no luck on this formula so if anybody has any assistance it would be appreciated, i feel i no what to do in theory but just do not know the correct wording if you get me.

Thanks