You will be adding four new columns to compute: $1)$ an average assignment grade $2)$ an adjusted final exam grade, $3)$ a weighted average course grade and $4)$ a letter grade. For all four computations, you should enter a formula just once for the first student and then copy the formula to remaining students. This will require that you use relative and absolute cell addressing correctly.

$2)$ Create a new column with the average assignment grade. It should be computed as the average of the highest three assignment grades $($e$.$g$.$ the lowest score is dropped$).$ There is more than one way to accomplish this and you may find it helpful to do a little online research to figure this out. Here$$s a way to proceed: Begin by calculating an average of the four assignments without using the AVERAGE function and then modify this formula by including a minimum function to subtract the lowest score. Manually verify your computed result.

$3)$ Create a new column with an adjusted final exam score. Five points should be added to a student$$s final exam score if s$/$he has not missed more than $1$ class. You will need to use an IF function. Make sure you also isolate the values for bonus points and maximum number of absences$.$

$4)$ Isolate the following course component grade weights:

$$ project $15\%$

$$ four assignments $40\%$

$$ mid$-$term exam $20\%$

$$ final exam $25\%$

$$

I have found that errors are minimized by creating a new row above student records with weights and corresponding grades aligned by column like this:

The $100\%$ value should be computed as a check to ensure that weights sum to $100\%$

$5)$ Create a column with the weighted average course grades using the weights given above. Given n graded components, a weighted average is computed as:

grade component $1*$ weight component $1+$

grade component $2*$ weight component $2+$

$...$

grade component n $*$ weight component n $+$

Weights must sum to $1.$ When computing the weighted average, use the adjusted final exam grade and the average of best three assignments. Make sure that you refer to your isolated grade weights.

$6)$ Copy and paste the following letter grade table into your spreadsheet. Create a column with the letter grade.

Numeric to Letter Grade Table

$059\mathrm{.}9$ F

$60\mathrm{.}062\mathrm{.}9$ D$-$

$63\mathrm{.}066\mathrm{.}9$ D

$67\mathrm{.}069\mathrm{.}9$ D$+$

$70\mathrm{.}072\mathrm{.}9$ C$-$

$73\mathrm{.}076\mathrm{.}9$ C

$77\mathrm{.}079\mathrm{.}9$ C$+$

$80\mathrm{.}082\mathrm{.}9$ B$-$

$83\mathrm{.}086\mathrm{.}9$ B

$87\mathrm{.}089\mathrm{.}9$ B$+$

$90\mathrm{.}094\mathrm{.}9$ A$-$

$95\mathrm{.}0100\mathrm{.}0$ A

$7)$ You will need to use the weighted average grade to lookup the corresponding letter grade in the above table. Use the VLOOKUP function.

$8)$ For each of the four course components $($midterm$,$ project, average of best $3$ assignments and adjusted final$)$ and the overall course weighted average, compute the following statistics: average, minimum, median, maximum and population standard deviation $($since data is for whole class population$).$ For example, you might display something like:

Average Minimum Median Maximum Std Deviation

Project $84\mathrm{.}0049\mathrm{.}0085\mathrm{.}00100\mathrm{.}0012\mathrm{.}00$

Assignments $92\mathrm{.}0025\mathrm{.}0096\mathrm{.}0097\mathrm{.}0016\mathrm{.}00$

Mid$-$term $78\mathrm{.}000\mathrm{.}0079\mathrm{.}00100\mathrm{.}0021\mathrm{.}00$

Final $86\mathrm{.}0077\mathrm{.}0085\mathrm{.}0098\mathrm{.}006\mathrm{.}00$

Overall Course $83\mathrm{.}0058\mathrm{.}0088\mathrm{.}0099\mathrm{.}008\mathrm{.}00$

Note $$ your numbers will be different.

$9)$ Make some changes to the course component grade weights $($keeping in mind they should add to $100\%)$ and observe the effect on overall course grades. Submit with changed weights so I can see that you did a $$what$-$if$$ analysis.

$8)$ Format the spreadsheet so that is easy to read $($e$.$g$.$ use common formats, highlight column headers, etc..$)$

$10)$ Submit your assignment via Blackboard

$11)$

Assn$1$ Assn$2$ Assn$3$ Assn$4$ Mid Term Exam Final Exam Project Absences

Adams, John $727082767268801$

Bradley, Mark $9598981009585983$

Brown, Stephanie $968298658686760$

Donald, Colin $85800957683651$

Draper, Thomas $8410091987990893$

Fater, William $929598876580970$

Falk, Michelle $91981008792891000$

Garrity, Karen $82747882079702$

Gonzales, Manny $828472889175821$

Harrison, Andrew $989594959588851$

Hennessey, David $827478807580724$

Hewitt, Cameron $959594977286920$

Hopkins, Timothy $99100958593100870$

Jetson, Barbara $809478866084833$

Johnson, Benjamin $927810087100861001$

Joyce, Debbie $707270957589820$

Kooper, Robert $1009880755787743$

Larson, Jeremy $989496988693940$

Morse, Kimberly $779496928790930$

Norton, Suzanne $918296808178704$

Ordinola, Maria $807472806065724$

Rodgers, Tray $707565608275650$

Shlesinger, Marcus $8994911007486842$

Shawmut, Stephen $809582968386881$

Sylvia, Rodney $788282857882920$

Tam, Steven $979478727680650$

Tao, Heather $889290998588855$

Wesley, Matthew $788694957888872$