# Athletics - Creating a formula to calculate win/loss/tie

#### orange421

##### New Member
I am creating an excel file in order to track the history of our football team. The excel sheet has the name of our opponents as well as the score for our team and the score for the opponent. I want to have another column that has a formula to calculate, based on the score, the result with a "W", "L" or "T".

The opponent is in column "A", our team score is in column "B" and the opponent score is in column "C". I'd like for the result formula to be in column "D".

Below is the formula that I have working to get wins and losses.

=IF(B3>C3,"W","L")

I tried the following formula to also track if the game ended in a tie but I'm having trouble with it.

=IF(B3>C3,"W","L",IF(B3=C3,"T"))

Is there a formula to track all three result possibilities? Thanks for your help!

#### wwbwb

##### Well-known Member
Try:
=if(b3=c3,"T",if(b3>c3,"W","L"))

#### orange421

##### New Member
What if I wanted to create a cell that adds up all of the "W" in column D? Also for "L" and "T". Thanks!

#### wwbwb

##### Well-known Member
You can use countif for that.

=countif(D3:D100,"W")

#### orange421

##### New Member
Great! Last one question. It's a little trickier.

I want to create another sheet within the excel file to put our all-time record vs. specific opponents.. On that sheet I have all of our opponents listed out in column A, our wins against that opponent in column B, losses in C, points scored by us in D and opponent points in E.

Can I create a formula that can find our opponent (Example: Alabama A&M) and tally the wins/loss/points for/points against for each time we've played them, on a separate sheet? I'm sure that it would be separate formulas for each column but I don't even know where to start for this one.

#### phoenixdown

##### Board Regular
I would probably use a pivot table with the opponent in the drop down / filter.

#### Jupiter6

##### New Member
I am creating an excel file in order to track the history of our football team. The excel sheet has the name of our opponents as well as the score for our team and the score for the opponent. I want to have another column that has a formula to calculate, based on the score, the result with a "W", "L" or "T".

The opponent is in column "A", our team score is in column "B" and the opponent score is in column "C". I'd like for the result formula to be in column "D".

Below is the formula that I have working to get wins and losses.

=IF(B3>C3,"W","L")

I tried the following formula to also track if the game ended in a tie but I'm having trouble with it.

=IF(B3>C3,"W","L",IF(B3=C3,"T"))

Is there a formula to track all three result possibilities? Thanks for your help!
I think this is a better way to calculate The W,L,T
S+/-= Score Differential
IF(E2>0,1," ") W
IF(E2<0,1," ") L
IF(E2=0,1," ") T

 HOME Score Opponent Score S+/- W L T Year Opponent Score Road/Away Score S+/- W L T Arizona Cardinals 27 Detroit Lions 27 0 1 19 Baltimore Ravens 23 Arizona Cardinals 17 -6 1 Arizona Cardinals 20 Carolina Panthers 38 -18 1 19 Cincinnati Bengals 23 Arizona Cardinals 26 3 1 Arizona Cardinals 10 Seattle Seahawks 27 -17 1 19 New York Giants 21 Arizona Cardinals 27 6 1 Arizona Cardinals 34 Atlanta Falcons 33 1 1 19 New Orleans Saints 31 Arizona Cardinals 9 -22 1 Arizona Cardinals 25 San Francisco 49ers 28 -3 1 19 Tampa Bay Buccaneers 30 Arizona Cardinals 27 -3 1 Arizona Cardinals 7 Los Angeles Rams 34 -27 1 19 San Francisco 49ers 36 Arizona Cardinals 26 -10 1 Arizona Cardinals Pittsburgh Steelers 0 1 19 Seattle Seahawks Arizona Cardinals 0 1 Arizona Cardinals Cleveland Browns 0 1 19 Los Angeles Rams Arizona Cardinals 0 1 21 123 31 187 -64 1 4 3 -11 27 164 22 132 -32 2 4 2 22 132 27 164 -32 2 4 2 -5

1,081,545
Messages
5,359,450
Members
400,528
Latest member
Ratish52

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...