Applying points to ranked cells

llcamino

New Member
Joined
Mar 4, 2005
Messages
7
I'm trying to rank a group of 5 cells and assign a value to them. For example Cells B26:B30 I would like to assign the values as follows in Cells C26:C30.

Numbers in Column B must stay in their current cell.

Lowest Number from Column B receives a 10 in Column C
Next lowest in B gets 7 in C
5 in C
3 in C
1 in C

Is this possible?

Thanks in advance.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Bubbis Thedog

Well-known Member
Joined
Jul 29, 2004
Messages
967
Sure it's possible!

Investigate the LARGE() function. That's an option that should give you the result you need. If you need help, ask; but see if you can do it on your own first.
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
You could also use the Rank Function, to return the rank of each cell.

You then could use vlookup to get the points from a table, or you could use the choose function.

Lots of choices here, post back if you get stuck.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
llcamino said:
I'm trying to rank a group of 5 cells and assign a value to them. For example Cells B26:B30 I would like to assign the values as follows in Cells C26:C30.

Numbers in Column B must stay in their current cell.

Lowest Number from Column B receives a 10 in Column C
Next lowest in B gets 7 in C
5 in C
3 in C
1 in C

Is this possible?

Thanks in advance.

Any possibilities of ties?
 

llcamino

New Member
Joined
Mar 4, 2005
Messages
7

ADVERTISEMENT

All I can seem to get with the Large function is for it to replace which ever column with the largest in the group. Here is what I'm really looking to do. I am tracking players in a game, where the lowest raw score yields the highest points in the total score. In other words, imagine if they did it this way in golf for multiple events throughout the year. The best score (lowest) is rewarded with a 10 for that round, second lowest earns 7 and so on, 5,3,1. Player with the highest total score wins the championship. Column A is the player, B is the raw score, and I'd like C to automatically assign the total points.

Player1---72---10
Player2---73---7
Player3---76---5
Player4---80---3
Player5---90---1

Thanks
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
llcamino said:
All I can seem to get with the Large function is for it to replace which ever column with the largest in the group. Here is what I'm really looking to do. I am tracking players in a game, where the lowest raw score yields the highest points in the total score. In other words, imagine if they did it this way in golf for multiple events throughout the year. The best score (lowest) is rewarded with a 10 for that round, second lowest earns 7 and so on, 5,3,1. Player with the highest total score wins the championship. Column A is the player, B is the raw score, and I'd like C to automatically assign the total points.

Player1---72---10
Player2---73---7
Player3---76---5
Player4---80---3
Player5---90---1

Thanks

How would you handle ties?
 

Bubbis Thedog

Well-known Member
Joined
Jul 29, 2004
Messages
967

ADVERTISEMENT

Brian makes an excellent point about ties (something that I often neglect :rolleyes: ). It appears that they are indeed possible. What if the top five all had 75s? How would you do the scoring then?
 

anubis

Active Member
Joined
Dec 22, 2004
Messages
359
Hello llcamino.

If there are more than 5 players, you can use this. It will return a null value if the player is not in the top 5.

=IF(ISERROR(CHOOSE(RANK(B26,$B$26:$B$38,1),10,7,5,3,1)),"",CHOOSE(RANK(B26,$B$26:$B$38,1),10,7,5,3,1))

This will also give the higher of the two for ties. ie 2nd and 3rd lowest will both get 7 points.
 

llcamino

New Member
Joined
Mar 4, 2005
Messages
7
Ties would split the points equally.

If 2nd and 3rd tie, they would split 7 and 5 points, so 6 points each.
If 2nd, 3rd, and 4th tie, they would split 7, 5, and 3 points and each receive 5 pts.

Thanks...I'm working on it on my end, and learning, but I'm sure someone could get the answer to me faster than I can learn it.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
llcamino said:
Ties would split the points equally.

If 2nd and 3rd tie, they would split 7 and 5 points, so 6 points each.
If 2nd, 3rd, and 4th tie, they would split 7, 5, and 3 points and each receive 5 pts.

Thanks...I'm working on it on my end, and learning, but I'm sure someone could get the answer to me faster than I can learn it.

something like this?
AverageAssign.xls
ABCD
17211010
273267
373265
480433
590511
Sheet1


Formula in B1 and copied down,

=RANK(A1,$A$1:$A$5,1)

Formula in C1 array entered and copied down,

=AVERAGE(IF($B$1:$B$5=B1,$D$1:$D$5,""))

Column D house points

Now if you got more than five scores, that's another step and formula, returning the nth scores. Do a search of the board for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top