Rank with Tie Breakers

[SFCChase]

Is there a way to rank a series of data that breaks ties by looking at additional data?

For example. I have 9 students with calculated GPA's in column B (3 of which have an 88.5 average). I want to rank them 1-9 but for the 3 tied GPA I would like the formula to automatically look in column C for the lowest amount of test failures (of the 3 tied individuals).

NAME	GPA	TEST FAILURES	RANK
JOE	88.5	2	6
JANE	92.0	0	2
BOB	91.5	0	3
LUCY	88.5	0	4
ROGER	87.0	1	7
MIKE	86.5	0	8
CHUCK	88.5	1	5
BETTY	75.5	0	9
DORIS	96.0	0	1

 

[DRSteele]

Yes, there is a way. There are probably other ways too, some of them even better. But this one works nicely: