Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
negitive and positive numbers
I want to track a team players progress each week. Handicap range
is:2-,2,2+,3-,3,3+ up to 7-,7,7+. Handicaps go up or down each week for the players that play. All players don't play each week. I want to use W or L for win or loss. How can I set it up so that it looks at whether I put in a "w" or "L", looks at the previous week handicap - say 4+ and returns a 5- in the next cell? All this for 8 teams and 64 players with only 5 of 8 players each week |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
negitive and positive numbers
Two questions:
1. What is the difference between 3 and 3+ or 4 and 4+ 2. Does an L always subtract 1 and a W always subtract 1 from a players handicap, or is the adjustment weighted by the opponents handicap or the difference between the two player's handicaps? If there is no weighting, then I would set up a wsh with headings: Team/Player/W or L/Handicap I would name the W or L column range "WL" I would then right click on the TAB for this sheet and "View Code" I would enter the following code in the Worksheet_Change event Dim X as long If Target.Count1 then Exit Sub If Not Intersect(Target, Range("WL")) Is Nothing Then Select Case Target.Value Case is = "W" X = 1 Case Is = "L" X = -1 End Select Target.Offset(0,1) = Target.Offset(0,1) + X End If (I think this will also handle the Case where you inadvertantly delete the W or L, leaving the cell Emplty) If you want a running W/L you would need to add this to the code to record it elsewhere. Next, if you have such a thing as a Team Handicap you could use =SumIf(Team_Range,Criteria,Range("WL")) HTH "kematz" wrote: I want to track a team players progress each week. Handicap range is:2-,2,2+,3-,3,3+ up to 7-,7,7+. Handicaps go up or down each week for the players that play. All players don't play each week. I want to use W or L for win or loss. How can I set it up so that it looks at whether I put in a "w" or "L", looks at the previous week handicap - say 4+ and returns a 5- in the next cell? All this for 8 teams and 64 players with only 5 of 8 players each week |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
negitive and positive numbers
The difference is just wins or losses. If a player has a 3+ handicap and wins
they go to 4- for the next week. Or if they are a 4 and loose they go to a 4- and so on up to a max of 7+. A "W" always increases a players handicap and an "L" always reduces it. There are 3 levels to each level. The opponents handicap has no bearing on the players handicap. "gocush" wrote: Two questions: 1. What is the difference between 3 and 3+ or 4 and 4+ 2. Does an L always subtract 1 and a W always subtract 1 from a players handicap, or is the adjustment weighted by the opponents handicap or the difference between the two player's handicaps? If there is no weighting, then I would set up a wsh with headings: Team/Player/W or L/Handicap I would name the W or L column range "WL" I would then right click on the TAB for this sheet and "View Code" I would enter the following code in the Worksheet_Change event Dim X as long If Target.Count1 then Exit Sub If Not Intersect(Target, Range("WL")) Is Nothing Then Select Case Target.Value Case is = "W" X = 1 Case Is = "L" X = -1 End Select Target.Offset(0,1) = Target.Offset(0,1) + X End If (I think this will also handle the Case where you inadvertantly delete the W or L, leaving the cell Emplty) If you want a running W/L you would need to add this to the code to record it elsewhere. Next, if you have such a thing as a Team Handicap you could use =SumIf(Team_Range,Criteria,Range("WL")) HTH "kematz" wrote: I want to track a team players progress each week. Handicap range is:2-,2,2+,3-,3,3+ up to 7-,7,7+. Handicaps go up or down each week for the players that play. All players don't play each week. I want to use W or L for win or loss. How can I set it up so that it looks at whether I put in a "w" or "L", looks at the previous week handicap - say 4+ and returns a 5- in the next cell? All this for 8 teams and 64 players with only 5 of 8 players each week |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
negitive and positive numbers
Jason 6- W 6 W 6+ W 7- L 6+ "gocush" wrote:
Two questions: 1. What is the difference between 3 and 3+ or 4 and 4+ 2. Does an L always subtract 1 and a W always subtract 1 from a players handicap, or is the adjustment weighted by the opponents handicap or the difference between the two player's handicaps? If there is no weighting, then I would set up a wsh with headings: Team/Player/W or L/Handicap I would name the W or L column range "WL" I would then right click on the TAB for this sheet and "View Code" I would enter the following code in the Worksheet_Change event Dim X as long If Target.Count1 then Exit Sub If Not Intersect(Target, Range("WL")) Is Nothing Then Select Case Target.Value Case is = "W" X = 1 Case Is = "L" X = -1 End Select Target.Offset(0,1) = Target.Offset(0,1) + X End If (I think this will also handle the Case where you inadvertantly delete the W or L, leaving the cell Emplty) If you want a running W/L you would need to add this to the code to record it elsewhere. Next, if you have such a thing as a Team Handicap you could use =SumIf(Team_Range,Criteria,Range("WL")) HTH "kematz" wrote: I want to track a team players progress each week. Handicap range is:2-,2,2+,3-,3,3+ up to 7-,7,7+. Handicaps go up or down each week for the players that play. All players don't play each week. I want to use W or L for win or loss. How can I set it up so that it looks at whether I put in a "w" or "L", looks at the previous week handicap - say 4+ and returns a 5- in the next cell? All this for 8 teams and 64 players with only 5 of 8 players each week |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel change a group of positive numbers to negitive numbers | Excel Discussion (Misc queries) | |||
How do I convert a column of positive numbers to negitive numbers | Excel Worksheet Functions | |||
Coloring cells based on positive or negitive numbers | Excel Discussion (Misc queries) | |||
adding cells with positive and negitive nos. | New Users to Excel | |||
How do I change numbers from positive to negitive | Excel Worksheet Functions |