Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Senior Member
Join Date
23rd July 2005
Posts
184

## reverse ranking

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.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: reverse ranking

from Excel Help
RANK

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 5

3. ## 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
VB:
```=RANK(\$B2,OFFSET(\$B\$1,MATCH(\$A2,\$A\$2:\$A\$26,0),
0,MATCH(\$A2,\$A\$2:\$A\$26,1),1),1)

```
and this does the same, but list the order the other way round
VB:
```=RANK(\$B2,OFFSET(\$B\$1,MATCH(\$A2,\$A\$2:\$A\$26,0),0,MATCH(\$A2,\$A\$2:\$A\$26,1),1),0)

```

4. Senior Member
Join Date
23rd July 2005
Posts
184

## 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 attached

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## 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!

Excel Video Tutorials / Excel Dashboards Reports

6. Senior Member
Join Date
23rd July 2005
Posts
184

## 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

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## Re: reverse ranking

Originally Posted by pytelium
Thanks a lot,Domenic.
You're very welcome!

Will the \$a\$26 have to be replaced by the last row in my sheet in the formula e.g \$a\$7004.
Yes. In my example, all \$26 references would be replaced with \$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
Let K1:L4 contain the following table...

VB:
```Event	Ranked Last
1	14
2	4
3	4

```
Then use the following formula to indicate which contestant is last...

I2, copied down:

=IF(G2=VLOOKUP(D2,\$K\$2:\$L\$4,2,0),"Last","")

Adjust the table and references accordingly.

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

8. Senior Member
Join Date
23rd July 2005
Posts
184

## Re: reverse ranking

Domenic

sorry cant get my head around this last formula at all--putting in last.

attached sheet,appreciate if you could help.

Excel Video Tutorials / Excel Dashboards Reports

9. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## 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!

Excel Video Tutorials / Excel Dashboards Reports

10. ## 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
VB:
```=A2&"#"&B2&"#"&C2&"#"&D2

```
then to rank the various event I use the following in H
VB:
```=RANK(\$E2,OFFSET(\$E\$1,MATCH(\$F2,\$F\$2:\$F\$26,0),0,
MATCH(\$F2,\$F\$2:\$F\$26,1),1),0)

```
and to find the last place for each event I would use
VB:
```=If(H2=MAX(OFFSET(\$H\$1,MATCH(\$F2,\$F\$2:\$F\$26,0),0,
MATCH(\$F2,\$F\$2:\$F\$26,1),1),0),"Last","")

```
in column I

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...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno