golf handicap spreadsheet

Preview
golf handicap spreadsheet
hi i need some help from someone who knows excel alot better than me which isnt hard really as im self taught and i know i have a long way to go

this is what i need

Category score nett score par handicap un hand over/under

4 96 68 70 28 28 -2



Handicap Category Buffer Zone Above Buffer Below Buffer

0 to 5 1 1 0.1 0.1
6 to 12 2 2 0.1 0.2
13 to 20 3 3 0.1 0.3
21 to 28 4 4 0.1 0.4
29 to 36 5 5 0.1 0.5

ok what i would like to do somehow is the following:- i need to use a formula so that in the handicap box on the top line somehow matches the category number in the top line with the category line in the bottom box...so if i have category 4 at the top i need it to be matching the category 4 line in the bottom box......then i need to check the box that has -2 in at the moment to see if thats a negative number then i need to use the below buffer figure....and if its a positive number i need to use the above buffer

i realise that this might not be explained very well so if anyone would like me to send them the spreadsheet then leave me your mail and i can send you the sheet with hopefully some better explanations...

your help would be greatly appreciated from me and my fellow golf members

Please can you attach a sample workbook here?

you can attach a file here so long as its below 100kb.

Heres what it looks to me like you're trying to do. I would assume you are trying to categorize handicaps, like for a league. So all the 21-28's are considered 4's, and if its above 28.4 or below 21.1 then you move to the next category up/down, respectively...

I think that should be able to be done with a simple IF function.. Unless, you are trying to do something more complex.

im not trying to run a league.im actually just trying to keep an update on people's handicaps....

now a friend of mine started the spreadsheet and it seems to work fine if a player scores under his handicap which gives a negative number but if a player goes over his handicap and a positive number comes in then it doesnt seem to work so i cant seem to see why it doesnt work correctly...

ideally i would prefer to start a fresh so i can see what im doing with a bit of help

i have attached the spreadsheet in its current form

basically it's this

each handicap branding has a category of 1,2,3,4 and 5 which relates to
0to5(1),6to12(2),13to20(3),21to28(4) and 29to36(5)...
if a player goes over his handicap then he should receive 0.1 per shot over his handicap and that is the same for all catogories
if player goes under his handicap then that is slightly different depending on which bracker you come under..
u would lose 0.1 per shot under if you are category 1
0.2 for category 2
0.3 for category 3
and so on until 0.5 for category 5

so the spreadsheet should be able to determine which category you come under and how many shots to come off your handicap..

pretty simple but not so simple for me with spreadsheets

I'm a little confused. For the two completed rows, what would you like for the final output - is it just a single number in column J? If so, could you indicate what those values should be so that we are completely clear. As you can probably tell I'm not much of a golfer.

j should be the handicap plus how ever many points the handicap should change up or down

so if the handicap started and 27 and the player went to over his handicap it shoould be 2 * 0.1 =0.2 + 27 = 27.2

i think g then looks at this number and rounds it down if it is .4 or lower and rounds it up if it is .5 or higher

im not quite sure why he has so many colums in this spreadsheet but im pretty sure there doesnt need to be as many as there is

I should show how many shots over or under the handicap is

j should wort out the new handicap

g should look at j and then correct it to the nearest integer

hope that helps a bit more

Still not sure if I have the right end of the stick but you don't seem too sure yourself! See if this comes close:

=IF(E5>F5,G5+((E5-F5)*0.1),G5+(INDEX($Q$7:$T$11,MATCH(C5,$Q$7:$Q$11,0),4))*(E5-F5))

fantastic!!!!!!

u might not have understood but u have done the job

it is now working like a dream and it doesnt need all those other columns either so the sheet will be alot smaller now and more managable

thank you so much

Glad to hear that we fumbled our way there!

another quick question please

i have found out today that i am not using the correct handicapping factors

so is it possible to slightly change the formula you have given me

this is what needs to be done

instead of if you have a positive number in the over/under par and that number being multiplied by 0.1 what i need to do is regardless of what positive number above zero is in that cell that 0.1 is the only number added to the handicap score

so at the moment what happens

if u have category 4 you lose 0.4 per -1 shot and if you have 0.1 per +1 shot
meaning if you have -2 = 2*0.4 make you lose 0.8 from your handicap
and +19 = 19*0.1 giving you 1.9 onto your handicap

i need the negative part working the way it does already but if you have a positive figure then only 0.1 is added to the handicap

thanks

hopefully you will be able to help me here but in the meantime i will play with the formula to see if i can get the desired result

thanks

Try this formula for J5 copied down

=IF(I5>0,1,I5*C5)/10+H5

yes that works

thanx