I have a column of events,there are 7000 in my sheet.The scores for each contestant in the event are in col.2. In col 4 I have the rank printed for each contestant in each event in ascending order.I want to know how to print them in opposite order i.e highest in each event is ranked 1 etc into column 5.
Announcement
Collapse
No announcement yet.
Unconfigured Ad Widget
Collapse
reverse ranking
Collapse
X

Re: reverse ranking
from Excel Help
RANK
See Also
Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Syntax
RANK(number,ref,order)
Number is the number whose rank you want to find.
Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.
Order is a number specifying how to rank number.
If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.
If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.
Remarks
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
Examples
If A1:A5 contain the numbers 7, 3.5, 3.5, 1, and 2, respectively, then:
RANK(A2,A1:A5,1) equals 3
RANK(A1,A1:A5,1) equals 5D
Better a bad day on the water than a good day in the office

Re: reverse ranking
Decided to have a quick play and see if i could get rid of the indirect for you...this works as long as the sheet is sorted by event number and replicates the uploaded file
Code:=RANK($B2,OFFSET($B$1,MATCH($A2,$A$2:$A$26,0), 0,MATCH($A2,$A$2:$A$26,1),1),1)
Code:=RANK($B2,OFFSET($B$1,MATCH($A2,$A$2:$A$26,0),0,MATCH($A2,$A$2:$A$26,1),1),0)
D
Better a bad day on the water than a good day in the office
Comment

Re: reverse ranking
Thanks Dave,that works. There is only one problem,each event is descibed by
3 columns,date,course and time. The sheet is sorted by these 3 fields.If I put in extra column,say something that increases the number by 1 when the time changes,then it should do away with all the indirect columns, I have loads of them in my sheet.Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
Also I would like an extra column to to indicate if a contesttant is last,because ranked 4th will be last in some events and rank 14th in others.
Sorry for so many questions,Dave.
see attachedAttached Files
Comment

Re: reverse ranking
Try the following...
First, concatenate Columns A, B, C, and D:
F2, copied down:
=A2&"#"&B2&"#"&C2&"#"&D2
Then, to rank lowest to highest...
G2, copied down:
=SUMPRODUCT((OFFSET(F$2:F$26,MATCH(F2,F$2:F$26,0)1,,MATCH(F2,F$2:F$26))=F2),(E2>OFFSET(E$2:E$26,MATCH(F2,F$2:F$26,0)1,,MATCH(F2,F$2:F$26))))+1
To rank highest to lowest, change E2> to E2<
Hope this helps!
Comment

Re: reverse ranking
Thanks a lot,Domenic.
Just the 2 questions again,
Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
Also I would like an extra column to to indicate if a contestant is last,because ranked 4th will be last in some events and rank 14th in others
Comment

Re: reverse ranking
Originally posted by pyteliumThanks a lot,Domenic.
Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
Also I would like an extra column to to indicate if a contestant is last,because ranked 4th will be last in some events and rank 14th in others
Code:Event Ranked Last 1 14 2 4 3 4
I2, copied down:
=IF(G2=VLOOKUP(D2,$K$2:$L$4,2,0),"Last","")
Adjust the table and references accordingly.
Hope this helps!
Comment

Re: reverse ranking
Domenic
sorry cant get my head around this last formula at allputting in last.
attached sheet,appreciate if you could help.Attached Files
Comment

Re: reverse ranking
Have a look at the attached file. You'll notice that I set up a table containing a list of events, along with their corresponding ranking for last place. Expand/amend this list according to your data and change the table reference within the formula.
Hope this helps!Attached Files
Comment

Re: reverse ranking
Morning  looks like this has moved on a bit...so, my solution is similar to domenic in that it now uses the combined column F with the formula
Code:=A2&"#"&B2&"#"&C2&"#"&D2
Code:=RANK($E2,OFFSET($E$1,MATCH($F2,$F$2:$F$26,0),0, MATCH($F2,$F$2:$F$26,1),1),0)
Code:=IF(H2=MAX(OFFSET($H$1,MATCH($F2,$F$2:$F$26,0),0, MATCH($F2,$F$2:$F$26,1),1),0),"Last","")
Replace the $F$26 with the last row (I think this was $F$7004 in your example).
Let us know if you have any further problems or questions...
D
Better a bad day on the water than a good day in the office
Comment

Re: reverse ranking
Thanks Dave and Domenic,you have both been fantastic help.I used Domenics method for the ranking up and down and Dave's for the "last". If I might dare another question:if I wanted to label 2nd last etc in the final column as well as last,is it possible?
Comment

Re: reverse ranking
Originally posted by DaveR (UK)Morning  looks like this has moved on a bit...so, my solution is similar to domenic in that it now uses the combined column F with the formula
Code:=A2&"#"&B2&"#"&C2&"#"&D2
Code:=RANK($E2,OFFSET($E$1,MATCH($F2,$F$2:$F$26,0),0, MATCH($F2,$F$2:$F$26,1),1),0)
Comment

Re: reverse ranking
I've attached a sample file showing the ranking using both formulas. With regards to your formula, it looks like the range provided by OFFSET for Event 2 is including both rows for Event 3. I don't understand why...Attached Files
Comment
Trending
Collapse
There are no results that meet this criteria.
Comment