Rank score based on participants

[iazcac]

Hi,
Am trying to allocate points evenly between 25 and 100 based on number of participants and failing miserably.

I need a formula to allocate points evenly. The lowest ranked person will always score 25 points. The highest ranked person will always score 100 points. Anyone else will be evenly spread

Say there were 4 people, the scores would be 25, 50, 75 and 100.

Appreciate some help
Thanks
C

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1	Group A	Rank	Points		Group B	Rank	Points
2	A	1	100		T	3	75
3	B	4	25		U	2	87.5
4	C	3	50		V	6	37.5
5	D	2	75		W	1	100
6					X	7	25
7					Y	5	50
8					Z	4	62.5
Sheet6

Cell Formulas
Range	Formula
C2:C5,G2:G8	C2	=IF(A2="","",25+(MAX(B:B)-B2)/(MAX(B:B)-1)*75)

 

[lrobbo314]

How about this?

iazcac
	A	B	C	D
1	Name	Score	Rank	Spread
2	NATHANAEL	54	1	25
3	ARHAM	62	2	50
4	CASSANDRA	81	4	100
5	EDMUND	73	3	75
6
7
8	Name	Score	Rank	Spread
9	MARA	24	1	25
10	ANNALISE	56	2	40
11	FRANCISCO	71	5	85
12	MAXWELL	66	4	70
13	CELIA	91	6	100
14	ELLIE-MAY	62	3	55
Data

Cell Formulas
Range	Formula
C2:C5	C2	=RANK.EQ(B2,$B$2:$B$5,1)
D2	D2	=MAPRANGE(MIN($C$2:$C$5),MAX($C$2:$C$5),25,100,C2)
D3:D5	D3	=MAPRANGE(MIN($C$2:$C$6),MAX($C$2:$C$6),25,100,C3)
C9:C14	C9	=RANK.EQ(B9,$B$9:$B$14,1)
D9:D14	D9	=MAPRANGE(MIN($C$9:$C$14),MAX($C$9:$C$14),25,100,C9)

LAMBDA FORMULA:

=LAMBDA(
    input_start,
    input_end,
    output_start,
    output_end,
    input,
    output_start + ((output_end - output_start) / (input_end - input_start)) * (input - input_start)
)

 

[iazcac]

Eric's solution worked great thanks. Managed to evolve it fit my spreadsheet. Much appreciated both of you.

 

[iazcac]

Going to have a play with maprange though. Never heard of that one!!

 

[lrobbo314]

Cool. Glad you got a solution that worked.

BTW. MAPRANGE isn't a function in Excel. It's a defined LAMBDA function.

Not sure if you even have that available. You should update your profile to let people know what version of Excel you are working with.

And, I noticed that my results weren't exactly correct on my previous post. Copy pasting error. Should have looked like this.

COMPSTAT Template1
	A	B	C	D
8	Name	Score	Rank	Spread
9	MARA	24	1	25
10	ANNALISE	56	3	50
11	FRANCISCO	71	6	87.5
12	MAXWELL	66	5	75
13	CELIA	91	7	100
14	ELLIE-MAY	62	4	62.5
15	KARL	46	2	37.5
Data

Cell Formulas
Range	Formula
C9:C15	C9	=RANK.EQ(B9,$B$9:$B$15,1)
D9:D15	D9	=MAPRANGE(MIN($C$9:$C$15),MAX($C$9:$C$15),25,100,C9)