Grades Analysis

In this project you will compute final grades and statistics and analyze the data.

$\backslash$table$[[,],[,\backslash$table$[[$Open the start file, Capstone Project $-$ Grades Analysis, and resave the file as: $[$your initials$]$ Capstone$],[$Project $-$ Grades Analysis$],[$NOTE: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the$],[$top of the workbook so you can modify the workbook.$]]],[,\backslash$table$[[$In the Gradebook Science worksheet, make the following calculations: $1)$ use COUNTIF to compute the$],[$number of absences for each student using the criteria Absent; $2)$ compute the average for each$],[$student; $3)$ compute the rounded average for each student, rounding the value in Average to zero$],[$decimal places; and $4)$ use an IFS statement to determine the final grade for each student. HINT: set up$],[$the formula as follows and then add the arguments for grades $"$ C $","$D$",$ and $"$E$".],[=$ IFS $($O$2>89,"$A$",$ O$2>79,$B $",]]],[,\backslash$table$[[$In the Gradebook Science worksheet, determine the median score, the average score, the standard$],[$deviation$,$ and the two most common scores $($mode$)$ for each assignment. Use the IFERROR function to$],[$display the message $"$no mode" instead of an error of no mode value is found. Determine the median$],[$and average scores, standard deviation, and the mode for both the Raw Average and Rounded$],[$Average columns.$]]],[,$In the Gradebook Math worksheet, sort the data alphabetically by section and then by last name.$],[,\backslash$table$[[$In the Gradebook Math worksheet, use Advanced Filter to find students who had less than $80$ on the$],[$midterm or had less than $80$ on the final. Copy cells A$3$:P$3$ and paste them starting in cell A$29$ for use as$],[$the header row in the criteria range. Enter the criteria range to find rows where the value in the Midterm$],[$column is $\backslash$epsi $80$ or the value in the Final column is $80.$ Use Advanced Filter and copy the filtered data to$],[$the range beginning with cell A$34.]]],[,\backslash$table$[[$In the Summary by Section worksheet, use Consolidate to create a summary of the grades by section.$],[$Place the summary in cell A$2$ of the Summary by Section worksheet. Use the Average function.$],[$Consolidate data from the cell range C$3$:O$26$ in the Gradebook Math worksheet. Use labels from both the$],[$top row and the left column. Do not create links to the source data. Autofit the columns, if necessary. On$],[$the Summary by Section worksheet, center and merge the title across cells A$1$:M$1,]]],[,\backslash$table$[[$In the Suspect Grades worksheet, apply conditional formatting to find suspicious grades $($duplicate values$],[$for each assignment$),$ Red, Accent $2$ and Bold formatting. You will need to create $10$ separate conditional$],[$formatting rules $-$ one for each chapter assignment. Filter each of the columns to show only the cells with$],[$the red font color.$]]],[,\backslash$table$[[$Make the following updates in the PivotIable worksheet: $1)$ Add a calculated field named Difference to$],[$the PivotTable to calculate the difference between the final average and the midterm average for each$],[$section; $2)$ Apply the Pivot Style Medium $2$ Quick Style to the PivotTable; $3)$ Change the PivotChart to a$],[$combo chart with the Sum of Difference series as a line chart on the secondary axis and the other two$],[$series as clustered column charts on the primary axis.; $4)$ Apply the Style $5$ PivotChart Quick Style to the$],[$PivotChart; $5)$ Add a slicer to the PivotChart to filter the data by the Final field. If necessary, move the$],[$slicer so it doesn't cover the PivotChart. Display data where the final exam scores are above $70\%$ only.$]]],[,$ected$]]$