Assignment
Learning Objectives:
• To understand relative reference, absolute reference, and mixed reference
• To use formulas to calculate sum, average, median, min, and max.
• To use functions, such as SUM(), AVE(), MAX(), MIN(), and YEAR().
• To use logical, lookup, and financial functions.
• To use range names.
Part I: multiple choices. Please select the best answer:
1. If cell D15 contains the formula = $C$5 * D$15, what is the D15 in the formula:
(a) Relative reference
(b) Absolute reference
(c) Circular reference
(d) Range name
2. What function would most appropriately accomplish the same thing as = (B5+C5+D5+E5+F5)/5
(a) =SUM(B5:F5)
(b) =AVERAGE(B5:F5)
(c) =MEDIAN(B5:F5)
(d) =COUNT(B5:F5)
3. When you start =AV, what display a list of functions and defined names:
(a) Function ScreenTip
(b) Formula AutoComplete
(c) Insert Function dialog box
(d) Function Arguments dialog box
4. A formula containing the entry =$B3 is copied to a cell one column to the right and two rows down. How will the entry appear in its new location?
(a) =$B3
(b) =B3
(c) =$C5
(d) =$B5
5. Cell B10 contains a date, such as 1/1/2016. Which formula will determine how many days are between that date and the current date, given that the cell containing the formula is formatted with Number Format:
(a) =TODAY( )
(b) =CURRENT( )-B10
(c) =TODAY( )-B10
(d) =TODAY( )+NOW( ).
6. Given that cells A1, A2, and A3 contain values 2, 3, and 10, respectively, and B6, C6, and D6 contain values 10, 20, and 30, respectively, what value will be returned by the function =IF(B6>A3, C6*A1, D6*A2):
(a) 10
(b) 40
(c) 60
(d) 90
7. Given the function =VLOOPUP(C6,$D$12:$F$18,3) the entries in:
(a) Range D12:D18 are in ascending order.
(b) Range D12:D18 are in descending order.
(c) The third column of the lookup table must be text only.
(d) Range D12:D18 contain multiple values in each cell.
8. The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5?
(a) APR
(b) Periodic interest rate
(c) Loan amount
(d) Number of payment periods
9. Which of the following is not an appropriate use of the SUM function:
(a) =SUM(B3:B45)
(b) D=SUM(F1:G10)
(c) =SUM(A8:A15, D8:D15)
(d) =SUM(D15-C15)
10. Which of the following is not an acceptable range name:
(a) FICA
(b) Test_Weight
(c) Goal for 2016
(d) Target_2015
Part II: project
Please follow the instructions below to make a Microsoft Excel workbook:
1. Please download e02m3Grades.xlsx from Blackboard and save it on your computer
2. Open e02m3Grades.xlsx and save it as e02m3Grades_Lastname_Firstname.xlsx
3. Use breakpoints to enter the grading scale in the correct structure on the Documentation worksheet and name the grading scale range Grades. The grading scale is as follows:
95+ A
90-94.9 A-
87-98.9 B+
83-86.9 B
80-82.9 B-
77-79.9 C+
73-76.9 C
70-72.9 C-
67-69.9 D+
63-66.9 D
60-62.9 D-
0-59.9 F
4. Calculate the total lab points earned for the first student in cell T8 in the Grades worksheet. The first student earned 93 lab points
5. Calculate the average of the two midterm tests for the first student in cell W8. The student's midterm test average is 87
6. Calculate the assignment average for the first student in cell I8. The formula should drop the lowest score before calculating the average.
Hint: you need to use a combination of three functions: SUM, MIN, and COUNT. The argument for each function for the first student is B8:H8. Find the total points and subtract the lowest score. Then divide the remaining points by the number of assignments minus 1. The first student's assignment average is 94.2 after dropping the lowest assignment score
7. Calculate the weighted total points based on the four category points (assignment average, lab points, midterm average, and final exam) and their respective weights (stored in the range B40:B43) in cell Y8. Use relative and absolute cell references as needed in the formula. The first student's total weighted score is 90.
8. Use a VLOOKUP function to calculate the letter grade equivalent in cell Z8. Use the range name in the function. The first student's letter grade is A-
9. Copy the formulas down their respective columns for the other students.
10. Name the passing score threshold in cell B5 with the range name Passing. Use an IF function to display a message in the last grade book column based on the student's semester performance. If a student earned a final score of 70 or higher, display Enroll in CS 202. Otherwise, display RETAKE CS 101. Remember to use quotation marks around the text arguments.
11. Calculate the average, median, low, and high scores for each assignment, lab, test, category average, and total score. Display individual averages with no decimal places; display category and final score averages with one decimal place. Display other statistics with no decimal places.
12. Please create a new worksheet named MultipleChoice and write the 10 multiple questions' answers on Column A. For example, A1 should be the answer to question 1, A2 should be the answer to question 2, A3 should be the answer to question 3, . . . , A10 should be the answer to question 10.
Attachment:- Grades.xlsx