Obtaining a Rank by multiple columns of values.

[Chiefee]

Hi,

I am creating a football score predictions for my work but struggling creating the rank formula to update the scores for the season.

I have managed to rank the table by the points scored and it to then be based on 4 pointers scroed if there is an equal points score but i cannot seem to extend the formula to include the 3 pointers, 2 pointers and 1 pointers if these turned out to be equal too.

Can anyone assist with such formula?

much appreciated..

Paul

Copy of Football Prediction V4.xlsm
	L	M	N	O	P	Q	R
6	Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points	Rank
7	Player 1	1	1	1	1	10	17
8	Player 2	2	0	1	0	10	18
9	Player 3	0	0	0	0	0	1
10	Player 4	3	1	0	0	15	19
11	Player 5	0	0	0	0	0	1
12	Player 6	4	0	0	0	16	20
13	Player 7	0	0	0	0	0	1
14	Player 8	0	0	0	0	0	1
15	Player 9	0	0	0	0	0	1
16	Player 10	0	2	1	1	9	1
17	Player 11	0	0	0	0	0	1
18	Player 12	0	0	0	0	0	1
19	Player 13	0	0	0	0	0	1
20	Player 14	0	0	0	0	0	1
21	Player 15	0	0	0	0	0	1
22	Player 16	0	0	0	0	0	1
23	Player 17	0	0	0	0	0	1
24	Player 18	0	0	0	0	0	1
25	Player 19	0	0	0	0	0	1
26	Player 20	0	0	0	0	0	1
Overall Season Score

Cell Formulas
Range	Formula
L7:L26	L7	=T7
M7:P26	M7	='Week 1'!C6+'Week 2'!C6+'Week 3'!C6+'Week 4'!C6+'Week 5'!C6+'Week 6'!C6+'Week 7'!C6+'Week 8'!C6+'Week 9'!C6+'Week 10'!C6+'Week 11'!C6+'Week 12'!C6+'Week 13'!C6+'Week 14'!C6+'Week 15'!C6+'Week 16'!C6+'Week 17'!C6+'Week 18'!C6+'Week 19'!C6+'Week 20'!C6+'Week 21'!C6+'Week 22'!C6+'Week 23'!C6+'Week 24'!C6+'Week 25'!C6+'Week 26'!C6+'Week 27'!C6+'Week 28'!C6+'Week 29'!C6+'Week 30'!C6+'Week 31'!C6+'Week 32'!C6+'Week 33'!C6+'Week 34'!C6+'Week 35'!C6+'Week 36'!C6+'Week 37'!C6+'Week 38'!C6+'Week 39'!C6+'Week 40'!C6
Q7:Q26	Q7	=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26	R7	=RANK.EQ(M7,$M$7:$M$26,1)

 

[Fluff]

One option

+Fluff 1.xlsm
	L	M	N	O	P	Q	R
6	Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points	Rank
7	Player 1	1	1	1	1	10	14
8	Player 2	2	0	1	0	10	17
9	Player 3	0	0	0	0	0	1
10	Player 4	3	1	0	0	15	19
11	Player 5	0	0	0	0	0	1
12	Player 6	4	0	0	0	16	20
13	Player 7	0	0	0	0	0	1
14	Player 8	0	0	0	0	0	1
15	Player 9	1	2	2	1	15	16
16	Player 10	1	2	1	1	13	15
17	Player 11	1	0	2	0	8	13
18	Player 12	0	4	2	0	16	11
19	Player 13	1	0	1	0	6	12
20	Player 14	0	0	0	0	0	1
21	Player 15	3	0	1	0	14	18
22	Player 16	0	0	0	0	0	1
23	Player 17	0	0	0	0	0	1
24	Player 18	0	0	0	0	0	1
25	Player 19	0	0	0	0	0	1
26	Player 20	0	0	0	0	0	1
Main

Cell Formulas
Range	Formula
Q7:Q26	Q7	=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26	R7	=RANK(M7,$M$7:$M$26,1)+COUNTIFS($M$7:$M$26,M7,$N$7:$N$26,"<"&N7)+COUNTIFS($M$7:$M$26,M7,$N$7:$N$26,N7,$O$7:$O$26,"<"&O7)+COUNTIFS($M$7:$M$26,M7,$N$7:$N$26,N7,$O$7:$O$26,O7,$P$7:$P$26,"<"&P7)

Also if your weekly sheets are consecutive you could simplify the formula in M7 to

=SUM('Week 1:Week40'!C6)

 

[Chiefee]

One option

+Fluff 1.xlsm
	L	M	N	O	P	Q	R
6	Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points	Rank
7	Player 1	1	1	1	1	10	14
8	Player 2	2	0	1	0	10	17
9	Player 3	0	0	0	0	0	1
10	Player 4	3	1	0	0	15	19
11	Player 5	0	0	0	0	0	1
12	Player 6	4	0	0	0	16	20
13	Player 7	0	0	0	0	0	1
14	Player 8	0	0	0	0	0	1
15	Player 9	1	2	2	1	15	16
16	Player 10	1	2	1	1	13	15
17	Player 11	1	0	2	0	8	13
18	Player 12	0	4	2	0	16	11
19	Player 13	1	0	1	0	6	12
20	Player 14	0	0	0	0	0	1
21	Player 15	3	0	1	0	14	18
22	Player 16	0	0	0	0	0	1
23	Player 17	0	0	0	0	0	1
24	Player 18	0	0	0	0	0	1
25	Player 19	0	0	0	0	0	1
26	Player 20	0	0	0	0	0	1
Main

Cell Formulas
Range	Formula
Q7:Q26	Q7	=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26	R7	=RANK(M7,$M$7:$M$26,1)+COUNTIFS($M$7:$M$26,M7,$N$7:$N$26,"<"&N7)+COUNTIFS($M$7:$M$26,M7,$N$7:$N$26,N7,$O$7:$O$26,"<"&O7)+COUNTIFS($M$7:$M$26,M7,$N$7:$N$26,N7,$O$7:$O$26,O7,$P$7:$P$26,"<"&P7)

Also if your weekly sheets are consecutive you could simplify the formula in M7 to

=SUM('Week 1:Week40'!C6)

Thank you ever so much!!! if the pubs were open i would buy you a beer!! My league table now auto sorts perfectly!

Thank you also for the extra help! I had tried a shorter formula to add all of the weeks scores but was also unable. Your idea is similar but it does not seem to work, it highlights the 'Week 40!' part of it. Im not to sure why this is not working.

 

[Fluff]

Do all forty sheets exist?

EDIT:
Just realised I missed a space on the sheet name, it should be

=SUM('Week 1:Week 40'!C6)

 

[Chiefee]

Do all forty sheets exist?

EDIT:
Just realised I missed a space on the sheet name, it should be

=SUM('Week 1:Week 40'!C6)

Spot on!!

My last challenge for my spreadhseet, i was thinking of adding to my league table the movement of positions throughout the season. Ideally showing in the colum next to the points how many positions someone had moved, eg. Player 1 down 4, Player 2 same, Player 3 up 3 etc. Is this too complicated? Or would a simple green arrow up, red arrow down and a yellow arrow for the same be achievable?

 

[Fluff]

To do that you would need to know what their rank was the previous week.

 

[Chiefee]

Thank you ever so much!!! if the pubs were open i would buy you a beer!! My league table now auto sorts perfectly!

Thank you also for the extra help! I had tried a shorter formula to add all of the weeks scores but was also unable. Your idea is similar but it does not seem to work, it highlights the 'Week 40!' part of it. Im not to sure why this is not working.

I have just come across a slight glitch. The order to which it works out the order if there is a tie is spot on but it does not rank the points tally first. what would i need to add to rank the points first before the rest?

 

[Chiefee]

No worries, slight change to your formula and its working!!

 

[Fluff]

Glad you sorted it & thanks for the feedback.