Team Ranking Spreadsheet

**CornfedChris** · 07-06-2011, 04:14 AM

I'm trying to create an excel spreadsheet which can rank about 12 football teams based on about 8 different criteria: Win/Loss Record, Win %, Win/Loss Differential, Bowl Wins, BCS Bowl Wins, National Titles, and if possible Strength of Schedule & Margin of Victory.

I would like to setup a table where when the values are plugged in, the rankings column will be updated and somehow linked to the "Team Name" column with an auto sort feature so that #1 is on top followed by #2 and so on.

Why am I doing this?
I run an online dynasty in the video game NCAA Football and I would like to use this ranking/poll system so that I can ultimately see who played the best and worst over multiple years of the dynasty.

I have used excel many times but I'm not very savvy with formulas so whatever you can help with... or if you can point me in the right direction I would appreciate it. Thanks!

**estige** · 07-06-2011, 05:11 AM

Hi, I have done something quite similar this way:
Make a table with teams in column A and the 8 criteria in column B to I (and off course with the teams values in the cells).

The next columns (J to Q) (or in a different table) use the rank function to get each teams rank among all the teams for each indicator. (make sure you (for all indicators) do it in a way that the smallest number always is best (or worst).

Sum the ranks for each team in the next column (R).

In the next column, rank the "Sum of ranks"for each team (S).

In the last column (T) copy the team names from column A

Make a result table with the numbers 1-12 in the first column

In column B use Vlookup to search for the rank (A in resulttable) in column S and return the teamname from column T

If you want to include more information from the first large table, use Vlookup to search for the teamname (from volumn B in resulttable) in column A in the first table

Good luck.

**estige** · 07-06-2011, 06:29 AM

I made an example, see attachment.

**CornfedChris** · 07-06-2011, 05:23 PM

Originally Posted by estige

I made an example, see attachment.

Wow that did exactly what I needed it to do!

I modified a couple category values to give it an even weight distribution and added a win percentage column too. I wasn't sure how to make the losses category count negatively against the ranking so I just used a negative value for them.

I understand what the tables are doing but I'm still kinda unsure how to use VLOOKUP but this will work perfectly.

Thank you very much!

**FootballRank** · 09-16-2018, 05:26 PM

Hello, I know this is an old thread. Can someone please help me create a Top 25. The same system close to the BCS. Factors would be Wins, Losses, Strength of schedule, margin of victory in each game. if possible can someone make this simple enough for a non tech person to understand?

**Pete_UK** · 09-16-2018, 05:47 PM

Welcome to the forum, though you seem a bit shy about telling us where you are from.

If you had read the new forum rules (see banner above), you would have seen in Rule 4 that you should not hijack someone else's thread by asking your own question. Instead, you should start a new thread of your own, with a link back to this thread if you think it is relevant.

Hope this helps.

Pete

**FDibbins** · 09-16-2018, 11:24 PM

FoorballRank, welcome to the forum

1. Please update your profile as necessary to properly reflect your general location. Members may need to tailor answers based on your regional settings.

2. upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

3. Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.