Excel ranking based on two columns

[carwardinet]

I am trying to rank based on two columns.

Firstly ranking on a number (grade) and then secondly ranking on a score for students all on the same grade. The second score is an average of their previous scores (column D)

The grades are split into +/- (e.g 4+/4/4-) but the overall grading needs to be on full whole grade (4/5/6). The idea is all the 5+ would be ranked and then 5 would be ranked beneath these and then 5-. I have converted the subgrades to points values.

Column L is how the final rank should look.

[JeteMc]

Hello carwardinet and Welcome to Excel Forum.
This proposal employs three helper columns (M:O) which may be moved and/or hidden for aesthetic purposes:
The Whole Grade column is populated using: =IF(ISTEXT([@TAG]),LEFT([@TAG])+0,[@TAG])
The TAG + TSR/100 column is populated using: =SUM([@[TAG PTS]],[@[AVE TA]]/100)
The TAG + TSR/100 Rank column is populated using: =SUMPRODUCT(([Whole Grade]=[@[Whole Grade]])*([TAG + TSR/100]>[@[TAG + TSR/100]]))+1
So that the Whole Grade Rank column can be populated using: =[@[Whole Grade]]&" "&[@[TAG + TSR/100 Rank]]
Let us know if you have any questions.