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

orange421

New Member
Joined
Oct 14, 2014
Messages
4
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!
 

orange421

New Member
Joined
Oct 14, 2014
Messages
4
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
Joined
Oct 20, 2003
Messages
513
You can use countif for that.

=countif(D3:D100,"W")
 

orange421

New Member
Joined
Oct 14, 2014
Messages
4
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.
 

Jupiter6

New Member
Joined
Dec 6, 2019
Messages
6
Office Version
2016
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 ScoreOpponentScoreS+/-WLTYearOpponentScoreRoad/AwayScoreS+/-WLT
Arizona Cardinals27Detroit Lions270 119Baltimore Ravens23Arizona Cardinals17-6 1
Arizona Cardinals20Carolina Panthers38-18 1 19Cincinnati Bengals23Arizona Cardinals2631
Arizona Cardinals10Seattle Seahawks27-17 1 19New York Giants21Arizona Cardinals2761
Arizona Cardinals34Atlanta Falcons3311 19New Orleans Saints31Arizona Cardinals9-22 1
Arizona Cardinals25San Francisco 49ers28-3 1 19Tampa Bay Buccaneers30Arizona Cardinals27-3 1
Arizona Cardinals7Los Angeles Rams34-27 1 19San Francisco 49ers36Arizona Cardinals26-10 1
Arizona CardinalsPittsburgh Steelers0 119Seattle SeahawksArizona Cardinals0 1
Arizona CardinalsCleveland Browns0 119Los Angeles RamsArizona Cardinals0 1
2112331187-64143-112716422132-32242
2213227164-32242-5
 

Forum statistics

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

Some videos you may like

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...
Top