Win/Loss Streak Stationary

[tgreene2856]

I couldn't find what I was looking for so I decided to post. I have a spreadsheet set up with win in D Col and loss in E Col. What I want is based on when stats change in either D or E in F the win/loss streak changes. Example:

Wins: 3 Losses: 2 My Win/Loss Streak is +1

Now I win three games in a row.

Wins: 6 Losses: 2 My Win/Loss Streak is +4

My next game I lose.

Wins: 6 Losses: 3 My Win/Loss Streak is -1

How would I go about do that? is it possible?

Each team has a row and total wins go in D & E.

Tim

 

[braindiesel]

I don't understand the logic here
Wins: 3 Losses: 2 My Win/Loss Streak is +1

Do you mean
Win Win Loss Loss Win = +1? since the last game in a win?

 

[tgreene2856]

Yes. Sorry for the confusion.

 

[braindiesel]

No prob.
With Win in D and Loss in E, I marked the wins or losses with an X in the appropriate column
In F2, I put the following
=IF(AND(D2="X",D1="X"),F1+1,IF(AND(E2="X",E1="X"),F1-1,IF(D2="X",1,-1)))

Does that solve it?

 

[tgreene2856]

I believe so, but D2 would be a completely different team so why does D2 =X in the formula?
Example of my sheet:

Team: W: L:
Team 1 3 2
Team 2 2 3

When I update I change the teams win or loss so total wins and total losses are in two cols. I hope that isn't confusing. Thanks for the help so far.

Tim

 

[braindiesel]

I'm trying to understand your layout.
You have a win column
You have a loss column.
In the first row "Superteam" wins
What precisely do you put in the cell under win if anything
What precisely do you put in the cell under loss if anything

 

[tgreene2856]

The table shows what I want my spread sheet to look like. Right now it does not have the win/loss streak.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Team Names:
[/TD]
[TD]Wins:
[/TD]
[TD]Losses
[/TD]
[TD]Win/Loss Streak
[/TD]
[/TR]
[TR]
[TD]Capitals
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Havoc
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to get is when I update the Capitals wins or losses their win streak will auto update. In that case if the update is to 5 wins then the win/loss streak will become 4. However it is a loss I want it to become -1. If that is possible. I am not sure I will be able to get any clearer then that. Thanks for your help so far.

Tim

 

[braindiesel]

With the code below, C4 is Capitol Wins and D4 is Capitol Losses
You could add a win and lose button for the row

Here is the code
Sub weWon()
Range("C4").Value = Range("C4").Value + 1
If Range("E4").Value >= 0 Then
Range("E4").Value = Range("E4").Value + 1
Else
Range("E4").Value = 1
End If

End Sub
Sub weLost()
Range("D4").Value = Range("D4").Value + 1
If Range("E4").Value <= 0 Then
Range("E4").Value = Range("E4").Value - 1
Else
Range("E4").Value = -1
End If

End Sub

If you need this to be on a cell change event, I will throw in the towel, as that isn't my strength, but I do believe it can be done.

 

[tgreene2856]

Thanks. It worked.