Ranking with Multiple tiebreakers

[Yoti14]

Good evening,

I'm looking for some help. I created a workbook, that has functions across sheets and tables(2). I created this book before I actually had any training in excel, and that was only an intro. It may seem a bit amature, but it is functional. I would like to make even more so, by adding some additional tricks, like Auto sorting, and ranking, transferring information to another cell and and onto another sheet. I'm going to post some examples of the situation of ranking I'm looking to do. I've used my row and column numbers to simplify anyone's answer for me to understand.

This is the qualifying Table after manually sorting by Speed (highest to Lowest), more about that function another time

	QUALIFYING
	AE	AF	AG	AO	AP
3	ST POS	DRIVER	CAR#	SPEED	QUAL POS
4	1	A	3	155	3
5	2	L	24	154	1
6	3	B	2	153	12
7	4	E	78	152	2
8	5	H	88	151	10
9	6	C	19	150	4
10	7	G	1	149	11
11	8	F	46	148	5
12	9	K	11	147	6
13	10	D	23	146	9
14	11	I	22	145	7
15	12	J	34	144	8

<tbody>
</tbody>

These are the Heat tables. I'm only showing the information I feel is pertinent to my question.

		HEAT 1
	A	B	C	N	O	P	S
2	LN	DRIVER	CAR#	POINTS	WINS	FINISH	RANK
3	4	A	3	58	4	7
4	3	E	78	55	2	8
5	2	G	1	37	1	6
6	1	D	23	39	1	6
7
8		HEAT 2
9	LN	DRIVER	CAR#	POINTS	WINS	FINISH	RANK
10	4	L	24	47	2	8
11	3	H	88	57	3	8
12	2	F	46	56	2	8
13	1	I	22	43	1	8
14
15		HEAT 3
16	LN	DRIVER	CAR#	POINTS	WINS	FINISH	RANK
17	4	B	2	50	2	7
18	3	C	19	39	1	7
19	2	K	11	50	2	8
20	1	J	34	59	3	8
21
22		SEMI HEAT 1
23	LN	DRIVER	CAR#	POINTS	WINS	FINISH	RANK
24	4	(RANK 1)	(R1)
25	3	(RANK 3)	(R3)
26	2	(RANK 5)	(R5)
27	1	(RANK 7)	(R7)
28
29		SEMI HEAT 2
30	LN	DRIVER	CAR#	POINTS	WINS	FINISH	RANK
31	4	(RANK 2)	(R2)
32	3	(RANK 4)	(R4)
33	2	(RANK 6)	(R6)
34	1	(RANK 8)	(R8)
35
36		CHAMPION HEAT
37	LN	DRIVER	CAR#	POINTS	WINS	FINISH	RANK
38	4	(SH RANK 1)	(SHR1)
39	3	(SH RANK 2)	(SHR2)
40	2	(SH RANK 3)	(SHR3)
41	1	(SH RANK 4)	(SHR4)

<tbody>
</tbody>

What needs to be accomplished here, is after the races are completed in each heat, i need the drivers to be ranked by points. The tie breakers would be wins, then, finish, and lastly speed from the qualifying. Once the rankings are completed, the driver names and car # need to be transferred to the next heats. I've put in the locations where each rank should go.

Ok, so there's my problem. I hope its not to much information. Hopefully someone can help me. If whomever wants to tackle this issue for me, i can also send you the workbook by email, if that would help.

Thank you,
Jeff

 

[DRSteele]

What you need to do to use Excel's Rank functions is create a new statistic (call it Grade if you like) that adds the fields together in decreasing order of magnitude.

Look over this link (Posts 3 and 4) that exhibit the principle. https://www.mrexcel.com/forum/excel...sing-3-columns-data.html?highlight=rank+grade

Be careful to understand the orders of magnitude. Each tiebreaker must be divided by a figure such that it can never tick-over the field to its left. So if two teams have 4 wins and Winner's score is the first tie-breaker, those (eg. 59 and 61) have to be divided by a number large enough so that it can go over 1 and add a Win. Here 10 obviously won't do, so 100 might be the maximum Winner's score a team could have. So Team1 gets a Grade of 4 + 59/100 = 4.59 and Team8 gets a Grade of 4 + 64/100 = 4.61. Subsequent fields must be then divided out the same way. So Team1 gets a Grade of 4 + 59/100 + 63/(100*100) = 4.5963 and Team8 gets a Grade of 4 + 64/100 + 55/(100*100) = 4.6155. And there you have it: Team8's Grade outranks Team1's.

In your case, you will want to invert the Finish field because that statistic is lower-the-better.