up vote 0 down vote favorite
share [g+] share [fb]

I have a spreadsheet that I would like to use to track the NFL seasons.

I'm having trouble figuring out the formula to use when there is a tie (either a 2-way, 3-way, or more). I have attached a screen shot of my spreadsheet. I have columns set up and I would like the ranking formula to be placed in column U shown in attachment.

I know there are different rules for ties within a division and conference, but I don;t know how to set it up in Excel. I'm sure it is a lengthy formula, so if there's an easier way to do it please let me know.

enter image description here

I've also posted this on answers.microsoft.com.

TIE BREAKING RULES ACCORDING TO nfl.com

Two Clubs

  1. Head-to-head (best won-lost-tied percentage in games between the clubs).
  2. Best won-lost-tied percentage in games played within the division.
  3. Best won-lost-tied percentage in common games.
  4. Best won-lost-tied percentage in games played within the conference.
  5. Strength of victory.
  6. Strength of schedule.
  7. Best combined ranking among conference teams in points scored and points allowed.
  8. Best combined ranking among all teams in points scored and points allowed.
  9. Best net points in common games.
  10. Best net points in all games.
  11. Best net touchdowns in all games.
  12. Coin toss

Three or More Clubs (Note: If two clubs remain tied after third or other clubs are eliminated during any step, tie breaker reverts to step 1 of the two-club format).

  1. Head-to-head (best won-lost-tied percentage in games among the clubs).
  2. Best won-lost-tied percentage in games played within the division.
  3. Best won-lost-tied percentage in common games.
  4. Best won-lost-tied percentage in games played within the conference.
  5. Strength of victory.
  6. Strength of schedule.
  7. Best combined ranking among conference teams in points scored and points allowed.
  8. Best combined ranking among all teams in points scored and points allowed.
  9. Best net points in common games.
  10. Best net points in all games.
  11. Best net touchdowns in all games.
  12. Coin toss
link|improve this question
89% accept rate
4  
Could you please state what the tie-break rules are? Otherwise, no one will know where to start helping you. – Excellll Jan 23 at 18:58
2  
Indeed. Generally sports are pretty low on the list of interests for geeks. What are the rules? – skub Jan 23 at 19:01
1  
Doesn't need to be general aversion to sports, but e.g. in Europe (lots of capable Excel users) American Football is a very minor sport. You're limiting the audience of your question needlessly. – Daniel Beck Jan 23 at 19:47
feedback

closed as too localized by soandos, DragonLord, Sathya♦ Feb 3 at 18:18

This question is unlikely to ever help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. See the FAQ for guidance on how to improve it.

1 Answer

up vote 0 down vote

That is complex enough that I'm not sure you can do it with just a formula. If you want to try, I would start with several columns to calculate a separate ranking for each of the 12 points. These columns could later be hidden if you don't want to see them.

For example, Test 10 is talking about touchdowns and I don't see those in your screen print.

link|improve this answer
feedback

Not the answer you're looking for? Browse other questions tagged or ask your own question.