negitive and positive numbers

[Guest]

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

 

[Guest]

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.Count>1 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

 

[Guest]

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.

 

[Guest]

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.Count>1 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