Calculating points and divisions of students scores in excel

Hi guys!

aim looking a simple formula to calculate the points and divisions of student score in excel.
first change numbers into letter
A starts from 100-81
B starts from 80-61
C starts from 60-41
D starts from 40-21
E starts from 20-0
after changing into A,B,c.... then you have to calculate the points for all nine subjects. here then
A have 1 point
B have 2 points
C have 3 points
D have 4 points
F have 5 points
divisions
I is between 7-17 points
II is between 18-21 points
III is between 22-25 points
IV is between 26-32 points
zero is between 33-35 points
for example
Anna have got Civics A, Chemistry C, Biology C, English B, Basic Maths C, History B, Geography C, Physics D and Kiswahili B then she have got Points 16 and Divisions I because you have to count only 7 subjects that she did best and also if she have got F in Mathematics and Civics and she obtained division I or II, she/he must be penalized to division three (III). for instance she obtained division I Points 15 then she must have III points 15.
looking forward to get the solution remember to select only seven subject that s/he has performed best

Hi

Have a look attachment

Good luck

Hi!

I checked the attachment. In fact I appreciate and congratulate you for the analysis. Am not any good in coding. The procedures return both the Division and Points nicely. But the problem remains that both the Divisions and Points are automated from SEVEN SUBJECTS only with the highest marks. If you have nine subjects, arrange then in assending order of their marks and then derive the Divisions and Points from the first seven subjects leaving out those not in the range. It doesn't matter whether the certain subjects have similar marks, what is considered is the subject in the first seven subjects.

If there is any way of coding for this let me also learn from you.

Thanks.