calculation of points and division in excel

[Edward Masoya]

Dear Experts!

I want to calculate the points and division of the students scored in 9 subjects but you have to choose only seven subjects that the students has scored mostly. The marks of the students are shown in terms of A,B,C,D and F where A=1,B=2, C=3,D=4 and F=5. how can i do it? if forexample a students scored A,B,B,B,D,C,C,B,D,A. Manually the student has scored points 13 and Division I.

[alansidman]

Perhaps you could post a sample spreadsheet showing what you have and what you would like it to look like. I am not following your request. What is the criteria for establishing the division. Which seven scores should be chosen? Highest value or lowest value?

Alan

[Edward Masoya]

Thanks alot for your response.

you have to choose the seven subjects that a student has perfomed well. here is a solved example but how can i code and solve it?
maso.xlsx
PLEASE IF YOU NEED MORE INFORMATION I WILL PROVIDE IT TO YOU. YOUR INFOMATION IS HELPFULL

[alansidman]

Edward,
I hope this is what you are looking for. Look at the formulae behind all the cells. I changed things up a bit.

Alan

[Edward Masoya]

Thank you Alansidman for the reply!
i wanted to calculate the division and points of students as you did early but this time want to calculate if the student got F in Mathematics and other subjects he/she did well then the division should be forexample, points 15 and Division I and it should be points 15 but division III. also if a student was absent during the examination it should give me XXXXXXXX OR ABS. How can this be done? i have made changes on the Attachment and you can also edit the attachment and do according to the above information.

Looking forwad to get your excellence reply!!!!
Points 7-17
Division I=7-17, II=18-21, III=21-25,IV=26-30 and Zero=31-35
If it happen that a student has got F in mathematics and he got division I or II, he should be pernalized to Division III but he has the previous points

I have changed the attachment. Please have a look on it

[alansidman]

Attached is another solution.

[alansidman]

Edward,
I am not sure how to automatically change the Division without the use of VBA and we will need someone else to step in to assist with that as it will involve numerous case statements. You may wish to do some research on Case statements in VBA and attempt it on your own and then post back when you have issues. Here is a link to Case statements, but there are many more out there.

http://www.techonthenet.com/excel/formulas/case.php

However, I was able to give you a fix/workaround if you care to employ it. If any student has an F in any subject, then I have conditionally formatted the division to show up as yellow. Look at the formula for conditional format to see how this was accomplished.

Here is link on conditional formatting

http://www.techonthenet.com/excel/qu...rmat1_2007.php

You had mentioned that the last file was read only. When you download it, save it and rename to make it available to you.

Alan