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

[orange421]

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]

Try:
=if(b3=c3,"T",if(b3>c3,"W","L"))

 

[orange421]

That's it! Appreciate your help.

 

[orange421]

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]

You can use countif for that.

=countif(D3:D100,"W")

 

[orange421]

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]

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

 

[Jupiter6]

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