Grade Calculator Assignment(Excel)–ISM3011Spring2023 Grading philosophy: There
Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates
For This or a Similar Paper Click To Order Now
Grade Calculator Assignment(Excel)–ISM3011Spring2023
Grading philosophy: There are quite a few details required in an effort to 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).
Requirements & Notes:
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 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 yellow 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. At the end of this document is an example with some values changed. In the
yellow cells, type in the maximum points possible for each assignment. In Cells P6:P11, I typed in the weight of each
assignment group, taken from the syllabus. The orange 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.
Calculations
9. Put in the maximum points possible for points earned in all green cells
10. For Projects and Starting/Ending, use the SUM function to add total points earned and total points possible. Then write a
division formula to find the % earned.
11. For the projects extra credit % earned, 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. Don’t use the default unequal shape color.
12. For check-ins, use the combined SUM & MIN functions to drop the lowest check-in grade. Then write the division formula for
% earned.
13. Connect topics should use an average formula and the cases studies section will just have a percentage entered from your
Canvas grades (use 100% for right now)
14. Your percentages should be formatted so that they display a % sign and display 1 decimal place.
15. In the Final Grade group, use simple reference formulas to display the totals already calculated in each activity group (don’t
recalculate the values).
16. Since the final grade is a weighted grade, write the formulas to calculate the Weighted %s for each activity group (cells
Q5:Q11).
17. Find the sum of these Weighted %s in cell Q12 (Total Weighted Grade). Then modify this formula (Q13) 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).
18. 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.
19. 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 ‘Letter Grades’)
• Then, name this group of cells ‘GradeScale’ (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.
20. Lastly, include a few additional skills:
• Freeze panes so that the first 5 rows of the calculator 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 that 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 the extra credit points earned 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.
21. Test again with different scores – see the overview on this and be sure that your results match those in the overview.
22. **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
formula (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 maximum points 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. Your results should match these. Also see the overview
video.
