Obtaining a Rank by multiple columns of values.

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
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

Cell Formulas
RangeFormula
L7:L26L7=T7
M7:P26M7='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:Q26Q7=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26R7=RANK.EQ(M7,$M$7:$M$26,1)
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
One option
+Fluff 1.xlsm
LMNOPQR
6Player Name4 Pointers3 Pointers2 Pointers1 PointersPointsRank
7Player 111111014
8Player 220101017
9Player 3000001
10Player 431001519
11Player 5000001
12Player 640001620
13Player 7000001
14Player 8000001
15Player 912211516
16Player 1012111315
17Player 111020813
18Player 1204201611
19Player 131010612
20Player 14000001
21Player 1530101418
22Player 16000001
23Player 17000001
24Player 18000001
25Player 19000001
26Player 20000001
Main
Cell Formulas
RangeFormula
Q7:Q26Q7=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26R7=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
Excel Formula:
=SUM('Week 1:Week40'!C6)
 
Solution

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
One option
+Fluff 1.xlsm
LMNOPQR
6Player Name4 Pointers3 Pointers2 Pointers1 PointersPointsRank
7Player 111111014
8Player 220101017
9Player 3000001
10Player 431001519
11Player 5000001
12Player 640001620
13Player 7000001
14Player 8000001
15Player 912211516
16Player 1012111315
17Player 111020813
18Player 1204201611
19Player 131010612
20Player 14000001
21Player 1530101418
22Player 16000001
23Player 17000001
24Player 18000001
25Player 19000001
26Player 20000001
Main
Cell Formulas
RangeFormula
Q7:Q26Q7=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26R7=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
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
Do all forty sheets exist?

EDIT:
Just realised I missed a space on the sheet name, it should be
Excel Formula:
=SUM('Week 1:Week 40'!C6)
 

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Do all forty sheets exist?

EDIT:
Just realised I missed a space on the sheet name, it should be
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
To do that you would need to know what their rank was the previous week.
 

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
No worries, slight change to your formula and its working!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top