Ranking by Rating

I have a list on Sheet2 of the following format:

Name | Rating | Games Played | Wins | Losses | Achievements

The list is alphabetical.

Is there a formula I can use to reorder/rank the names and their associated stats on Sheet1 by RATING. The added trick if possible is I need two lists.. One for people >1000 rating.. and one list for people <1000 (higher rating= higher ranking)?

Thank you for any help.

Putting aside the two lists, can't you just sort by rating?

Well the rating constantly changes as new results are input. I'd prefer for this to be the "database" while I make a nice looking Rankings Page that will be posted. I am currently doing it manually but prefer to somehow automate it. The Rank function along with a Countif will rank them for me, now I just have to reorder them on another sheet according to their rank..

Here's one approach....

Still on sheet 2, assuming your ratings are in B2:B100 then in an empty column in row 2 use this formula

=IF(B2<1000,"",SUMPRODUCT(--(B$2:B$100>=1000),--(B$2:B$100>B2))+SUMPRODUCT(--(B$2:B2>=1000),--(B$2:B2=B2)))

copied down to row 100

...and then in the next column

=IF(B2>=1000,"",SUMPRODUCT(--(B$2:B$100<1000),--(B$2:B$100>B2))+SUMPRODUCT(--(B$2:B2<1000),--(B$2:B2=B2)))

also copied down

This gives you two columns of unique ranks or blanks, one ranking ratings greater than or equal to 1000 and the other ranking ratings under 1000

You can then use INDEX/MATCH formulas in sheet 1 to return the data you want based on the rankings

So list the rankings 1 to N on the output sheet, and use INDEX/MATCH/LARGE to retrieve the data. (You can't have any ties in the ratings.)

Thanks. I do have some ties in the ratings but you've given me an idea on how to get around it.. I'll try it tonight.. Thanks!

Great! Post back if you need help.

My suggested formulas above will give unique ranks even if you have ties. The first tied entry in the list will have the higher rank...or do you need to break ties using another criterion?

My suggested formulas above will give unique ranks even if you have ties

Should have known ...