Bellingham School District

SPREADSHEET BASICS:

GPA CALCULATION

GOAL: Use basic skills to design a spreadsheet:

RESOURCES:

Online tutorial, Spreadsheet Features, Formula Basics

ASSESSMENT:

Skills Checklist

Task: Develop a spreadsheet that calculates your grade point average (GPA), based on estimates of your grades.  It should look similar to this:

I. Fill in and format the table:

1.)   Rows 1 and 2 - capitalize and bold the title; center title and your name

2.)   Row 3 - leave blank

3.)   Row 4 - make column headings--names of your categories--bold

4.)   Column 1 - names of your current classes

5.)   Column 2 - your teachers' last names

6.)  Adjust column widths to fit longest names

7.)   Column 3 - the grades you estimate you will receive

8.)   Column 4 - use these values to calculate grade points:

9.)   Add borders and shading as shown, choosing your own colors

10.)   Darkly shade bottom rows of Teacher / Grade columns--to block data entry

11.)   Align names left and numbers right

12.)   Title: change to a "stylish" font that you like, and increase size to 12*

*If the font style is small, increase its size so it appears larger than the column headings

II. Add formulas to calculate grade point average.

1.)  Total: select the cells with numbers in the Point Value column and click AutoSum 

2.)  GPA: select the GPA cell and use either of these methods:

• In the formula bar, type =, the location reference for the Total Point Value cell, a forward slash (division sign), and the number of grades to average [=D11/6], OR
• In the formula bar, click  and choose AVERAGE from the drop down list.  Correct the formula -- do not include the location reference for the Total cell [=AVERAGE(D5:D10)]

3.)  Notice that the formulas are different in the two methods above, but the results are the same.

III.  Center the table on your page and add a header (or footer, as preferred by your teacher)

1.)  File > Page Setup > Margins tab: for Center on page, check Horizontal & Vertical boxes

2.)  File > Page Setup > Header/Footer tab:

• Click Custom Header (or Custom Footer), and in the Left section, type your name and class period on 2 lines

IV.  Name the worksheet tab

Double click on the sheet tab to rename it GPA

V.  Make a copy of your sheet and use AutoFormat

1.)  Edit > Move or Copy Sheet:  (Not "Copy" --look farther down on the menu):

  opens dialog box: 

• To book: no change here
• Before sheet: select where the copy should go
• You must check Create a copy or you will only move the sheet.

• Excel gives the copied sheet the same name with a number:
• Double click sheet tab to rename:

2.)  Use the copied sheet:

• Select your table and change the format: go to Format > AutoFormat
• Scroll through examples and choose a format to apply to your table
• Try different formats--use the undo button to go back--and choose one that works
• Add a header as in Step III
• Add this footer: AutoFormat style: ___________ (you will see the name under the style example in the AutoFormat dialog box)

  

VI.   Save your workbook

A workbook is a file which can contain as many spreadsheets as you need.

1.)   Save the file in H:\Tech Connections\Spreadsheets, using your initials and gpa (--gpa.xls)

2.)  Print or show your teacher electronic copies of your tables, as requested.