You have just received a trimester grade report which was obviously created by a computer. How is it organized? What kind of thought went into the layout? What kinds of calculations are involved? Report generation is an important topic and indeed an important task in Information Technology. In this project you will reverse engineer the layout of your trimester grade report in Excel, automate the calculation of GPA, extend the functionality to the calculation of a trimester average, and then personalize your report card so that it could potentially serve as a template for more exciting reports than the one you just received.
Please keep a copy of your report or get the basic data entered on the day you receive it. You may use test data as is done in the example, impersonal report, but it will be more useful to work with your own data and calculate your own averages. Comparing Excel's calculations with the school's will help us reverse engineer the underspecified algorithm that is used for GPA. The calculations we will use are contained in GradesCode.txt, which you will not retype, but paste into a VBA module. The project is divided into four short parts. Some instructions describe the final outcome without detailing the steps, while coding parts include step by step instructions. The final result, minus your name, will be judged by you and your peers and your preferences might be fed back to the maker of the report.
Note: Changes to the original set of instructions are indicated in bold type.
The first step is to reproduce the current grade report as closely as possible. How to do this in detail is left up to you, but there are several properties of the original that should be preserved. This eases the conversion from the old system to the new and also provides practice with Excel. These seem to be the most obvious:
Two (or more) different font sizes
Bold and normal fonts
Cells merged vertically
Cells merged horizontally
Horizontally centered text
Vertically centered text
Borders galore
Automatic calculation of Weighted Points from Points and Weght
Date in mm/dd/yyyy format
Single page
Calculation of weighted points
Display of numbers to 0 and 2 decimal places
Student cumulative average calculation. For this sum the values in the Weighted Points column and divide by the sum of the values in the Weight column.
The cumulative averages on the grade report are very likely calculated by computer. Although Excel has an AVE() function to compute the average of numbers, it does not include a similar function for letters. This is understandable because there are for one many different conversions between letters and numbers. Some schools use a five point system. Some allow A+ and F-. Some use different cutoff values. Those details published in the Parent Student handbook (page 12) are provisionally implemented in the code you can download from the link above. The code includes just one public function, averageGrade(letters As Range), that can be used to calculate the averages.
If your file is of type .xlsx, an Excel Workbook, save it as an .xlsm file. The Excel Macro-Enabled Workbook will necessary in order for the macro to work.
Open GradesCode.txt, which you can do right in the web browser. You can copy and paste it from there.
Click the Visual Basic icon on the Developer Tab to open the editor. Right click on the project node in the tree in the upper left and Insert a Module.
Paste in the code and save.
Before you automate the calculation, be sure to have recorded the official averages. You will want to compare this version to the automated version.
For the cells in the Grade column of the Cumulative Average area, enter the formula to calculate the average. The function name is averageGrade and the argument should be the range that contains the two letter grades so far. For example, it might be averageGrade(C10:D10).
Copy the formula to all lines requiring an average.
If the results differ from the original values, let your teacher know.
The Points column just after the grade can be automated as well. It is just the result of the formula letterToNumber() on the value to its left. In the example spreadsheet, this means H10=letterToNumber(G10).
It's great to know the average grade in a class, but awards like honor roll depend on the trimester average. This is the average down the trimester columns rather than across. The same averageGrade() function can also calculate vertically. These steps will create an average, but you are welcome to extend the reproduced version differently:
Make sure there is extra space in the row just under your last class under the teacher and trimester columns.
Under the last teacher add the label Average.
To its right, under the first trimester, use the formula =averageGrade() with its range parameter all of the graded cells just above it. For example, =averageGrade(C10:C18) is used on the example spreadsheet.
Copy the formula one cell to the right so that the overage is also calculated for the second trimester.
You are probably familiar with "skins". Wikipedia defines it like this: "In computing, a skin is a custom graphical appearance achieved by the use of a graphical user interface (GUI) which can be applied to specific software and websites to suit its the purpose, topic, or the tastes of different users." You have added skins to several graphs in the recent past. Do so again. Keep the data and calculations as they are, but change the visual elements to create a more interesting report. Several possibilities are listed below. Personalize the report in at least two different ways. Say in the email what you have personalized, especially if your choice isn't in the list, but even if it is. What isn't a choice is the requirement to remove your name and government number (which should not have been there to start) from the document. Change your grades if you want even more privacy. We will again complete for the best design. Rather than printing PDFs, copy and paste your grade report into a graphic (.gif, .png, .jpg, .jpeg) which can be displayed directly on a web page. It may need to be pasted into Paint and saved from there. Ask for a demonstration if you haven't done this before.
Create an avatar for yourself ("a computer user's representation of himself/herself").
Include a graphic related to your homeroom.
Add a smiley face to every good grade, a frowny face to every bad one.
Color the report
Make a chart showing your grades as they change throughout the year.
Arrange the courses in alphabetical order.
Order the grades, and their courses, from best to worst.
Include a blank for a parent signature.
You should have a single Excel workbook and a graphic to either submit via email or a USB drive. Attach them to the same single email or transfer them together via USB on or before the due date which will be announced in class and published on the Projects page. In the email or in person explain the ways in which you have personalized the report.
Here are the items that will appear on the grade sheet. Remember that you can and must personalize this project in at least two ways and depersonalize it by removing name and government number.
Two (or more) different font sizes
Bold and normal fonts
Cells merged vertically
Cells merged horizontally
Horizontally centered text
Vertically centered text
Borders galore
Automatic calculation of Weighted Points from Points and Weight
Date in mm/dd/yyyy format
Single page
Calculation of weighted points
Display of numbers to 0 and 2 decimal places
Student cumulative average calculation
Excel Macro-Enabled Workbook
Insert a Module
Paste in the code and save
=averageGrade(C10:D10).
Copy the formula to all lines requiring an average.
First personalization
Second personalization
First depersonalization
Second depersonalization
Extra space
Label Average
=averageGrade(C10:C18)
Copy the formula one cell to the right
Graphic file
Description of personalization