Excel Mastery Assignment

Directions

In this assignment, you will use excel function and formula concepts to create a gradebook dashboard that updates as new grades are entered. Follow the directions and use the check your work section to confirm your results are correct. REMINDERS: Absolute and relative cell references should be used where appropriate. You should only be creating formulas once and then filling the appropriate rows and columns. There will be deductions for manual caclulations and for incorrect formula usage. Use of CSE/Array functions are not permitted. Sum, If, vlookup, rank, count, countif, countblank, average, max, min, median and isblank commands should be used where appropriate. In other words, no manual calculations. Conditional formatting should be used where appropriate. Directions: In column B, labeled ID#, assign each student a unique ID#. These numbers should start at 123 and run consecutively through 150. You should not type each number individually (use fill). If any student is missing an assignment, the cell should be red. This should update automatically (don’t manually shade it red). Calculate the current grade as a percentage. The Current grade column should only look at grades that have been submitted to the gradebook. (ignore empty cells) Note: All assignments are not equally weighted. Calculate the finale grade as a percentage. The Final grade column treats all grades as being submitted (0’s are computed for omitted work). Compute the students current letter grade. The Letter grade column is based off of the Current grade and should update automatically as assignments are graded. Report the count for how many students fall into each grade cut point. This should update automatically as grades are input. Calculate the class rank. Rank should be calculated such that each student has a unique rating based off of current grade and ties should be broken based on location. (Whatever is listed first is ranked first) Calculate Averages, ranges, and Medians for All assignments and percent grades. Check your work: Rose Sayer has a 64.8% for current and final grade and a D; her ID is 129 Melanie Daniels has a C in her current grade but if she doesn’t turn anything in, she’d have a 16.7% in her final grade Tommie DeVito has a 72.4 currently but if he doesn’t make up his exam, his final grade would be a 56.5 0 Students have an A in the course 1 Student is Failing… but just barely. The mode grade is a C+, 6 Students have a C+ The average counting blanks as 0 for exam 2 is 74.9. The average noting counting zeros is 80.6. The sum of all of the class ranks should equal 406 Darth Vader is second in the class. Ace Ventura is 22nd.

Grade Sheet