CS 3 - Kurtz Santa Monica College

  Spreadsheet Assignment
GPA & Grade Report

 

 

1.      Start Windows and launch Excel.

2.      Create the “GRADES” spreadsheet as shown.  Enter your name in cell A1 and the current date in cell A2.  Complete the rest of your spreadsheet to match the example.

3.      Make the following changes to your spreadsheet:

a)     Enter a formula in Cell F8 that will multiply the credits in Cell D8 by the points in Cell E8.

b)     Copy the formula in Cell F8 and paste it in Cells F9 through F12 to accomplish the same result in each row.

c)      Enter a SUM function in Cell F14 to total the five courses that appear in Column F.

d)     Type CS 3 in Cell F1.

4.      Save this workbook as GPA1.XLS.

5.      Make the following changes to your spreadsheet:

a)     Insert a row at Row 11 between BUS101 and BUS105.

b)     Type ENG103, 9203, B, 4, and 3 in Cells A11, B11, C11, D11, and E11 respectively.

c)      Type or copy a formula into Cell F11 to compute the value as before.

d)     Type UPDATED in Cell F2.

e)     Delete Row 12 to remove BUS105.

 

6.      Place a SUM function in Cell D14 that totals the credits column.

7.      Save the modified workbook as GPA2.XLS.

8.      Make the following changes to your spreadsheet:

a)     Center all the data in cells B6 through C12.  Right align the data in Cells D6 through F6.

b)        Format Cells D8 through F14 to number format, no decimals (0).

c)     Insert a row below Row 10.

d)      Copy Cells A10 through F10 into the new Row 11. Type ART205, 9301, A, 2, and 4 in Cells A11, B11, C11, D11, and E11 respectively.

e)        Move the word “GRADES” in Cell A4 to Cell C3.

f)        Change the standard (default) cloumn width to 8.

g)      Left-align the data in Column A.

h)        Change the width of Column A to 10.

 

9.      Save the modified workbook as GPA3.XLS.

10. Make the following changes to your spreadsheet:

a)     Type PART OF in Cell G5 and TOTAL in Cell G6.  Create a formula in Cell G8 to divide F8 (relative reference) by F15 (absolute reference).  Copy this formula into Cells G9 through G13.  Format Column G to percent, two decimals (0.00%).

b)     Change the font color to white and the fill color to black for the column headings in rows 5 and 6.

c)      Place a formula in Cell F4 to calculate the current GPA as (Total Points/Total Credits).  Format the cell to number format, two decimals (0.00).  Place the title GPA in Cell E4

 

11. Save this workbook as GPA4.XLS and submit all four files into your submittal folder.

 

 

 

 

 

 

GRADES Spreadsheet

 

 

 

A

B

C

D

E

F

1

Your Name

 

 

 

 

 

2

Date

 

 

 

 

 

3

 

 

 

 

 

 

4

GRADES

 

 

 

 

 

5

 

 

 

 

 

 

6

COURSE

TERM

GRADE

CREDITS

POINTS

TOT.PTS.

7

 

 

 

 

 

 

8

MUS101

9203

A

2

4

 

9

ART102

9203

B

3

3

 

10

BUS101

9203

A

3

4

 

11

BUS105

9203

B

3

3

 

12

HIS104

9301

B

3

3