Grade Calculator Assignment

Grade Calculator Assignment (Excel) – ISM3011 Fall 2022

Take advantage of contact hours to clarify any questions you have. Refer to the syllabus on Academic Dishonesty and group/individual
work and allowable help for all projects – also remember it’s your responsibility to protect your work; do not share your project in any
way, remove your work to a flash/thumb drive from any shared computers, check/delete work & empty the recycle bin if you work on
public/lab computers..

Grading philosophy: There are quite a few details required in an effort illustrate some new Excel techniques and we look for these
details when grading.

Before you start — Read this whole assignment and use an optional text and/or review the tutorials on the Overview page as necessary.
For each project in the class, I post a project overview video in the Modules area of Canvas to get you started. It’s important to view the
overview so you can see exactly how the project works. Below the overview is a series of short videos demonstrating some of the skills
required for this project.

Project Sample: Create a spreadsheet to keep track of your grades in this class. Your final project should include the information shown
here and be formatted as displayed (feel free to use your own colors).

1. Start with a blank workbook and name it using your Last name followed by your initials and _ GradeCalc.xlsx. For Example:
mine would be called WarnerBL_GradeCalc.xlsx
2. Your first worksheet (shown here) should be named ‘Calculator’ – name it by changing the worksheet tab.
3. Create a title for your worksheet in the second row that and use the Merge and Center feature to make the title include
multiple columns but act as one wide cell.
4. Use the Excel note feature to add a note to the merged cell that includes your name, date of creation and your email address.
Add a date formula in the 2nd row of the title to display the current date when opened (when you open it tomorrow, the date
should automatically change to the current day).
5. Organize your worksheet as above including a blank row at the top and a blank column to the left, so the borders are visible on
all sides. Do not use the Excel Table feature, instead type in the labels, formulas, etc. and use your own colors (don’t use black
or dark blue). Include: 1) Color fonts,
2) Fancy (dotted, etc.) color borders on at least the title, 3) Color background fill for the labels and title. You can use your own
colors (my colors are selected to show different types of cells)
6. In my example above, the grey cells are labels that are just typed in. The green are the grades earned (typed in). You should
start by entering full points for each assignment – columns C, I and E21. At the end of this document is an example with some
values changed. In the orange cells, type in the maximum points possible for each assignment. In Cells P6:P10, I typed in the
weight of each assignment group, taken from the syllabus. The blue cells are formulas that you’ll create and enter.
7. Create vertically merged cell for the Final Grade title.
8. Match the alignment shown in the sample / overview. Note that some are left aligned, some are right aligned and indented
(use the indent feature) and some are center aligned.
9. Correctly use the SUM function for the totals in each activity group (columns C & D and H & i, and P11 & Q11). In E26, use the
average formula. Note that there is a drop grade in the Check-Ins, so the lowest grade should automatically be dropped from
points earned and points possible (include the MIN function to help with this).
10. Write your own formulas to calculate the % grade for each activity as well as the total % grade for each activity group. Your
percentages should be formatted so that they display a % sign and display 1 decimal place. For the Connect Assignments, you’ll
have to just enter one number from the Canvas or Connect gradebook. In this example, just add 100% (let’s be optimistic!)
11. Add a colored unequal sign (use Excel’s shape feature) as shown in the sample above to show that the % shouldn’t be
displayed for this assignment – as the formula would cause an error. Don’t use the default unequal shape color.

12. In the Final Grade group, use simple reference formulas to display the totals already calculated in each activity group (don’t
recalculate or use the sum function again).
13. Since the final grade is a weighted grade, write the formulas to calculate the Weighted %s for each activity group (cells
Q5:Q10).
14. Find the sum of these Weighted %s in cell Q11 (Total Weighted Grade). Then modify this formula (Q12) by adding an IF
function to the formula so that if the grade earned is greater than 100%, only 100% is displayed. Test this by changing all the
activity grades to 100% (if they are not already set to this).
15. As you change grades in your worksheet, your totals, percentages and final grades should automatically adjust. See the project
overview video to compare your results.
16. Use the VLOOKUP formula to display the letter grade based on the Total Weighted Grade. To
do this, you’ll have to
• first create this group of cells (see right) on a second worksheet (name the worksheet tab
‘Scale’)
• Then, name this group of cells ‘GradingScale’ (no space)– use the Named Range feature of
Excel.
• Then create your VLOOKUP formula referring to the cell with the Total Weighted Grade and
your named range. There is a video tip on this in our Overview page.

17. Lastly, include a few additional skills:
• Freeze panes so that the first 5 rows of the calculator so they are always visible.
• Data Validation to ensure all points earned values are within a reasonable range. For
example, if an assignment is worth 50 points, be sure the only points that can be entered
are within that range (0 to 50 and select ‘decimals’ so partial points can be added). Also
include:
o a custom input message (message you write) and
o a custom error alert (messages you write).
The messages should be specific to the type of assignment / points represented by the cell and should make sense, be
business appropriate. Test this by trying to enter grades outside the acceptable range.
Data validation for cell C16 should just ensure that the value entered is greater than or equal to zero and not exceed 15.
• Add a chart showing the grades earned for each activity as well as the grade weight (columns O and P). Format the
columns with color, display labels above the columns and be sure the legend is correct.
• See the overview on how to create the chart title so it changes when the grades are changed.
• Once your project is working, protect your Calculator worksheet so that the labels and formulas can’t be modified. The
only cells that should be updateable are the cells where you enter grades earned (green cells). Do not use a password on
this.

18. Test again with different scores – see the overview on this and be sure that your results match those in the overview.
19. **Before you submit, enter perfect / maximum scores in for all assignments.

Finishing Up
1. Check your formulas, be sure they are correct and make sense. For example, if you are subtracting 2 numbers don’t use the SUM
formulas (sum is for adding). Excel may figure out what you mean, but we want the formulas to be used correctly and show that
you understand how to use them.
2. Again, put in perfect grades for ‘points earned’ and save your work before submitting to Canvas.

Project Submission Instructions / Notes:
• Contact hours get busy as deadlines approach. If you procrastinate and wait until the last days to work on your project, you may
not be able to get all the help you want.
• The only way we can fairly grade the projects is if we check for each requirement. Please go through the instructions before you
submit & be sure you have done each one correctly, so you don’t miss points. Compare your solution to the project overview.
• Submitting:
o Remember to leave all the internal file properties intact for your project, if they are modified or deleted, you project won’t
be accepted (see syllabus for more on this).
o Read and follow the instructions in the Assignments section of Canvas on uploading and checking your upload. If you
follow these instructions, you can ensure that your project is uploaded correctly (and is the correct project). Be sure that
Access / Excel are closed before you try to upload your project files.

o If your project doesn’t upload correctly before the due date, it will be considered late and be assessed the late penalty –
even it was finished on time. This is the only way we can ensure that students check their Canvas submissions.
• Technology problems relating to your home computer (Windows based or Mac), internet connection or slow Canvas access are not
valid excuses for late/missing work, unless Canvas is down for 6+ hours on the due date. Computers at USF computer labs and the
library are available; leave enough time to access them as needed. Also give yourself enough time that if a TA can’t answer a
question, you’ll have time to contact me & I can either help you or make an allowance in your grade. If you wait until the last days,
I may not be able to do either.

Results with some values ‘Points Earned’ changed so you can check your results. Also see the overview video.

This criterion is linked to a Learning OutcomeSubmitted work matches assignment for F22 or can’t be opened / graded. Grader: give a zero and add a comment in comment field below.
0 pts
Full Marks
0 pts
No Marks
0 pts
This criterion is linked to a Learning OutcomeNaming Conventions: 1) workbook named correctly – LastF_GradeCalc.xlsx, 2) Worksheet 1 named Calculator, 3) Worksheet 2 named Scale, 4) Named range used (GradingScale) 5) Named range cells are correct
5 pts
Full Marks
4 pts
1 part missing / incorrect
3 pts
2 parts missing / incorrect
2 pts
3 parts missing / incorrect
1 pts
4 parts missing / incorrect
0 pts
No Marks
5 pts
This criterion is linked to a Learning OutcomeWorksheet protected correctly – 1) only points earned can be modified, 2) no password used
4 pts
Full Marks
2 pts
1 part missing / incorrect
0 pts
No Marks
4 pts
This criterion is linked to a Learning OutcomeData validation – 3 parts: 1) range correct on on all points earned cells, 2) input message correct, 3) error message correct (Default messages not used).
6 pts
Full Marks
4 pts
1 part missing / incorrect
2 pts
2 parts missing / incorrect
0 pts
No Marks
6 pts
This criterion is linked to a Learning OutcomeFormatting: 1) Top 5 rows frozen, 2) Title Merged/Centered, 3) Note / comment used – name/date/email address, 4) Color fonts and fancy borders, 5) Vertical heading for final grades, 6) Date FORMULA (Today or Now) included in 2nd row of title
6 pts
Full Marks
5 pts
1 part missing / incorrect
4 pts
2 parts missing / incorrect
3 pts
3 parts missing / incorrect
2 pts
4 parts missing / incorrect
1 pts
5 parts missing or incorrect
0 pts
No Marks
6 pts
This criterion is linked to a Learning OutcomeCell Formatting: 1) indentation alignment used for activities, 2) Other alignment matches (center, left)
2 pts
Full Marks
1 pts
1 part missing / incorrect
0 pts
No Marks
2 pts
This criterion is linked to a Learning OutcomeFormulas / Numbers: 1) % Earned formula correct, 2) SUM formula used for totals 3) formula for total % earned correct and 4) dropped grade calculations correct and USING MIN. ** Grader: formulas should automatically update if earned points changed. Note: changing the first grade in each group to 1 and first 2 grades in check-ins to 1 = 87.6% final average. Does not include midterm/final quiz grades.
8 pts
Full Marks
6 pts
3 parts missing / incorrect
4 pts
4 parts missing / incorrect
2 pts
5 parts missing / incorrect
0 pts
No Marks
8 pts
This criterion is linked to a Learning OutcomeFinal Grades: 1) % Earned formulas = cell references (not sum or division formulas), 2) Weighted % formula correct, 3) Total Weighted Grade WITH IF function correct and uses SUM (not O6+O7+…) , 4) VLOOKUP formula correct and uses Named Range **Grader: formulas should automatically update if earned points changed.
8 pts
Full Marks
6 pts
1 part missing / incorrect
4 pts
2 parts missing / incorrect
2 pts
3 parts missing / incorrect
0 pts
No Marks
8 pts
This criterion is linked to a Learning OutcomeChart & Formats: 1) Chart works correctly (grades earned & grade weight) and includes 2) data labels, 3) correct legend, 4) Title changes as final grade changes 5) Textbox included to left of title
5 pts
Full Marks
4 pts
1 part missing / incorrect
3 pts
2 parts missing / incorrect
2 pts
3 parts missing / incorrect
1 pts
4 parts missing / incorrect
0 pts
No Marks
5 pts
This criterion is linked to a Learning Outcome1) Earned Grades entered as all 100%, 2) X Shape added to Extra Credit % Earned – color changed, 3) Calculated %s all have % sign and 1 decimal place
6 pts
Full Marks
4 pts
1 part missing / incorrect
2 pts
2 parts missing / incorrect
0 pts
No Marks
6 pts
Total Points: 50

Last Completed Projects