Word, Excel, and Access assignment.
1
Microsoft Excel Assignment CMAP120/Maradei/Fall 2019
25 Total Points/-2 each error, omission, misspelling, etc. -5 no formulas/large concept missing
Using Microsoft Excel you will take a set of courses, credits and grades and calculate the GPA for your friend
who has just earned an AAS in Computer Gaming and Simulation. Part of the degree requires taking courses in
the major, taking general education courses, and taking electives. You will calculate their GPS in each of these
categories. They have already entered the data, but needs your help with the calculations and formatting.
Review the Sample uploaded for visual help as to what this part should look similar to when completed.
Set-up
1) Open the spreadsheet “GPA Calculator start” and save it as “GPA Calculator First Last” replacing the
words First and Last with your actual first and last name.
Enter Text and Numbers
2) In cell “A1” replace FirstName and LastName with your own best friend’s first and last name.
3) Select Cells “A1” through “M1” and “Merge & Center” the cells.
4) Select cells “A2” through “M2” and “Merge & Center” the cells. Make larger and bold rows 1 and 2 to
your liking.
Add Quality Points and Calculate GPA
5) Insert a new blank column between “F” and “G” – the new column will become a new blank column
“G.”
6) Add a similar blank column between “I” and “J” and between “L” and “M.”
7) Label “G4”, “J4” and “M4” as “Q.P.” for quality points earned
8) For every class in the major multiply the “Credits” times the “Grade” and put the answer in the quality
points column (for example cell “G12” is “=E12*F12” and “G13” is “=E13*F13”).
9) Calculate quality points for courses that are “General Education” and “Electives” (columns “J” and
“M”).
10) In cell “E9” enter the function “=sum(E5:E8)” and copy the formula across to cell “M9”
11) Since summing grades does not help delete the function in cells “F9”, “I9” and “L9”
12) Calculate the GPA for courses in the major by inserting into cell “F10” the formula “=G9/E9”. Repeat
the process by inserting into cell “I10” the formula “=J9/H9” and in cell “L10” the formula “=M9/K9”
(some will say #DIV/O! – don’t worry, you’ll fix a little later)
13) Repeat the process for calculating totals and GPA for the other three semesters on the worksheet
(rows 16-17, 24-25, and 31-32).
14) Replace the “#DIV/0!” (division by zero error) with the value 0 in cells “F10” and “L17” since no classes
in those areas equals no GPA (not an error message).
15) To calculate overall GPA for classes in the major (cell “F34”) type the formula
“=(G9+G16+G24+G31)/(E9+E16+E24+E31)”.
16) Copy and update the formula to cells “I34” and “L34” to calculate the overall GPA for “General
Education” courses and “Electives.”
2
Format the worksheet
17) “Merge and Center” cells “E3” – “G3”, “H3” – “J3”, and “K3” – “M3” then apply the Cell Style “Heading
2” to the merged cells.
18) Select cells “A4” – “M4” and apply the “Heading 3” style, then right-align the cells from “E4” – “M4”.
19) Select the cells “E5” through “M34” and format the numbers in the “Comma” number format with one
decimal.
20) Set the background of cells “E3” – “G10”, “E12” – “G17”, “E19” – “G25”, and “E27” – “G32” to a light
orange fill color of your liking. Set the background of cells “H3” – “J10”, “H12” – “J17”, “H19” – “J25”,
and “H27” – “J32” to a light yellow fill color of your liking. Set the background of cells “K3” – “M10”,
“K12” – “M17”, “K19” – “M25”, and “K27” – “M32” to a light green fill color of your liking.
21) Select cells “D10” – “M10” bold the text and apply “Outside Borders” to the selection. Repeat the same
formatting for cells “D17” – “M17”, “D25” – “M25”, and “D32” – “M32”.
22) Select cells “D34” – “M34” and apply the Cell Style “Total” and set the font to 14 points.
23) Add the following text (bold them too)
• In “D36” – “Highest Grade”
• In “D37” – “Lowest Grade”
• In “D38” – “Number of Classes”
• In “D39” – “Most Credits”
• In “D40” – “Least Credits”
• In “D41” – “Total Credits”
24) To calculate the totals for “Major” classes, first create two named ranges. The first named range is
“MajorGrade” and should include the cells “F5:F8, F12:F15, F19:F23, F27:F30”. The second named
range is “MajorCredits” and should include the cells “E5:E8, E12:E15, E19:E23, E27:E30”.
25) Insert the following formulas to calculate the summary data
• In “F36” – “=max(MajorGrade)”
• In “F37” – “=min(MajorGrade)”
• In “F38” – “=count(MajorGrade)”
• In “F39” – “=max(MajorCredits)”
• In “F40” – “=min(MajorCredits)”
• In “F41” – “=sum(MajorCredits)”
26) Format the cells “F36” – “F41” in the “comma” format with one decimal
27) Repeat the previous three steps to calculate the summary data for “General Education” and “Electives”
classes in columns “I” and “L”. Excel requires that each range name be different, so use names that
make sense (like “GEGrade”, “GECredits”, “ElectivesGrade” and “ElectivesCredits”) and are unique for
each set of data.
Finish the worksheet
28) Left Justify cell “A43” and insert the following text: Grades are converted as follows; "A" = 4.0, "B" =
3.0, "C" = 2.0, "D" = 1.0, and "F" = 0.0 and italicize.
29) Save the worksheet after changing the Author of the workbook as yourself (add your first and last
name to the document property Author) and looking over for typo or formatting errors. Make sure
your fill color and any formatting doesn’t go past the worksheet area used.
3
30) When you’re done, submit for grading in the Excel Assignment Link located in Course Content.
TO SUBMIT YOUR COMPLETED EXCEL ASSIGNMENT: Do not submit until your file is complete and ready for
grading, you do not get a second chance to update. Make sure your Excel file is closed before uploading.
1) Click on Course Content on BlackBoard Course Menu
2) Click on Excel Assignment title
3) Scroll down some and choose Browse My Computer
4) Find file you have saved, select it and hit Open
5) You will see file name and know file is attached.
6) Click Submit