i am working on my database in excel...I need to rank the Scorers by considering 4 league scores.I want Ranking based on Scores of leagues with priority from 1 to 4 i.e. who scored most in league 1 would be 1st and if same score in league 1 then consider league 2 scores and so on...my table is as following- please if u know help me for ranking for my table...
..........A............B...............C................D..................E..............F
======= ======= ======= ======= ================
1.......ID......Score 1......Score 2......Score 3......Score 4......Rank
2...... N01.... 12............... 4................. 3................ 18
3...... N02.... 15............... 4................. 6................ 13
4...... N03.... 12............... 5................. 4................ 11
5...... N04.... 12............... 4................. 3................ 3
6...... N05.... 15............... 5................. 5................ 8
7...... N06.... 16............... 3................. 2................ 3
8...... N07.... 12............... 4................. 5................ 12
9...... N08.... 10............... 5................. 4................ 9
10.....N09.... 13............... 6................. 4................ 17
11.....N10.... 10............... 5................. 4................ 5
For ranking based on 2 fields i have used these formulas:
intermidiate total in F2 to F11 -
=RANK(B2,B$2:B$11,0)+SUMPRODUCT((B2=B$2:B$11)*(C2
and The Rank in G2 - =RANK(F2,F$2:F$11,1)
But I cannot do it for ranking based on 4 columns...Help plz
Of course the easiest way to do this is simply to do Data > Sort and do 4 levels of sorting (Score1, ..., Score4), but I kinda liked this question to see if I could do it with formulas - A fun challenge...
The nicest way I could think of doing it without VBA would be to add in a few helper columns.
To do this, put the following formulas in:
F2: =B2
G2: =F2 & " - " & SUM(IF(B2=B$2:B$11,IF(C2>=C$2:C$11,1,0)))
And enter G2 as an array formula using ctrl + shift + enter.
Then, drag G2 across till I2 and then, just fill Columns F:I down for your dataset.
Now, if you sort based upon column I, you will have the data ordered as you want it.
Of course, you could make it all in one large and ugly formula in one cell per row:
=B2& " - " & SUM(IF(B2=B$2:B$11,IF(C2>=C$2:C$11,1,0)))& " - " & SUM(IF(C2=C$2:C$11,IF(D2>=D$2:D$11,1,0)))& " - " & SUM(IF(D2=D$2:D$11,IF(E2>=E$2:E$11,1,0)))
but this seems to be a better solution IMHO.
Hope this does the trick!
EDIT:
In response to your comment below, to get the rank, you'll need 2 extra columns (boy, this is getting long :p)...
Put in the following:
J2: =SUMPRODUCT((I2>=$I$2:$I$11)+0)+1
K2: =RANK(J2,$J$2:$J$11,1)+COUNTIF($J$2:OFFSET(J2,0,0),J2)-1
This will give you the rank using non-numeric data.
Hope this fully satisfies your criteria, but it's pretty ugly for sure... Like I said, I enjoyed the challenge of this, but the better way would be to just create a VBA function that took care of this for you...