Ranking RANK

[Dirty Northerner]

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>

 

[AD_Taylor]

Have a look at my example below

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M
2	H/Cap	Score	Hits	Golds	Allowance	Total Score	Hits Rank	Golds Rank	Score Rank	Overall Rank	Actual Place
3	39	357	54	65	671	1028	6	1	1	116	1
4		153	55	61		153	5	6	6	665	6
5	42	348	56	62	615	963	4	5	2	254	3
6	42	348	57	63	615	963	3	4	2	243	2
7	45	349	58	64	554	903	2	3	4	432	4
8	52	349	59	65	396	745	1	1	5	511	5
9
10
Sheet1

Cell Formulas
Range	Formula
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!

 

[jimrward]

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

 

[Marcelo Branco]

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.

 

[Dirty Northerner]

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.

 

[jimrward]

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

 

[jimrward]

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

 

[Marcelo Branco]

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.

 

[Dirty Northerner]

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?

 

[Marcelo Branco]

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.