Starting from:
$23.99
Chp 2 - Professor's Grade Book Solution
Project Description:
You are a teaching assistant for Dr. Denise Gerber, who teaches an introductory C# programming class at your college. One of your routine tasks is to enter assignment and test grades into the grade book. Now that the semester is almost over, you need to create formulas to calculate category averages, the overall weighted average, and the letter grade for each student. In addition, Dr. Gerber wants to see general statistics, such as average, median, low, and high for each graded assignment and test, as well as category averages and total averages. Furthermore, you need to create the grading scale on the documentation worksheet and use it to display the appropriate letter grade for each student.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1
Start Excel. Download and open the file named exploring_e02_grader_h4.xlsx.
0
2
In the range A7:B9 in the Documentation sheet, enter the breakpoints for the C grades. In row 7, enter 70 as the breakpoint for C-; in row 8, enter 73 as the breakpoint for C; and in row 9, enter 77 as the breakpoint for C+.
3
3
Assign the name Grades to the range A3:B14 in the Documentation sheet.
Hint: Create a named range.
4
4
In cell R3 of the Grades sheet, calculate the total lab points earned for the first student in the Grades worksheet. Lab assignments are indicated by the column labels L1 through L8. Then copy the function to the range R4:R21.
Hint: - There is no such function in Excel called TOTAL(). Instead, use the function that can be used to add up values stored across a range of cells. - To copy the function, drag the fill handle in cell R3 to cell R21.
9
5
In cell U3 of the Grades sheet, calculate the average of the two midterm tests for the first student. Midterm tests are indicated by the column labels M1 and M2. Copy the function to the range U4:U21.
9
6
In cell I3 of the Grades sheet, calculate the assignment average for the first student. Assignments are indicated by the column labels A1 through A7. This formula should also accommodate the syllabus rule that in calculating the average grade, the lowest score will be dropped from the calculation. Then copy the formula to the range I4:I21.
Hint: Use a combination of SUM(), MIN(), and COUNT(). The argument for each function for the first student will be B3:H3. Find the total points (SUM()) and subtract the lowest score (MIN()) from the result. Then divide the remaining points by the number of assignments (COUNT()) minus 1.
10
7
In cell W3 of the Grades sheet, calculate the weighted total points based on the four category points (assignment average, lab points, midterm average, and final exam, in that order) and their respective weights (stored in the range B30:B33). Use relative and absolute cell references as needed in the formula. Copy the formula to the range W4:W21.
Hint: - The formula in cell W3, once perfected, will be replicated down to apply it to the other students. When replicating a formula, you should always consider whether the use of absolute cells referencing is warranted. - The formula in cell W3 refers to the weights for the assessment components, which are recorded in the range A30:B33. In this case, there is no need to use a Lookup table. - Do not use SUM()
6
8
In cell X3 of the Grades sheet, use a VLOOKUP function to calculate the letter grade equivalent. Use the range name from step 3 to reference the lookup table in the function. Copy the formula to the range X4:X21.
Hint: - When intending to replicate a formula, you should always consider whether the use of absolute cells referencing is warranted. - The range of cells making up the Table_array argument does not include column headings. - The fourth argument in VLOOKUP() is optional - i.e., if not specified, it defaults to TRUE. TRUE assumes the first column in the table is sorted either numerically or alphabetically (please confirm that this is the case), and will then search for the closest value. In contrast, FALSE searches for the exact value in the first column. As not all grade values are listed in the lookup table, clearly we will not wish to do an exact match. Therefore, the fourth argument must be TRUE. However, as this is the default value, we can leave out specifying the fourth argument.
10
9
Name the passing score threshold in cell B1 with the range name Passing.
4
10
In cell Y3, use an IF function to display a message based on the student’s semester performance. If a student earned a final score of 70 or higher, display the label Enroll in CS 202. Otherwise, display the label RETAKE CS 101. Use the range name in the function (Passing) to reference the cell containing 70. Copy the function to the range Y4:Y21.
Hint: - When dealing with text strings within functions, the Microsoft convention is to use "text" (double inverted commas) to wrap the text string - e.g., "GSU".
10
11
In cell B24, enter a function to calculate the average score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C24:W24.
6
12
In cell B25, enter a function to calculate the median score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C25:W25.
6
13
In cell B26, enter a function to display the low score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C26:W26.
6
14
In cell B27, enter a function to display the highest score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C27:W27.
6
15
In cell Z3, insert the RANK function to display the first student’s rank in the class based on the student’s total points compared to the range of all students’ total points. Copy the function to the range Z4:Z21.
Hint: In cell Z3, type =RANK(W3,$W$3:$W$21) and then press CTRL+ENTER.
6
16
Insert a list of range names starting in cell D3 in the Documentation worksheet.
Hint: On the Documentation sheet, click cell D3, then on the FORMULAS tab, in the Defined Names group, click Use in Formula. Click Paste Names, then click Paste List.
5
17
Ensure that the worksheets are placed in the following order in the workbook: Documentation, Grades. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0
You are a teaching assistant for Dr. Denise Gerber, who teaches an introductory C# programming class at your college. One of your routine tasks is to enter assignment and test grades into the grade book. Now that the semester is almost over, you need to create formulas to calculate category averages, the overall weighted average, and the letter grade for each student. In addition, Dr. Gerber wants to see general statistics, such as average, median, low, and high for each graded assignment and test, as well as category averages and total averages. Furthermore, you need to create the grading scale on the documentation worksheet and use it to display the appropriate letter grade for each student.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1
Start Excel. Download and open the file named exploring_e02_grader_h4.xlsx.
0
2
In the range A7:B9 in the Documentation sheet, enter the breakpoints for the C grades. In row 7, enter 70 as the breakpoint for C-; in row 8, enter 73 as the breakpoint for C; and in row 9, enter 77 as the breakpoint for C+.
3
3
Assign the name Grades to the range A3:B14 in the Documentation sheet.
Hint: Create a named range.
4
4
In cell R3 of the Grades sheet, calculate the total lab points earned for the first student in the Grades worksheet. Lab assignments are indicated by the column labels L1 through L8. Then copy the function to the range R4:R21.
Hint: - There is no such function in Excel called TOTAL(). Instead, use the function that can be used to add up values stored across a range of cells. - To copy the function, drag the fill handle in cell R3 to cell R21.
9
5
In cell U3 of the Grades sheet, calculate the average of the two midterm tests for the first student. Midterm tests are indicated by the column labels M1 and M2. Copy the function to the range U4:U21.
9
6
In cell I3 of the Grades sheet, calculate the assignment average for the first student. Assignments are indicated by the column labels A1 through A7. This formula should also accommodate the syllabus rule that in calculating the average grade, the lowest score will be dropped from the calculation. Then copy the formula to the range I4:I21.
Hint: Use a combination of SUM(), MIN(), and COUNT(). The argument for each function for the first student will be B3:H3. Find the total points (SUM()) and subtract the lowest score (MIN()) from the result. Then divide the remaining points by the number of assignments (COUNT()) minus 1.
10
7
In cell W3 of the Grades sheet, calculate the weighted total points based on the four category points (assignment average, lab points, midterm average, and final exam, in that order) and their respective weights (stored in the range B30:B33). Use relative and absolute cell references as needed in the formula. Copy the formula to the range W4:W21.
Hint: - The formula in cell W3, once perfected, will be replicated down to apply it to the other students. When replicating a formula, you should always consider whether the use of absolute cells referencing is warranted. - The formula in cell W3 refers to the weights for the assessment components, which are recorded in the range A30:B33. In this case, there is no need to use a Lookup table. - Do not use SUM()
6
8
In cell X3 of the Grades sheet, use a VLOOKUP function to calculate the letter grade equivalent. Use the range name from step 3 to reference the lookup table in the function. Copy the formula to the range X4:X21.
Hint: - When intending to replicate a formula, you should always consider whether the use of absolute cells referencing is warranted. - The range of cells making up the Table_array argument does not include column headings. - The fourth argument in VLOOKUP() is optional - i.e., if not specified, it defaults to TRUE. TRUE assumes the first column in the table is sorted either numerically or alphabetically (please confirm that this is the case), and will then search for the closest value. In contrast, FALSE searches for the exact value in the first column. As not all grade values are listed in the lookup table, clearly we will not wish to do an exact match. Therefore, the fourth argument must be TRUE. However, as this is the default value, we can leave out specifying the fourth argument.
10
9
Name the passing score threshold in cell B1 with the range name Passing.
4
10
In cell Y3, use an IF function to display a message based on the student’s semester performance. If a student earned a final score of 70 or higher, display the label Enroll in CS 202. Otherwise, display the label RETAKE CS 101. Use the range name in the function (Passing) to reference the cell containing 70. Copy the function to the range Y4:Y21.
Hint: - When dealing with text strings within functions, the Microsoft convention is to use "text" (double inverted commas) to wrap the text string - e.g., "GSU".
10
11
In cell B24, enter a function to calculate the average score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C24:W24.
6
12
In cell B25, enter a function to calculate the median score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C25:W25.
6
13
In cell B26, enter a function to display the low score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C26:W26.
6
14
In cell B27, enter a function to display the highest score for the Assignment 1 values stored in the range B3:B21. Copy the function to the range C27:W27.
6
15
In cell Z3, insert the RANK function to display the first student’s rank in the class based on the student’s total points compared to the range of all students’ total points. Copy the function to the range Z4:Z21.
Hint: In cell Z3, type =RANK(W3,$W$3:$W$21) and then press CTRL+ENTER.
6
16
Insert a list of range names starting in cell D3 in the Documentation worksheet.
Hint: On the Documentation sheet, click cell D3, then on the FORMULAS tab, in the Defined Names group, click Use in Formula. Click Paste Names, then click Paste List.
5
17
Ensure that the worksheets are placed in the following order in the workbook: Documentation, Grades. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0
1 file (278.4KB)
