Applying points to ranked cells

[llcamino]

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.

 

[Bubbis Thedog]

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]

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]

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]

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]

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]

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

 

[anubis]

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]

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]

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
	A	B	C	D
1	72	1	10	10
2	73	2	6	7
3	73	2	6	5
4	80	4	3	3
5	90	5	1	1
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.