Grade Sheet
Weight
Maximum score
Student ID
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
Average
Maximum
Minimum
Exam 1
35%
100
Exam 2
65%
100
Exam 1
86
82
89
83
77
51
98
81
87
84
74
87
83
77
68
81
83
85
65
75
78
67
91
74
71
81
78
75
59
95
90
Exam 2
85
75
90
84
76
50
89
89
92
80
78
80
91
86
76
90
85
86
69
73
69
76
99
73
65
81
76
69
66
100
93
Weighted Total Final Grade
Bin
Frequency
A
B
C
D
Total
MSIS110 Excel Assignment #1
Learning Objectives:
1. Review what we have learned in Excel Lab 01 ~ 03.
2. Practice the use of formulas and functions in Excel (especially aggregate functions and IF functions).
Work Load
This is an individual assignment. You will probably spend 2 to 3 hours depending on your proficiency and experience
with Excel. If you are stuck, you can seek help from your peer students or the instructor. However, please make sure to
ask specific questions rather than just for general guidance on how to work on the assignment.
What and How to Submit?
Submit your entire Excel file as the answer through Blackboard before the due time. To submit through
Blackboard, you need to log in to our course site, click “Weekly Sessions” and then “Week 04 Zara: IT &
Strategy”, and then click the title link of “Excel Assignment #1”. After that, you should be directed to a
submission page through which you can upload your Excel file.
Late submissions is accepted and graded within 24 hours after the deadline. You may still submit your work after
24 hours past the deadline. However, it won’t be accepted, nor graded.
Assignment Instruction
Please download the spreadsheet “Excel Assignment 1.xlsx” from Blackboard. It is an Excel workbook that a professor
uses to keep track of students’ grades. There are two exams – Exams 1 and Exam 2. There are two more columns –
Total Weighted Score and Final Grade. Exam 1 is weighted 35% of the total, and Exam 2 is weighted 65% of the total.
These weights are in cells B4 & C4.
Please do the following:
(0. 1 point) Put your full name (as FirstName LastName) in Cell D1. Make the font bold, italic, and in dark blue.
(a. 2 points) Find the total weighted score for the semester based on Exam 1 and Exam 2 and their respective weights.
(i.e., Exam 1 * Weight 1 + Exam 2 * Weight 2) This formula belongs in the Weighted Total column. (Hint: you might want
to use absolute cell references when referring to these cells.)
(b. 1 point each, 3 points in total) Find the average (AVERAGE), maximum (MAX), and minimum (MIN) for Exam 1, Exam
2 and Total Weighted Score. Put these formulas in cells B40:D42, as appropriate.
(c. 2 points) Assign a grade for each student based on the following rule: If Total >=90, then the grade is an A; between
80 and 89.99 it is a B; between 70 and 79.99 it is a C, otherwise D. Please use nested IF functions (i.e., IF functions
within other IF functions) to calculate the grade. Place this formula in the Final Grade column for all students.
[Hint] Consider the following hypothetical example. If you have a credit score of above 700, you are “E” (i.e., excellent
credit history); between 600 and 699, you are a “G” (i.e., good), otherwise “R” (i.e., Risky). In this situation, the formula
would be:
A
Credit Score
789
B
Rating
=IF(A1>=700, “E”, IF(A1>=600, “G”, “R”))
Note: You have 4 possible grades to assign, so you need to extend this if statement to consider four options.
Note again: If you cannot figure out how nested IF functions work, an alternative solution is to insert four new columns
labeled A, B, C, and D and then just put “Yes” or “No” in the corresponding cells in each of these four columns for each
Wei Zhang, @Spring 2020. Modified from Kui Du, Fall 2015
1
student, to indicate the letter grade this student receives (You can delete the Final Grade column in this case). You can
try to use this solution (but you will then think about how to complete step d below.)
(d. 2 points) Calculate the frequency of each letter grade using the COUNTIF function. [Hint: We do not cover COUNTIF
function in our lab exercise. Please learn it by yourself to understand how this function works. You can use google to find
any tutorials about COUNTIF. For example, you can watch this one: https://www.youtube.com/watch?v=CLEYppjUWFI]
Place the formulas in cells H14:H17. Calculate the total number of grades by inserting a sum formula in cell J19.
(e) SAVE your Excel workbook and RENAME it by using the following naming convention – LastnameFirstname‐
Excel1.xlsx – this will help us with the sorting and grading. Using this convention, John Doe’s homework would be
DoeJohn‐Excel1.xlsx.
(f) Submit your saved workbook through Blackboard.
Wei Zhang, @Spring 2020. Modified from Kui Du, Fall 2015
2
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.