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.