Create a table in Excel and solve for the following:

1. What percentage of students had an overall grade above 80%?
2. How many students had a grade of B+ or higher?

In the table in Excel, you must use the SumProduct formula initially to calculate each students grade, and then use VLookup to enter the letter grade.

Next create a pivot table showing only the major, status (freshman or sophomore) and letter grade. Try to create multiple pivot tables on different sheets in your Excel file. Play around with different items in columns versus rows.

You must submit an excel file not a pdf of the file - I need to be able to click on the cells and see the formula. The file will have two sheets, the first sheet is all the data you enter (and shows use of the SumProduct and VLookup formulas), and the second sheet is the pivot table (though you can submit multiple pivot tables).

Upload the file and the answers to the two questions above.

Use the grade chart and the student information below to create your Excel file. Be sure to compare the chart below to the one in my video example; the information being used is different and you need to use the data below.

HINTS:

• Include plus and minus grades.
• The order in which the grades are listed in your grade table (look at the video) is important.
• The grade table in my video does not show a range (e.g. 90-92%) but rather a cut-off score only, and this is very important when creating your grade table. You must use a cut off score also and use the information below to determine the cut off score for each letter grade.

The students and their grades are listed below. The quizzes are worth 10% each, and the paper and the finals are worth 40% each. You cannot simply average the grades because the different components (quizzes, final) have different values.

Score B+ > C+ Grade 93-100% 90-92% 87-89% 83-86% 80-82% 77-79% 73-76% 70-72% 67-69% 63-66% 60-62% Failure Contribution to GPA 4.00 3.70 3.30 3.00 2.70 2.30 2.00 1.70 1.30 1.00 0.70 0.00 D+ Quiz 2 Final Paper 90 87 Alice Ann Bill Major BAN CRJ NUR 92 89 88 84 68 75 70 BEC 79 78 74 75 ACC 70 75 CIS 82 85 85 BAN 93 90 90 Bob Charles Diego Fernando Gabriella Harry Jesus Jose Kim BEC 95 90 75 89 75 LIB 71 LIB 85 85 80 87 NUR 86 85 75 CRJ 71 77 Louis ACC 77 75 75 LIB 62 65 68 90 Year Quiz 1 Freshman 90 Sophomore 88 Freshman 56 Freshman 75 Freshman 64 Sophomore 89 Sophomore 91 Freshman 100 Freshman 67 Sophomore 87 Freshman 90 Freshman 66 Freshman 76 Sophomore 50 Sophomore 92 Sophomore 78 Freshman 89 Freshman 69 Sophomore 82 Sophomore 81 Freshman 71 Freshman 75 Sophomore 95 Sophomore 92 Freshman 88 Freshman 47 Freshman 59 Sophomore 60 Sophomore 66 Freshman 84 Mario Martha Mary Nestor 90 BAN LIB 89 80 90 86 MAT 86 90 89 BAN 74 75 Oscar Paula 72 81 ECE 81 85 CRJ 89 85 87 77 70 75 NUR BEC 80 81 75 85 ACC 90 92 BAN 89 85 89 Peter Quinn Roberto Ryan Salvatore Samantha Tom Tyler Veronica Vincent William ECE 85 90 89 BAN 61 70 65 72 70 CIS 68 69 ECE 70 CIS 72 75 85 72 77 88 BAN 86