How to rank the other way round + other question

shirleequah

New Member
Joined
Oct 13, 2008
Messages
2
Hi..

Can someone help me on the following:

Table to work on will look like this:

<TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=437 border=0 x:str><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2432" span=2 width=76><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 3808" width=119><COL style="DISPLAY: none; mso-width-source: userset; mso-width-alt: 2336" width=0><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2336" width=73><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 2976" width=93><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=76 height=21>TIME 1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=76>TIME 2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=119>AVERAGE TIME</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=0>PLACING</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>PLACING</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=93>POINTS</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21 x:num="8.8946759259259267E-2">02:08:05</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.12640046296296295">03:02:01</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.15214699074074073" x:fmla="=A2+B2/2">3:39:05</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(D2=1,"5")' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21 x:num="4.3090277777777776E-2">01:02:03</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="8.4062499999999998E-2">02:01:03</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="8.5121527777777775E-2" x:fmla="=A3+B3/2">2:02:34</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(D3=1,"5")' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21 x:num="0.16877314814814814">04:03:02</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="8.4039351851851851E-2">02:01:01</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.21079282407407407" x:fmla="=A4+B4/2">5:03:33</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(D4=1,"5")' x:bool="FALSE">FALSE</TD></TR></TBODY></TABLE>

1) Say I have done this ranking formula thing but I actually wanted to rank it another way round, the thing is on the average of time, what is the formula to it?
This is my current formula =RANK(G$5:G$12,G$5:G$12)

2) Next, how do I go about connecting a formula to make them assign points according to the ranking column in H?

It will goes like this: Rank 1 will have "5"points, Rank 2 will have "3"points, Rank 3 will have "2"points, Rank 4 will have "1"point, Rank 5 to 8 will have "0"points.

I only manage to do for the Rank 1 while the rest of the rank receives a word name "false"

My current formula to this is =IF(H10=1,"5")
 
Last edited:

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
Do you mean

=RANK(G5,$G$5:$G$12,1)

instead of

=RANK(G5,$G$5:$G$12,0)

that is, ascending vs descending?
 

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767
2) Next, how do I go about connecting a formula to make them assign points according to the ranking column in H?

It will goes like this: Rank 1 will have "5"points, Rank 2 will have "3"points, Rank 3 will have "2"points, Rank 4 will have "1"point, Rank 5 to 8 will have "0"points.
Few ways of doing this bit but one might be:

=IF(H10=1,5,IF(H10>4,0,5-H10))
 

Watch MrExcel Video

Forum statistics

Threads
1,095,205
Messages
5,443,015
Members
405,212
Latest member
Arnie58

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top