Excel 2007 formula query to calculate golf handicaps

Hi all,

I'm trying to apply a formula to my Golf scoring spreadsheet that will automatically calculate the handicap cut or increase someone will get when they post in their score.

The first part I’d like help with is on the 'Home' sheet where a person inputs their current exact handicap. I would like the system to automatically fill in the 'category' cell with what category the player is in.

If the player enters 7.5 into the exact handicap cell it already (thanks to help from here) rounds the handicap to a playing handicap of 8, but I’d now like to also fill in the category cell automatically.

For the auto-category part the category’s are split into the handicaps shown below:

Category Handicap
1 0.1 - 5.4
2 5.5 - 12.4
3 12.5 - 20.4
4 20.5 - 28.4
5 28.5 and above

I'd like the category cell to auto show what category the player is in based on what exact handicap they enter above.

Once the category bit is done I'd like to move to 'Sheet3' where players input their score. At the moment I have 5 cells:

Total gross score from the round
Course SSS
Old exact handicap
New exact handicap
Change

The cells that will update automatically are 'New exact handicap' and 'change'. To calculate the 'New exact handicap' so far I have the following formula running:

=AB10-((Home!$C$7+ROUND(AB10,0)-Z10)-(Home!$C$7-AA10))*(Home!$C$9/10)

This is based on my inputted handicap of 7.5 (or 8 being the playing handicap) meaning I am a category 2 player. Category 2 players will loose 0.2 off their handicap for each shot under the SSS (set at the time of entering your score for the round – as this can move up and down based on how high or low the competition scores were that day).

AB11 is the course SSS, Z11 is the gross score I shot in the round, Home!C7 is the Par of the course (70) and C9 is the category I am in.

At the moment this appears to work ok, apart from the part where if I shoot inside my buffer zone (which is 2 - the same as my category) which means I don't get a handicap change up or down, and the part where if I shoot over the buffer zone where I get the standard 0.1 added to my handicap.
So in short, I’d like my new exact handicap to be automatically calculated based on what score I put in, and the change be it up or down displayed.

Those are the two bits I am struggling with. I have no idea how they should be implemented (in the formula or with some actual code?) – Note, I am a total noob / idiot at both ideas, so baby steps would be appreciated.

Help greatly appreciated (spreadsheet so far is attached).

=lookup(a1,{0,0.1,5.5,12.5,20.5,28.5},{0,1,2,3,4,5})

i dont talk "golf" lol
just give examples of values and what should happen!
along the lines of if a1 = 272 and b1 = x then i should get y