# Ranking Based on Criteria

1. ## Ranking Based on Criteria

I'm looking to rank some categories based on criteria from other cells. I have 12 people who will receive a score in four different categories. I want to rank them by their finish according to my scale I've included and add up their composite score of the four categories to one whole number. There are 3 numbers in the scale, 3 being the best and one being the worst. So for the Top 4 finishers at each cateogory I want them to receive a 3 for that category, and same with the middle 4 to receive a 2 and the bottom 4 to receive a 1.

This is easy to do manually if the numbers and amount of players weren't consistently changing. For example, if I start out with 12 players and then two more players get added to the sheet that is going to affect the rest of the players. I would then want the category scale to now be divisible by 14 players instead of 12. Also, I'd need to calculate in if a players category score changes that the rest of the players are now affected as well.

I've attached my worksheet. Thanks!

2. ## Re: Ranking Based on Criteria

see attachment

3. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
see attachment
Is there anyway to do this without a table? Thanks

4. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
see attachment
Also by my Math it's not calculating correctly.

Aaron should end up with 10 Total Points according to the scale, not 11.

5. ## Re: Ranking Based on Criteria

Aaron has 9 points, not 10
1st place for A = 3
12th place for B = 1
6th place for C = 2
1st place for D = 3
total = 9
you can use a defined range instead of a table

6. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
Aaron has 9 points, not 10
1st place for A = 3
12th place for B = 1
6th place for C = 2
1st place for D = 3
total = 9
you can use a defined range instead of a table
How do you do that? I'm a beginner to Excel. Thanks!

7. ## Re: Ranking Based on Criteria

see attachment

8. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
see attachment
I forgot to mention that I need the results to show for each category. So could you have the results from Column C show in Column G and so forth with the Total showing in column K? Thanks!

9. ## Re: Ranking Based on Criteria

split that column or formula into 4

10. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
split that column or formula into 4
Tried to insert this into my master copy and it's giving me back an N/A symbol. I've attached my master copy to see if you can get it to work on there. I appreciate your work on this. Thanks!

11. ## Re: Ranking Based on Criteria

Originally Posted by mlbdc2012
Tried to insert this into my master copy and it's giving me back an N/A symbol. I've attached my master copy to see if you can get it to work on there. I appreciate your work on this. Thanks!
Sorry wrong file, here's the correct master copy.

12. ## Re: Ranking Based on Criteria

I think you had problems with the offset-function.
See attachment, in column DI, I added the ranking on total and a conditional format with colorscales, so the best are green, middle=yellow and the worst are red.

13. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
I think you had problems with the offset-function.
See attachment, in column DI, I added the ranking on total and a conditional format with colorscales, so the best are green, middle=yellow and the worst are red.
Thanks!

I didn't want you to have to do all that work that's why I was just going to do 12 players to start. I'm trying to copy the rest of the formulas into the rest of the columns (Z-AP) that are hidden and correspond them with columns (CR-DH) that are hidden but I can't seem to get it to work. Where's the MyPoints formula located at? I know the my players is in cell DM3.

14. ## Re: Ranking Based on Criteria

you already used a lot of defined names, so I thought you knew that thing and had only troubles with the function "OFFSET"

sorry because my examples are in dutch
verschuiving = offset
aantalarg = counta
perhaps your formula uses a "," instead of my ";"
PHP Code:
``` MyPlayers =VERSCHUIVING('Batters Eff (3)'!\$D\$11;;;AANTALARG('Batters Eff (3)'!\$D\$11:\$D\$400);)  ```
that's not D11, but D11:D318. Read the online-help on OFFSET and the meaning of the 5 arguments.
1st argument = 'Batters Eff (3)'!\$D\$11 = kind of startpoint
2nd and 3rd argument = omitted here, thus equals 0
4rd argument = height = number of rows you want to use = AANTALARG('Batters Eff (3)'!\$D\$11:\$D\$400) = count the number of not-empty cells in that range, so all cells between D11 and D318 aren't empty = 318-11+1 = 310
5th argument = width = omitted, thus equals 1

MyPoints is a normal defined name with range ='Batters Eff (3)'!\$DK\$4:\$DK\$23

15. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
you already used a lot of defined names, so I thought you knew that thing and had only troubles with the function "OFFSET"

sorry because my examples are in dutch
verschuiving = offset
aantalarg = counta
perhaps your formula uses a "," instead of my ";"
PHP Code:
``` MyPlayers =VERSCHUIVING('Batters Eff (3)'!\$D\$11;;;AANTALARG('Batters Eff (3)'!\$D\$11:\$D\$400);)  ```
that's not D11, but D11:D318. Read the online-help on OFFSET and the meaning of the 5 arguments.
1st argument = 'Batters Eff (3)'!\$D\$11 = kind of startpoint
2nd and 3rd argument = omitted here, thus equals 0
4rd argument = height = number of rows you want to use = AANTALARG('Batters Eff (3)'!\$D\$11:\$D\$400) = count the number of not-empty cells in that range, so all cells between D11 and D318 aren't empty = 318-11+1 = 310
5th argument = width = omitted, thus equals 1

MyPoints is a normal defined name with range ='Batters Eff (3)'!\$DK\$4:\$DK\$23
What am I doing wrong? I've attached a screenshot

16. ## Re: Ranking Based on Criteria

in row 320 copy the formula in CQ320 to CR320:DH320

17. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
in row 320 copy the formula in CQ320 to CR320:DH320
Ok that was it thanks! One last thing, if I want the lowest number in a column to be valued first instead of the highest number how do I alter that in the formula?

18. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
in row 320 copy the formula in CQ320 to CR320:DH320
Any ideas?

19. ## Re: Ranking Based on Criteria

I suppose that in your column the lower a number is the better, so you want to reverse the ranking, that's by adding a 1 in the (dutch) formula. In the original formula it was omitted, so was equal to 0
``Please Login or Register  to view this content.``

20. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
I suppose that in your column the lower a number is the better, so you want to reverse the ranking, that's by adding a 1 in the (dutch) formula. In the original formula it was omitted, so was equal to 0
``Please Login or Register  to view this content.``
Ok thanks this works great! Thanks for all your help on this!!

21. ## Rankings Cells

I'm having trouble ranking certain columns based on the criteria in another cell.

In my attached sheet, I want to rank columns AS:BB based on the number of teams entered in cell AE8. I have a total of 20 teams possible with ranks set up for them, the problem I'm running into is if I select 12 teams I want it to only rank the first twelve rows (16-27) of columns AS:BB but how I have it setup it's still ranking all 20 rows thus not providing an accurate ranking. If 12 teams are selected, I would want the last 8 rows (28-35) of columns AS:BB to be blank and not have a rank.

Same thing goes for all the other possible selections in cell AE8 which consists of team sizes of 8,10,12,14,16,18,20.

My worksheet is attached. Thanks!

22. ## Re: Rankings Cells

=RANK(AG16,INDIRECT("\$AG\$16:\$AG\$"&15+\$AE\$8),1)

in AT16 and copy down is one option.

23. ## Re: Rankings Cells

In R16, enter formula:
Formula:
`Please Login or Register  to view this content.`

And copy down.

Apply similarly to AT:BB.

24. ## Re: Rankings Cells

Originally Posted by ragulduy
=RANK(AG16,INDIRECT("\$AG\$16:\$AG\$"&15+\$AE\$8),1)

in AT16 and copy down is one option.
Maybe I'm doing something wrong but this doesn't seem to work. Can you put it in my worksheet and then attach it for me? Thanks!

25. ## Re: Rankings Cells

Use Ollyxls's formula instead, it's a better way.

26. ## Re: Rankings Cells

Originally Posted by OllyXLS
In R16, enter formula:
Formula:
`Please Login or Register  to view this content.`

And copy down.

Apply similarly to AT:BB.
This isn't working. Doesn't pick up the ranks for rows 30,32 and 34

27. ## Re: Ranking Based on Criteria

I'm having trouble ranking certain columns based on the criteria in another cell.

In my attached sheet, I want to rank columns AS:BB based on the number of teams entered in cell AE8. I have a total of 20 teams possible with ranks set up for them, the problem I'm running into is if I select 12 teams I want it to only rank the first twelve rows (16-27) of columns AS:BB but how I have it setup it's still ranking all 20 rows thus not providing an accurate ranking. If 12 teams are selected, I would want the last 8 rows (28-35) of columns AS:BB to be blank and not have a rank.

Same thing goes for all the other possible selections in cell AE8 which consists of team sizes of 8,10,12,14,16,18,20.

My worksheet is attached. Thanks!

28. ## Re: Ranking Based on Criteria

something like this ?

29. ## Re: Ranking Based on Criteria

I merged the 2 threads-as ragulduy's suggestion.

All of you can continue here.

30. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
something like this ?
Yes Perfect! Thanks so much.

31. ## Re: Rankings Cells

Originally Posted by mlbdc2012
This isn't working. Doesn't pick up the ranks for rows 30,32 and 34
Originally Posted by mlbdc2012
If 12 teams are selected, I would want the last 8 rows (28-35) of columns AS:BB to be blank and not have a rank.
Your requirement is not clear, then. The formula I gave you does exactly as you asked.

32. ## Re: Ranking Based on Criteria

Originally Posted by bsalv
something like this ?
One last thing I need the same thing to be true for column R based on the total in column AE

33. ## Re: Ranking Based on Criteria

Originally Posted by mlbdc2012
One last thing I need the same thing to be true for column R based on the total in column AE
Use the formula I gave you.

Are you reading our replies, and trying to understand them, at all??

34. ## Re: Ranking Based on Criteria

Originally Posted by mlbdc2012
One last thing I need the same thing to be true for column R based on the total in column AE
Nevermind I figured it out. Thanks for all your guys help on this!

35. ## Re: Ranking Based on Criteria

Originally Posted by OllyXLS
Use the formula I gave you.

Are you reading our replies, and trying to understand them, at all??
Ya I used your formula, I forgot it was in this thread. Thanks for your help

36. ## Re: Ranking Based on Criteria

I'm having a difficult time coming up with a formula that will look up a value for me based on the criteria entered in a drop down menu.

In my attached worksheet, I want to look up the value of Column C in Sheet 4 based on the information entered in Sheet 2 which is taken from the drop down menu on Sheet 3.

For example, Cell C3 on sheet 4 should equal \$55 if there are 260 players drafted in the drop down box cell D11 sheet 3 based on the amount listed in cell E6 of sheet 2. On the other hand, if there are 290 players drafted in the drop down box cell D11 of sheet 3 then the amount listed in cell C3 on sheet 4 should be \$58 based on the amount listed in cell L6 of sheet 2.

Please let me know if this is confusing as I'm sure it is.

Thanks!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1