Multiple Criteria Ranking

[SFCChase]

I'm trying to figure out a way to rank candidates for a competition based off a couple of data points.

Unit	Name	Points Found	Time	Rank
A Co	Smith	5	3:45:00	2
A Co	Jones	5	3:38:33	1
B Co	Adams	4	3:58:44	3
B Co	Michaels	5	4:08:23	5
C Co	Williams	2	3:31:40	7
C Co	Smith	3	2:29:45	4
D Co	Duggan	4	4:38:38	6
D Co	Rogers	0	3:22:30	8

Ranking will be based of the number of points found in the shortest time. A minimum of 3 points must be found (5 max) and the maximum time allowed is 4:00:00. If the minimum requirements aren't met, points found are weighed heavier than the time for completion (i.e. Williams ranks higher than Rogers because they found more points even though both missed the minimum.....Smith ranks higher than Duggan because they both met 3 point criteria and Smith was faster).

I thought I was close with this equation but it's not ranking the time correctly (it put Michaels in 3rd instead of Adams)
=IF(D2<4,RANK(D2,D2:D9,1),RANK(C2,C2:C9,0))

I will be looking to put the formula in cell E2 and pasting down a large data sheet.

Willing to use helper cells if I need to but I have no experience with VBA.

 

[dalvin647]

Hi SFCChase,

This should work.

I had to use two helper columns.

Column F: checks if teams have met minimum criteria.
Column G: Ranking by Pts
Column H: In case of tie-breaker e.g. Smith and Jones have 5 pts, break tiebreaker based on faster time.

Multiple Ranking Criteria.xlsx
	A	B	C	D	E	F	G	H
1	Unit	Name	Points Found	Time	Rank	Criteria Check	Score Ranking	Time Adjusted Final Ranking
2	A Co	Smith	5	3:45:00	2	TRUE	1	2
3	A Co	Jones	5	3:38:33	1	TRUE	1	1
4	B Co	Adams	4	3:58:44	3	TRUE	3	3
5	B Co	Michaels	5	4:08:23	5	FALSE	5	5
6	C Co	Williams	2	3:31:40	7	FALSE	7	7
7	C Co	Smith	3	2:29:45	4	TRUE	4	4
8	D Co	Duggan	4	4:38:38	6	FALSE	6	6
9	D Co	Rogers	0	3:22:30	8	FALSE	8	8
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=AND(C2>=3,D2<TIME(4,0,0))
G2:G9	G2	=IF(F2,COUNTIFS($F$2:$F$9,F2,$C$2:$C$9,">"&C2)+1,COUNTIFS($F$2:$F$9,F2,$C$2:$C$9,">"&C2)+1+COUNTIF($F$2:$F$9,TRUE))
H2:H9	H2	=IF(COUNTIFS($G$2:$G$9,G2)>1,COUNTIFS($G$2:$G$9,G2,$D$2:$D$9,"<"&D2)+G2,G2)

 

[SFCChase]

Thanks a lot! Works great!