Calculate Which Score is Higher (Value) Based on Name (Text)

[samrizz4]

Hi,

I am trying to develop a way to calculate my football head to head matchups for my football league to show which team plays the best against each other. A snapshot of my spreadsheet is below:

Team names are in columns A and D, and their respective scores are in columns B and E.

So, I am wondering if there is a way to see if column B is greater than column E and then check to see what name is in the column to the left of the scores (column B/E) and then sort them into head to head records. For example, in the picture above, if I want to see Sam vs. Peyton's head to head record, it should be 0-2-0 (with the first value being a win, second is the loss column, and third are ties). Because in row 3 and row 6, Peyton scored more than Sam.
For Dan vs. Anthony it would be 0-1-0,
and for Joe vs. Nick it is 1-1-0 and format it looking like this:

Sam vs. Peyton Head to Head:	Dan vs. Anthony Head to Head:	Joe vs. Nick Head to Head:
0-2-0	0-1-0	1-1-0

In cell F3, I tried to calculate the Sam vs. Peyton scenario, but could not get it to work properly.

If anyone has any advice on how to tackle this problem that would be awesome because I have four years worth of this data and doing that by hand would not be fun!

Sam

 

[kennypete]

One way to tackle it is to break the task into steps.

1. Normalise the results so that the first alphabetical team's results appear first
2. Add a W / D / L column
3. Create a table of Head-to-Head results

The following achieves that:

...where the formulas for each of the G-K columns are like this:

You can then add the table:

...where:
M3 is =IFERROR(INDEX($G$3:$G$21, MATCH(0, INDEX(COUNTIF($M$2:M2, $G$3:$G$21), 0, 0), 0)), "")
and
N2 is =IFERROR(INDEX($I$3:$I$21, MATCH(0, INDEX(COUNTIF(M2:$M$2, $I$3:$I$21), 0, 0), 0)), "")

M3 can be dragged down and N2 across for the number of teams/adjusted if there are more than the 20 this accommodates. (Or you could always just do this manually if there are not too many teams to accommodate, you want a particular order, etc.)

N3 is then =COUNTIFS($G:$G,$M3,$I:$I,$N$2,$K:$K,"W") & "-"&COUNTIFS($G:$G,$M3,$I:$I,$N$2,$K:$K,"D")& "-"&COUNTIFS($G:$G,$M3,$I:$I,$N$2,$K:$K,"L")
and so forth for each combination, to create the W-D-L output.

I'm sure this isn't perfect in terms of efficiency, but it does chunk it down reasonable easily and is uncomplicated.

The Workbook with the above examples/formulas in it is here.

 

[samrizz4]

@kennypete thank you that works great. I was also wondering if there is a way to add the records together (i.e. 3-2 + 5-4 = 8-6) and also to them sum that into total games played (i.e. 8-6 = 14 total games played) do you happen to know a way I could calculate this?

 

[kennypete]

Do you mean how many games are played by each combination? In which case, just sum the COUNTIFS?
Like COUNTIFS($G:$G,$M3,$I:$I,$N$2,$K:$K,"W") + COUNTIFS($G:$G,$M3,$I:$I,$N$2,$K:$K,"D") + COUNTIFS($G:$G,$M3,$I:$I,$N$2,$K:$K,"L")
Or are you trying to sum something else?

 

[samrizz4]

@kennypete
I'm saying so for example, using your workbook, in cell M3 it shows the head to head matchups for Peyton. If she plays Sam three times and winning 2 and 1 tie, it shows 2-1-0 in N3, and if she plays Dan twice and loses and ties once each, it shows 0-1-1 in O3. I'd now want to add N3 + O3 to find Peyton's overall record, which would result in showing a 2-2-1 record (2-1-0 + 0-1-1). So now the overall record result (2-2-1) is stored in cell M14.

After that, I'd like to add the wins, losses, and ties from M14 to show how many games Peyton has played (2+2+1=5) in M18.

Does that make more sense of what I'm looking to accomplish?

 

[kennypete]

Sure, that's actually easier than getting the head-to-head itemised results because the counts of the W, D, and L only have one less criterion, so this will do it (for Peyton on the example below):

=COUNTIFS($G:$G,$N3,$K:$K,"W") & "-"&COUNTIFS($G:$G,$N3,$K:$K,"D")& "-"&COUNTIFS($G:$G,$N3,$K:$K,"L")

To illustrate with my results (you'll see that Joe's and Dan's results, in the new Overall column, are the combo ... or, more correctly, just a count of the W/D/L for them as individuals)