1. (28 points) In the worksheet named Students & FinalExam a. (10 points) (Worksheet Students) Enter random numbers using the RAND function for all homeworks and exams (#1 and #2) for all students using the following properties: i. (6 points) Each homework grade should be an even number between 50 and 100 ii. (4 points) Each Exam grade (#1 and #2) should be an integer between 40 and 100 b. (3 points) (Worksheet Students) In the AVG column (H), enter a formula which calculates the homework average while dropping the lowest homework grade c. (3 points) (Worksheet FinalExam) Enter random numbers using RANDBETWEEN function for final exams in the RAW column such that each grade is between 60 and 100 d. (6 points) (Worksheet FinalExam) For the curve2 column, enter a formula which subtracts the RAW average from 75 and rounds the result to the nearest integer in E1. Then use that it to calculate the result of using that value in E1 to curve the exam scores in the curve2 column for each student. e. (2 points) (Worksheet Students) Enter a formula which shows the result of curve2 on worksheet FinalExam in the final exam column (FE – M) f. (4 points) (Worksheet Students) Enter a formula to calculate the final grade for each student in the final grade column (FG – O) using the following percentages: Homework 35%, Final Exam 25%, Exams (Each) 20% and round it to the nearest integer.
2. (25 points) In the worksheet named Students2Statistics (& Students2) a. (4 points) In the worksheet named Students2 enter a formula in Rank Column which calculates the rank of each student based on their final grades & sort the worksheet by rank b. (5 points) In the Students2Statistics worksheet, enter formulas to get the names of the top five students based on their rank in the Students2 c. (4 points) In B8 and B9, calculate the number of students who have a C or higher in Students2 and calculate the number of students who have a C or lower in Students2 respectively d. (4 points) In B11, calculate the difficulty of the course using the following metric if the number of students with a C or higher is greater than the number of students with a C or lower, the class is easy. If the number of students with a C or higher is equal to the number of students with a C or lower, then the class is medium. If the number of students with a C or higher is lower than the number of students with a C or lower, the class is hard. e. (5 points) (new Worksheet) Create a new worksheet and create a table for calculating letter grades from their scores using the following grade scale and name the area lettergrades
Average Grade 93 – 100 A 90 – 92 A- 87 – 89 B+ 83 – 86 B 80 – 82 B- 77 – 79 C+ 73 – 76 C 70 – 72 C- 67 – 69 D+ 60 – 66 D < 60 F
f. (3 points) (Worksheet Students2) Enter a formula which calculates the letter grade for each student in the final letter grade column (FLG – P) using the named table you created earlier 3. (10 points) In the worksheet named Students3 a. (4 points) In the worksheet named Students3 enter a formula in the homework average column which drops the lowest 2 homeworks (HINT: use SMALL function twice) b. (6 points) In the worksheet named Students3, determine using Goal Seek how much extra credit (adding points to a single homework – cell is N1) you’d need to give to get the average homework score (in N2) up to 75. 4. (22 points) In the worksheet named Students4 a. (6 points) For condition 1 column, enter a formula for each student which determines if it is true each student has a passing grade using the following conditions: i. A homework average above 70 ii. An exam average above 70 iii. Final Project grade above 70 b. (4 points) In condition 2 column, enter a formula which determines whether or not it is true the student is better or equal at projects and homeworks (all averaged together) than the student is at exams (also all averaged together) c. (12 points) In the FG column, enter a formula which determines the final grade for each student based on the following criteria: i. (6 points) If the student is better (or equal) at projects than homeworks, then weigh the homework AVG 35%, the Project 35%, and the exams at 30%, otherwise, weight the exams at 50%, the homework avg 25% and the project 25% ii. (4 points) If the student has a higher final grade than the project, then the final grade is the project grade
5. In the worksheet named AdvisorSheet a. (15 points) Enter formulas in the rows such that if a user enters a valid student ID in A5, then the rows becomes populated with the correct data from the worksheet StudentsWID (the name and the GPA column) i. (5 points) Make sure to leave the cells blank if the SID is blank ii. (5 points) if an incorrect SID is entered, indicate that by showing Invalid SID in the cells
