# Ranking RANK

#### Dirty Northerner

##### New Member
Coming back to Excel after a few years away, so....
I'm creating an Archery Competion results, I have =IF(D3="","",RANK(H3,H\$3:H\$10)) the final score to set them in 1st, 2nd, 3rd etc but, in archery, joint placing is then determined by Gold hits, and if that doesn't resolve it, then actual Hits on target.
So the query is how do I check first, IF there is a duplicate, and then check for other criteria to determine true placing?.
H3 is the top, 1st place, score.
Waiting with bated breath...
DN
 H/Cap Score Hits Golds Allowance Total Score Place 39 357 54 65 671 1028 1 153 55 61 153 6 42 348 56 62 615 963 2 42 348 57 63 615 963 2 45 349 58 64 554 903 4 52 349 59 65 396 745 5

<colgroup><col style="width: 48pt;" span="4" width="64"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;" width="95"> <col style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;" width="49"> <tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Have a look at my example below

Excel 2010
CDEFGHIJKLM
2H/CapScoreHitsGoldsAllowanceTotal ScoreHits RankGolds RankScore RankOverall RankActual Place
339357546567110286111161
415355611535666656
54234856626159634522543
64234857636159633422432
74534958645549032344324
85234959653967451155115
9
10
Sheet1
Cell Formulas
RangeFormula
I3=IF(\$D3="","",RANK(E3,E\$3:E\$10))
J3=IF(\$D3="","",RANK(F3,F\$3:F\$10))
K3=IF(\$D3="","",RANK(H3,H\$3:H\$10))
L3=IF(\$D3 = "", "", (K3*100) + (J3 * 10) + I3)
M3=IF(\$D3 = "", "", RANK(L3, \$L\$3:\$L\$10, 1))

What I've basically done is ranked all 3 columns and then combined these into an overall rank. The person with the lowest overall rank is the winner so I've just consolidated these with a rank formula to get the actual placings.
You could then hide these helper columns if you wanted to only show the overall place.

HTH!

keep it simple and create another column, in this column have
=total + (gold/1000) + (hits /1000000) then rank on this as follows

=H3+(F3/1000)+(E3/1000000)

set the format to number and 6 decimal places and you should see 1028.065054 in the first instance, copy this down and RANK on these values, if you need to add another criteria just add it on and increase the divisor by a multiple 1000

this is the way I rank shooting results where we also have a tie break/countback scenario

Or maybe this formula

=IF(D3<>"",SUMPRODUCT(--(\$H\$3:\$H\$100+\$F\$3:\$F\$100/(10^3)+\$E\$3:\$E\$100/(10^6)>H3+F3/(10^3)+E3/(10^6)))+1,"")

M.

Thanks guys for the quick replies, but have to go with Jimrward. Such a simple solution which can be expanded almost infinitly.
Keep on doin' what you're doin' for the likes of us...less talented.

Marcelo solution is the same as mine in a different way and without using a helper column
Sumproduct can be used for ranking as well I also use it

My method I have used for years only problem is when you exhaust ties then you get the placings in list order

Another trick I use because shooting scores are out of 100 I subtract 1 before dividing to stop the numbers jumping decimal places

Marcelo solution is the same as mine in a different way and without using a helper column
Sumproduct can be used for ranking as well I also use it

I seldom use ORDER to rank.

Either COUNTIF(S) - not possible in the this situation (as far as i know) because the formula requires operations (adding) on the ranges - or SUMPRODUCT

And, in fact, my formula follows the same idea you suggested, but doesn't require extra columns,

M.

If the truth be told Marcelo, it was only that I would be able to "remember" and apply Jimrward's solution that I used it.
Having said that, can you inform me what the -- as in SUMPRODUCT(--(\$H\$3:\$H\$100+\$F etc. is?

If the truth be told Marcelo, it was only that I would be able to "remember" and apply Jimrward's solution that I used it.
Having said that, can you inform me what the -- as in SUMPRODUCT(--(\$H\$3:\$H\$100+\$F etc. is?

As an argument of the SUMPRODUCT funtion there is a comparison. ie

\$H\$3:\$H\$100+\$F\$3:\$F\$100/(10^3)+\$E\$3:\$E\$100/(10^6)>H3+F3/(10^3)+E3/(10^6)

that generates an array of logical (boolean) values either True or False, like this

{FALSE;TRUE;TRUE;FALSE..................}

-- the so called double unary operator coerces (converts) the True and False values to, respectively, 1 and 0.

resulting in
{0;1;1;0.........}

At last, the SUMPRODUCT function, that requires arrays of numbers to work properly, adds these values doing, in essence, a counting.

For a very good explanation about SUMPRODUCT and the use of -- take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

M.

Replies
3
Views
229
Replies
3
Views
4K
Replies
4
Views
862
Replies
1
Views
600
Replies
5
Views
534

1,203,094
Messages
6,053,506
Members
444,667
Latest member
KWR21

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

### Which adblocker are you using?

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

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