Placings with Multiple Criteria

[Stace_129]

Hi, this is probably really simple but I just can’t get it to work!
I have an excel sheet for a sporting events which I want to determine the placings for.

There are Three criteria for placings in this order:

least number of Non Completes
Least number of Penalties
Quickest time

so someone might have the slowest time but if they have the least non completes, least penalties then they win.

Any help with a formula is appreciated.

 

[Toadstool]

Hi Stace_129,

I don't think anybody can answer this as you must decide what the weighting is for each criteria.

Here's an example where a score is calculated using the 3 criteria. Non Complete and Penalties are both decided by multiplying by -9 but if you decide lower penalties means a higher rank then you could multiply Non Completes by -9 and Penalties by -7, so giving a higher score.

For the Time I've just taken the average time and subtracted that competitors time, so a slower time will give a lower value, then I've multiplied by 9 but if you want time to have more impact you may multiply by 50, or 99, or 500. You'll need to decide the weight of each criteria.

Stace_129.xlsx
	A	B	C	D	E	F
1	Name	Non Completes	Penaties	Time	Score	Rank
2	Fred	2	1	4:33	-28.51	3
3	Bert	1	0	4:45	-10.59	2
4	Sue	0	0	5:37	-1.91	1
5	Jim	4	3	5:11	-64.75	4
6	Sarah	4	4	5:24	-73.83	6
7	Vanessa	4	4	2:55	-72.90	5
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=(-9*B2)+(-9*C2)+(AVERAGE($D$2:$D$9999)-(D2)*9)
F2:F7	F2	=RANK.EQ(E2,$E$2:$E$9999)