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!

Originally Posted by bsalv

see attachment

Is there anyway to do this without a table? Thanks

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.

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!

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!

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!

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.

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.

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

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?

Originally Posted by bsalv

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

Any ideas?

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!

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

in AT16 and copy down is one option.

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!

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

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

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!

Originally Posted by bsalv

something like this ?

Yes Perfect! Thanks so much.

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

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!

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

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!