Rank & Tie Breakers based on multiple criteria

[Anonymous1]

Hi Excel Gurus,

I am working on a sports schedule and would like to break ties based on multiple criteria. I'm looking for a formula that will rank & break a tie (or multiple ties) in-order, based on the following criteria:

Wins
Win %
Head-to-Head
Point Differential
Total Points Scored

I have a formula that is working to calculate wins & win percentage, but I'm stuck at head to head.

I have all of the raw data organized to pull from, I just can't figure out the right formulat to make this work. Any/all suggestions are much appreciated!

Tab 1: Ranking Table (wins, losses, etc...)
Tab 2: Raw score entry that populates wins/losses
Tab 3: Pivot Table showing head-to-head wins

Please note, this is just a sample so everything in the sample sheet is not linked, just wanted to give you an idea of where the data is being pulled and input.

Thank you!

Mike

[dflak]

Here is how I would calculate it.

First of all convert the range into an Excel Table. you don't have to do that, but it makes the formulas easier to read and also pivot tables and other formulas will always reference the right amount of data.

I added a couple of helper columns to compute head-to-head.

Head to Head at Home: =SUMPRODUCT( ([Home Team]=[@[Home Team]]) * ([Visitor]=[@Visitor])* ([Home Team Score]>[Visitor Score]) ) - where the team looked at is at home playing this particular visitor and the home score is greater than the visitor score.

Head to Head Away: =SUMPRODUCT(([Visitor]=[@[Home Team]])*([Home Team]=[@Visitor])*([Visitor Score]>[Home Team Score])) - where the team looked at is the visiting team playing the same opponent and the visitor score is greater.

Head to Head Total = Head to Head (Home) + Head to Head (Away).

Results are summarized on the Head to Head sheet.

P.S. I removed the blank rows in the Game Score Table.

[Anonymous1]

Awesome, that totally worked!

Thank you so much for the reply and assistance, I was trying every different combination I could think of but couldn't get over the H2H!

[dflak]

I find that I have to translate the problem into words and then sometimes it makes sense.