Re: Recording the lowest value shown in a cell that contains a value that is overwrit
Well thanks for that and thanks for looking at the problem - just having the chance to chew it over with someone helped me find a solution - all the best
Ashbo
Re: Recording the lowest value shown in a cell that contains a value that is overwrit
Well thanks for that and thanks for looking at the problem - just having the chance to chew it over with someone helped me find a solution - all the best
Ashbo
Re: Recording the lowest value shown in a cell that contains a value that is overwrit
Hi
I have attached a new version of the sample spreadsheet - this seems to work - and it seems almost too simple! Can you see any problems with column I continuing to record the lowest score attained by each player?
Ashbo
Re: Recording the lowest value shown in a cell that contains a value that is overwrit
There are 3 of us that maintain the scores through the year on any one day any one of might agree to record the scores we always use the last published version of the spreadsheet to build the next one- the spreadsheet gets mailed around twice a week and all of the 50 plus players receive a copy. I therefore think that using VBA will not work out in my situation but I have a non-VBA idea that might work - I'll explain it in words:
Suppose in our mini table I use rows 22-27 to record the handicap acheived on that day by that player (i.e. the new number that appears in J2) -then as the year progresses will I not merely create a long series of numbers? Then in I2 (say) all I need is a formula that looks at that long series of numbers (say across row 22 from L22 to DD22) and only displays the lowest. Won't that work?
Re: Recording the lowest value shown in a cell that contains a value that is overwrit
I don't know anything about Visual Basic - so I guess I would prefer not to use VBA - but if you can reassure me that it will be simple to use and maintain I am willing to consider it.
Re: Recording the lowest value shown in a cell that contains a value that is overwrit
Hi and thanks for looking at the spreadsheet.
Yes the values in L13-Q13 can be positive - you will need to enter scores against several players to see this effect - the reason is explained below.
Maybe I can clarify things further for you. Each time a player plays a round a golf he returns a 'score'. Typically players score between 25 and 40 points. whatever their score on that day it is entered under that date and against their name. It is rather complicated to explain in full but if a player's score is more than 36 points his pot handicap (col J) will go down. If the player scores less than the overall average score plus 1 on that day his handicap will go up. The exact amount that the handicap alters is calculated in rows 13 - 19. If you enter 38 in cell L2 you will see that cell L13 returns a value of -1 and the players handicap is reduced by 1.0. If you also enter a value of 28 in cell L3 you will see that L14 returns a value of 0.5.
The next time a players turns up to play he plays off his new handicap and he returns a new score - this new score may result in his pot handicap being altered. Players that do not play on the next date have no score entered for that round. The lowest score I have seen recorded is 11 the highest 48.
There is one other wrinkle you might need to be aware of. Although the spreadsheet calculates the new handicap of each player playing that day - the winner on that day has the value returned in rows 13-19 decreased by an additonal 0.5. This is because the winner on the day loses an extra 0.5 from his handicap. Subtracting this extra 0.5 is currently done 'by hand' after the results have been entered - you will see that column C records the number of wins acheived by each player but I have never been able to automate this step. Please dont worry about this - when we subtract the extra 0.5 in row 13-19 this changes the value in column J - as long as the lowest value recorded in column I 'sees' this change and records it.
I hope this helps - please come back with more questions - I am very patient and greatly appreciate your help.
Ashbo
Re: Recording the lowest value shown in a cell that contains a value that is overwrit
vwankerl
Please see that I have now attached a very small sample file. To see how this part of the spreadsheet currently works just start entering values above 36 against the players names in columns showing the date. For example if you enter 38 into cell L2 you will see that Mike Allen's hanidcap goes down from 20 to 19. Now that value in cell J2 will change during the course of the year - sometimes twice a week - as the player records different scores from his round of golf. All I want to do in column I is to capture the lowest value that ever appears in J2 during the year. The value in J2 can also rise above the starting value - so players handicaps will fluctuate up and down during the year. All I want to do is have a formula in column I that records the lowest value that ever appears in the adjacent cell in column J.
Any help would be greatly appreciated.!
Tony
Hi
I run a golf handicapping system that utilises excel quite heavily to record a LOT data over the course of a season. In essence we have in excess of 50 players who play once or twice a week and I record their Stableford scores (dont worry that's a golfing phrase but it is basically how they performed that day) and adjust their golf handicap accordingly - all in XL.
So - a player may start the season with handicap of 20 but over the season this will vary up and down as each score is recorded. A player may well play in excess of 50 rounds in a season.
I currently record the player's starting handicap in one column and in the next column appears the freshly calculated new handicap after a new score has been entered. The column containing the new handicap overwrites the old handicap value each time it is recalculated.
All I want to do is to create a 3rd column that records the lowest handicap value attained during the season. So for each player I guess it should take the newly calculated handicap and decide if it is lowest it has 'seen' in that column and if it is record it.
Of course the easiest way for any clever person out there to help me is for me to send them a sample of the spreadsheet - let me know how to do that
Ashbo
Re: Limit A Returned Value To A Maximum
Derk
Thanks a lot - I was trying to use MAX ! I think I understand why it is MIN!
OK simple question but my earlier posts have established me as a dummy!:duh
Given this formula =(H2+(SUM(K53:DB53))) which simply takes an existing value and adds to it the sum of a series of values.
I am happy if the value returned is any number less than 28. However if the value returned from the above formula is >28 I want the number returned to be shown as 28.
Can somebody please modify the formula above so that this occurs?
Thanks
Ashbo
Re: Update Golf Handicaps According To Rules
shg
You are right I did grab the formula before you made that adjustment - which corrects both the problems I indentified earlier.
This works fine now - and I really appreciate your help.
The last thing I want to do now is that for most players, most of the time, they score abovce the average but below 36 - so the formula, correctly, returns the adjustment as 0.00
Is it possible to adjust the formula such that when the returned value is 0.00 nothing is placed in the cell. This would mean that only adjustments (both +ve and -ve) would be visible when inspecting the spreadsheet - making it easier to see what has happened.?
In any case - thanks again for your help
Ashbo[hr]*[/hr] Auto Merged Post;[dl]*[/dl]OK - doh! I just learned how to supress zero values!! Sorry to bother you.!
I think we are done - but don't worry I'll come back if there is a problem!
You are a top guy for helping me out
Cheers
Ashbo
Re: Update Golf Handicaps According To Rules
shg,
Nice one - I'm actually happier using a formula like this than the VBA UDF - even though I miraculously managed to spot the error!
Now - as for your formula it needs a couple of tweaks. Firstly - if there IS an average score that day but the player did not himself return a score (i.e. he did not play) - then the formula currently returns a value of 1.0 which means the player's handicap goes up by 1 and he didn't play! (Must be how it treats a 0 or blank score). So this needs to be fixed.
Secondly if there was no average score that day (e.g. suppose nobody played because the weather was too bad (not a problem in Texas I guess but in the UK we get stuff like snow and rain!)) then the formula returns an an error - well actually it returns '#VALUE!'.
Any chance you can adjust the formula to deal with these two eventualities?
Thanks again...
Ashbo
Re: Update Golf Handicaps According To Rules
Thanks Barryj but your reply refers to the attachment posted you Barryj which I haven't had a chance to look at yet! I was referring to the attachment posted by shg ....
Tony[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hey shg - I got it to work!
First time I opened your zip file I forgot to enable the macros - hence the problem with the data in the cells.
OK - thanks a lot I'm going to check what you did further but it looks good to me. Is there a way of saving this function permanently other than via the enable macros route?
Thanks again
Tony[hr]*[/hr] Auto Merged Post;[dl]*[/dl]shg - now I have examined the sample spreadsheet that you sent me it looks fine EXCEPT that in a few cases it seems to have added more than 1.0 to the Hcap - so I wonder whether the rules in the function for limiting the maximum points gained to 1.0 is working?
For example look at cells V30 and W30 - surely these should read 1.0 ?
Thanks again
Tony[hr]*[/hr] Auto Merged Post;[dl]*[/dl]shg
OK - I spotted the VBA error and edited it - Tony
Re: Update Golf Handicaps According To Rules
Hi shg
Thanks for your help so far - I think I see what you have done - basically defined a new function which is then applied.
I opened your attached file and again I can see what you have done or intended to do, but all the handicap stuff has not worked - for the Hcp Adj and Curr Hcap columns it says '#Name?' so I guess it is waiting for me to define the function ? If so how do I input /use the definition you kindly provided?
Maybe my Excel skills are not up to this - please explain it to me as if I were a dummy!
Thanks again
Tony
Re: Update Golf Handicaps According To Rules
Hi shg
See attached zip file - which has not been modified to show players handicaps. But you will see how this works. All updates are made to the data worksheet. I then use the other worksheets to manipulate and sort the data by hand to produce the league tables. (In fact a guy who is just about Excel illiterate does this so I have printed out a step-by -step guide for him to produce the tables).
The data files contain no handicap information at the moment - but they do contain and are updated with the scores (twice a week) and it these updates (scores) that need to be used to recalculate the players handicaps. All players have a handicap from the club at the start of the year - for our competition we take these handicaps and adjust them twice weekly to reflect players current form and performance. So each time a player plays his handicap could change. In essence if a player scores between about 29 and 36 pts his handicap will remain unchanged - scores higher or lower than this will generally either increase or decreae their handicap.
To your question -
No, points are Stableford points which is a legitmate golf scoring system. Basically if you played to your handicap - whatever it is you should get 36 points. If you play better than your handicap you will get more than 36 points and if you play worse you get less. If look at the data in the zip file you will see the ranges. Most players, in most rounds, score below their handicap - i.e. score fewer than 36 points. we only want to cut people's handicaps if they score above 36 and increase their hanidcap if they are scoring below the average for the round. (which will normally be between 28 and 31).
Hope this helps- and thanks for even considering helping me out !
Tony
Hi
I'd really appreciate some help from an Excel guru!
I run an already relatively complex set of spreadsheets for a 40+ group of golfers. I record their scores each week and update and print league tables throughout the year. See the attachment for a sample of the data sheet I update.
We have also introduced a handicap adjustment system which currently runs completely seperately. I'd like to now incorporate this into my current system but do not know how.
Let me explain how this needs to work.
1. At the start of the year every golfer has a handicap - a number between 0.0 and 28.0.
2. After a round of golf the player may need to have his handicap adjusted depending on his performance.
3. I'd like to automate this as much as possible
4. After a round of golf I need to adjust the players handicap as follows:(this is complicated)
a) If the player has scored more than 36 points, then for every point more than 36 he has scored I need to subtract 0.5 from his current handicap. So if a player with a handicap of 20 scores 40 points then his handicap will be reduced by (40-36)/2 = 2. So his new handicap = 18. [The MAXIMUM reduction allowed for any one round is 3 points]. Simple so far?
5. For all the players that played on that day I need to calculate the average score (easy)
6. For every player whose actual score is one point or more BELOW the average score then I need to INCREASE their handicap by 0.1 for every whole point less than the average up to a maximum of 1 point. So - a player with a handicap of 20 scores 22 points on a day when the average score is 28 points. His handicap will increase by: (28-22)*0.1 = 0.6. Note the max that can be added to the handicap is 1.0.
7. When you look at the very small data sample I have attached you can see that under each date that a round of golf is played, a players score is recorded. In an ideal world I would like to have another column next to the 'club handicap' column that is called 'new handicap' and when I enter a new score, this cell is automatically uodated. However until I have entered all the scores for that day the average cannot be calculated so the new handicap cannot be adjusted immediately - I need a 'calculate new handicaps now' button I guess!
8. This system runs throughout the year (I can send a whole year's spreadhseet if you like!) and most players have between 20 and 70 rounds of golf.
9. If what I'd ideally like is not possible then I'd be happy if the data went to another worksheet and I could obtain the handicaps from there.
I'm not sure if I have made this sufficiently clear - but ask away - I'll do all I can to clarify in exchange for some kind soul helping me sort this out!
best regards
Tony (Ashbo)
Re: Ranking a league table to give positions
Will,
That's it! I knew I was close but just forget how to fix the range - excellent! Case closed!
Ashbo
Re: Ranking a league table to give positions
Thanks Will,
I've just looked at this and downloaded it. It actually doesn't help me with my problem though. Since I can see the ranking formula he uses is the same as mine but how do you create it so that you do not need to type in each row?
Tony
Each week I create a league table of people (golfers) with the best average score to date. I thought it would be trivial to rank them in the first column in order to allow people to easily see their position. I used the formula of the type: # =RANK($X1, $X1:$X20)# for say 20 entrants with their score in column X.
However in fact I have over 40 in the table but having created the formula for the first place I cannot seem to extend it down so that the next cell down (2nd position) reads: # =RANK($X2, $X1:$X20) # - it instead increments the range by one so that every postion is ranked 1st !
I attach a fragment of the table for clarity. I just want a formula I can extend down without having to type it in for each position.
Cheers
Ashbo
Re: Incrementing absolute references
Domenic (and others following this thread)
Although the work you helped me with on the golf scores spreadsheet is fine and in use, I think that I have now devised a better way to record the data which avoids the issues of different player's 'score record boxes' expanding at different rates.
The first attachment you will see the small example of the original solution.
The 2nd attachment (MWP-example) shows the new data collection/recording worksheet called 'Data'.
On the 2nd worksheet in the 2nd attachment (MWP-example) I am trying to have a table that summarises the scores (much as in the worksheet table). But as before I am having trouble creating a formulae that I can copy down and across easily. You will find my halfhearted attempt to merely copy across references to each relevant cell in Data - but there must be a simpler way. I tried to use by analogy the work you originally helped me with but to no avail.
Any help you can offer wouldbe greatly appreciated.
Ashbo
Re: Incrementing absolute references
Domenic
I've done all suggest and it worked fine !!
Thanks a lot.
Ashbo