How to assign a number to a list of values

**SJBurger** · 06-22-2017, 09:05 AM

Good Day

I am new to the forum and in some need of help.

I am busy helping a local sport team setting up a scoring sheet on excel. But having a problem creating a formula to work out their sort of scoring. Below is the problem

Evey one on the list gets a score from 0% to 100%. People can get the same score. now they also assign a number from 100 down to 0 starting at the highest to lowest. So if person A have the highest score 80% he will get 100 but person B & C gets the second highest score 70% they will both get 99. and so on till the person that gets 0.

I am having a problem creating a formula that will assign a value from 100 to 0 on the score. There are about 250 members that will be on the list.

Any help would be appreciated.

**xlnitwit** · 06-22-2017, 09:12 AM

Hi,

That sounds like a RANK function, depending on whether the next highest value in your example would get 98 or 97 as a score?

**SJBurger** · 06-22-2017, 09:18 AM

I have played with the rank function but not really getting to work.

Here is a bit of a better example. It is listed from high to low but will not always be like that:

Name Score Number
A 80 100
B 75 99
C 75 99
D 75 99
E 75 99
F 73.3 98
G 65 97
H 60 96

**benishiryo** · 06-22-2017, 09:36 AM

hi SJBurger. so eventually, your lowest % may not get a score number of 0? if so, try:
=100-SUMPRODUCT(($A$1:$A$250>A1)*1/COUNTIF($A$1:$A$250,$A$1:$A$250))

as similar formula is explained in the file of my link below*Tips & Tutorials I Compiled, sheet 19

**xlnitwit** · 06-22-2017, 09:36 AM

Perhaps this

	A	B	C
1	A	80	100
2	B	75	99
3	C	75	99
4	D	75	99
5	E	75	99
6	F	73.3	98
7	G	65	97
8	H	60	96

	C
1	=100-COUNT(1/FREQUENCY(IF($B$1:$B$8>B1,$B$1:$B$8),$B$1:$B$8))

Note that the formula in C1 is an array formula and must be committed by pressing Ctrl+Shift+Enter. Then copy it down.

**ChemistB** · 06-22-2017, 09:50 AM

And just to give you a third choice, use this Array function

=100-SUM(IF(B2<$B$2:$B$9,1/COUNTIF($B$2:$B$9,$B$2:$B$9)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

I would personally choose benishiryo's solution as it is not an arrayed function but all three are good. I included all 3 in my spreadsheet.

**SJBurger** · 06-22-2017, 09:53 AM

Thanks Benishiryo

I added your formula to an IF statement so that if a user gets 0 he will get 0 and then if not your formula will kick in. i will test it a bit more with their scoring system but it seems that it is working. Thanks all for the help

How to assign a number to a list of values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to assign a number to a list of values

Re: How to assing a number to a list of values

Re: How to assing a number to a list of values

Re: How to assign a number to a list of values

Re: How to assign a number to a list of values

Re: How to assign a number to a list of values

Re: How to assign a number to a list of values

Thread Information

Users Browsing this Thread

Similar Threads

Automatically assign a code number to a list of products?

Assign values to items in a list

Assign values to drop down list

how to assign a number between two values

Excel 2007 : How to Assign Number values to List Items

Can I assign a series of values to a number?

List of Dates- Assign Week Number??

Bookmarks

Bookmarks

Posting Permissions