Create Grade Sheet in MS Excel

I’m a teacher and wish to automate the creation of a grade sheet in MS Excel. Can you please help me to do this using the standard Excel features (no VBA)? Our school conducts tests on every Wednesday and a term exam in September and February. Weekly tests carry 20 points and are weighted at 20%. Terminal exams carry 50 and 100 points and are weighted at 40% each. At the end of the year students are graded according to the table given below:

% Points         Grade
0                          F
40%                    E-
50%                    E
60%                    D
70%                    C
80%                    B
90%                    A-
95%                    A
98%                    A+

To be promoted to the next class students need to score at least 40% points or get grade E-. The subjects are for example:English, Math, Science, etc. How will the final report look?
Many people would like to know how to create grade sheets in MS Excel and finally print a certificate.We first enter the assumptions like total points and their weight. Next we enter the grades we would like to assign using a table array. We name the table array as ‘grades’ and later use it in a Vlookup function. Since the total points in the unit tests, mid-term exam and the finals are different we ensure that our data entry is less error prone by using data validation. Although we have just calculated the total percentage points and the relevant grades, we can also easily find out the lowest and highest points among the students in each subject and in each test or exam and display them in our report. The report is created by using the ‘import’ feature and creating a new Excel sheet. Now the new report Excel sheet is used as a data source for the mail-merge document in MS-Word to create the final certificate. Since the mail-merge process is automated you can make the Word document as jazzy as you like and then print out for all the candidates. In fact you can get as many details into the certificate as possible depending on what your institute or school needs.

Leave a Reply

Your email address will not be published. Required fields are marked *