Player ranking

[Saracen628]

Hi All

Id like to set up a ranking system based on points.
When a person gains or loses points they add the number of points gained or lost (-) in column D (player points col B cant go lower than 0)
When they press enter the points counter for the relevant row in column C updates, the row moves up or down and column D goes back to being blank.
Column A would need to remain static.

But thats not all! (Please dont hate me) on Sheet 2 it would be great if a history of points gained or lost could be recorded against each player as a list.

Any help would be greatly appreciated!

Cheers
Chris

 

[Snakehips]

@Saracen628
Chris, it may help if you can clarify a couple of things.
You say Player Points column B cannot go below 0. (Assume you mean C?)
So a player history of 10,10, -10, -15, 5 would reflect as 10, 20, 10, 0 , 5 in column C, even though the overall gains and losses sum to 0 ?
Ranking is based upon this never-negative total rather than the real ongoing sum of gains and losses?
The entry scenario you have outlined will require vba. Are you ok with that?
Would you take a non-vba solution if it could be found?

 

[Saracen628]

Chris, it may help if you can clarify a couple of things.
You say Player Points column B cannot go below 0. (Assume you mean C?)
So a player history of 10,10, -10, -15, 5 would reflect as 10, 20, 10, 0 , 5 in column C, even though the overall gains and losses sum to 0 ?
Ranking is based upon this never-negative total rather than the real ongoing sum of gains and losses?
The entry scenario you have outlined will require vba. Are you ok with that?
Would you take a non-vba solution if it could be found?

Hii Snakehips
thanks for the quick response!
Apologies for the mistakes.
Yes its column C - i will probably set a starting points count at 2000 per player so there is a buffer if people have a losing streak.
id like to keep the neg (-) value for losses if possible.

im happy to provide more info where i can if needed
Id be completely happy with VBA

Cheers
Chris

 

[Snakehips]

Might something like the below offer a simple formulaic solution?
It would require losses and gains to be entered in the player's history column and the ranking table would update accordingly.

MXLBook1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Player History		Buffer >	2000
2
3	Player	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	Points Total	2025	1975	2020	2070	1965	2025	2005	2010	2010	2010	2010	2020	2010	2010	2015	2010	2010	2005	1985	2005
5	Rank	2	19	4	1	20	3	15	7	8	9	10	5	11	12	6	13	14	16	18	17
6	History	10	-15	20	20	-10	20	5	5	5	5	5	5	5	5	5	5	5	20	-5	5
7		10	5	-15	20	-5	15	10	10	10	10	10	20	10	10	15	10	10	15	-5	10
8		-10	5	10	20	-20	-25	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5	-30	-5	-10
9		15	-10	10	20		15	-5
10		-10	10	-5	-10
11		5	-20
12		-25
13		30
14
15
Player History

Cell Formulas
Range	Formula
B4:U4	B4	=SUM(B6:B2000)+$E1
B5:U5	B5	=RANK(B4,$B$4:$U$4,0)+COUNTIF($B$4:B$4,B$4)-1

MXLBook1 (version 1).xlsb
	A	B	C
1	Ranking
2	Ranking	Player	Points
3	1	D	2070
4	2	A	2025
5	3	F	2025
6	4	C	2020
7	5	L	2020
8	6	O	2015
9	7	H	2010
10	8	I	2010
11	9	J	2010
12	10	K	2010
13	11	M	2010
14	12	N	2010
15	13	P	2010
16	14	Q	2010
17	15	G	2005
18	16	R	2005
19	17	T	2005
20	18	S	1985
21	19	B	1975
22	20	E	1965
23
Ranking

Cell Formulas
Range	Formula
B3:B22	B3	=INDEX('Player History'!$B$3:$U$3,1,MATCH($A3,'Player History'!$B$5:$U$5,0))
C3:C22	C3	=INDEX('Player History'!$B$4:$U$4,1,MATCH($A3,'Player History'!$B$5:$U$5,0))

 

[Saracen628]

That looks great.
I can definitely work with this!
Thank you for your help!!

Cheers
Chris