Excel Assignment 1
-------------------------------------------------
Excel Assignment #1
Exercise 1: Student Grade Book (expected time – less than 45 minutes)
Please download the spreadsheet Excel Assignment 1.xlsx from Blackboard. Click on worksheet E1 (you may be in Worksheet E1 by default). This spreadsheet keeps track of students’ grades. There are two exams – Exams 1 and Exam 2.
Please do the following:
(a) 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) Exam 1 is weighted 45% of the total, and Exam 2 is weighted 55% of the total. These weights are in cells B4 & C4. (Hint: you might want to use absolute cell references when referring to these cells.) This formula belongs in the Weighted Total column. You MUST use absolute cell references to get credit for this question.
(b) Find the average (AVERAGE), standard deviation (STDEV), maximum (MAX), and minimum (MIN) for Exam 1, Exam 2 and Total Weighted Score. (Click on the “Formulas” tab and then select “Insert Function.” Select “Statistical” from the category drop down box.) Put these formulas in cells B40:D43, as appropriate. (d) You have two more columns called Consistent A and Consistent C or D. Display “Yes” in the Consistent A if the student scored 90 or above on both exams. Otherwise, display “No.” Similarly, display “Yes” in the Consistent C or D column if the student received a score less than 80 on both exams. Otherwise, display “No.” (Hint: You will need to use the COUNTIF function within an IF function for these columns.)
Consider the following hypothetical example. If both a husband’s and wife’s credit scores are above 650, they will be approved for a home loan. So the mortgage company might create a function that looks like this:
A B
Credit Score Rating
650 =IF (Count if (B5:C5,”>650”) =2, “Approve”,”Reject”)
(e) Sum the number of students with “Yes” in both the Consistent A and Consistent C
Excel Assignment #1
Exercise 1: Student Grade Book (expected time – less than 45 minutes)
Please download the spreadsheet Excel Assignment 1.xlsx from Blackboard. Click on worksheet E1 (you may be in Worksheet E1 by default). This spreadsheet keeps track of students’ grades. There are two exams – Exams 1 and Exam 2.
Please do the following:
(a) 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) Exam 1 is weighted 45% of the total, and Exam 2 is weighted 55% of the total. These weights are in cells B4 & C4. (Hint: you might want to use absolute cell references when referring to these cells.) This formula belongs in the Weighted Total column. You MUST use absolute cell references to get credit for this question.
(b) Find the average (AVERAGE), standard deviation (STDEV), maximum (MAX), and minimum (MIN) for Exam 1, Exam 2 and Total Weighted Score. (Click on the “Formulas” tab and then select “Insert Function.” Select “Statistical” from the category drop down box.) Put these formulas in cells B40:D43, as appropriate. (d) You have two more columns called Consistent A and Consistent C or D. Display “Yes” in the Consistent A if the student scored 90 or above on both exams. Otherwise, display “No.” Similarly, display “Yes” in the Consistent C or D column if the student received a score less than 80 on both exams. Otherwise, display “No.” (Hint: You will need to use the COUNTIF function within an IF function for these columns.)
Consider the following hypothetical example. If both a husband’s and wife’s credit scores are above 650, they will be approved for a home loan. So the mortgage company might create a function that looks like this:
A B
Credit Score Rating
650 =IF (Count if (B5:C5,”>650”) =2, “Approve”,”Reject”)
(e) Sum the number of students with “Yes” in both the Consistent A and Consistent C