A

B

C

D

E

F

G

1

Courses

Course

Grade

Grade

Graded

Earned

Total

2

By Location & Date

Units

Received

Points

Units

GPA

Units

3

4

5

Spring 1999

6

2.0

A

8.0

2.0

4.000

2.0

7

8.0

2.0

4.000

2.0

8

Fall 1999

8.0

2.0

4.000

2.0

9

3.0

U

8.0

5.0

1.600

2.0

10

8.0

5.0

1.600

2.0

11

8.0

5.0

1.600

2.0

12

Spring 2001

8.0

5.0

1.600

2.0

13

3.0

A-

19.1

8.0

2.388

5.0

14

3.0

CR

19.1

8.0

2.388

8.0

15

19.1

8.0

2.388

8.0

16

Fall 2001

19.1

8.0

2.388

8.0

17

3.0

B-

27.2

11.0

2.473

11.0

18

3.0

W

27.2

11.0

2.473

14.0

19

3.0

B+

37.1

14.0

2.650

17.0

20

3.0

NC

37.1

14.0

2.650

17.0

21

3.0

I

37.1

17.0

2.182

17.0

22

23

A

B

C

D

1

Letter

Grade

Count

Count

2

Grades

Values

GPA

Units

3

4

A

4.0

1.0

1.0

5

A-

3.7

1.0

1.0

6

B+

3.3

1.0

1.0

7

B

3.0

1.0

1.0

8

B-

2.7

1.0

1.0

9

C+

2.3

1.0

1.0

10

C

2.0

1.0

1.0

11

C-

1.7

1.0

1.0

12

D+

1.3

1.0

1.0

13

D

1.0

1.0

1.0

14

D-

0.7

1.0

1.0

15

F

0.0

1.0

0.0

16

I

0.0

0.0

0.0

17

U

0.0

1.0

0.0

18

W

0.0

0.0

0.0

19

CR

0.0

0.0

1.0

20

NC

0.0

0.0

0.0

21

RD

0.0

0.0

0.0

INSTRUCTIONS FOR SPREADSHEET

Create an MS Excel Spreadsheet. You may begin by using the supplied datasheet here. Download this workbook, do a "Save as..." to the assignment name to preserve the original should you need to go back and start again. Open the datasheet in the Excel program.

Comic Sans MS font, size 12, will be used throughout.

Sheet 1--
The information in Rows 1 & 2 will be centered and bold. The column titles will be exactly as shown. Row two will be underlined.

Enter your information in columns A - C for each course you've completed or will attempt to complete. Enter at least 20 classes to complete this assignment. If you have fewer than 20 classes on your record, then you are to use this semester and, if necessary, add future classes that you intend to take in subsequent semesters that you will need to complete to satisfy your major's requirements. For these projected courses, enter grades that will cause your GPA calculation to fluctuate.

Column A is the institution(s) you attend(ed), the term & year as a subgroup with the subject number of courses taken during each term -- listed/sorted in alphabetical order by subject number.

The institution name, left justified and bold.

The term/year, centered and bold as shown.

The course number, all caps, no space between subject and number, left justified.

Leave a space between institutions (if applicable) and between terms (as shown.)

The last entry in this column will be your first name, last name, and classID, left justified.

Column B is the number of units the course is worth, one decimal place, right justified.

Column C is the grade you received (fictitious values are allowed) represented as a letter using +/- grading, right justified.

Enter at least one each of the following grade values for a class: letter grades, + grade, - grade, CR, NC, W, U, I.

Use conditional formatting to show any value of U or I in Bold and Green for the entire column.

Before proceeding move to the instructions for Sheet 2 -- Columns A-D and complete them.

Column D is calculated to be your grade points earned, one decimal place, right justified. It is the sum of the units multiplied by the grade.

In this example cell D6 =D5+IF(C6>0, B6*VLOOKUP(C6,Grades, 2, FALSE), 0) (where * indicates multiply/times). Calculations will be covered in class.

This calculation is dragged down the D column

Note: All calculations run from the first data cell down the column to the last data entry with no breaks or changes in the formulas.

Column E is a running total of your graded units, one decimal place, right justified. As a course is completed the graded units will update in this column.

In this example cell E6 =E5+IF(C6>0,VLOOKUP(C6,Grades,3,FALSE)*B6,0)

This calculation is dragged down the E column

Column F is a calculation of your GPA, three decimal places, right justified.

In this example cell F6 =IF(D6=0, 0, D6/E6)

This calculation is dragged down the F column

Using conditional formatting choose a value of GPA (3.00, 3.20, etc.) and show any value in column F that is less than or equal to the value you select in the styles Bold and Blue for the entire column. Choose a value that will yield at least 20% of your values in this manner (3.500 was used in the example above.) This will show you at a glance if you've fallen below your expectations at any time. This is useful in setting a GPA goal for graduation with honors (3.40+) or to enter Grad school (3.20+).

If you have received real grades that cause your GPA to remain constant, then I ask that you enter several made-up grades that will cause fluctuation in your GPA. This may also be necessary to be able to complete the assignment to show some of your GPA scores, that fall below a predetermined number, in Bold and Blue.

Column G is a running calculation of your total units earned toward graduation, one decimal place, right justified. As a course is completed the earned units will update in this column.

In this example cell G6 =G5+IF(C6>0,VLOOKUP(C6,Grades,4,FALSE)*B6,0)

This calculation is dragged down the G column

Sheet 2 --
Columns A-D contain a lookup table of values to be used to convert your letter grades into numbers to be calculated They also contain values to indicate if a values applies to a particular calculation.

Column A contains all letter grades shown in the example above, right justified.

Column B contains the grade values listed in the catalog for the grade in column A, one decimal place, right justified.

Column C contains a 1 if this grade counts toward the GPA calculation in column F or a 0 if it doesn't, one decimal place, right justified.

Column D contains a 1 if this grade counts toward the Total Units calculation in column G or a 0 if it doesn't, one decimal place, right justified.

Select all of the cells in your lookup table (Sheet 2 A4-D21 in the example above) and click into the name box in the formula bar to select the name. Type in the name Grades and hit Enter to record the name. You can instead go to Insert/Name/Define... and name your selection Grades in this window. This name matches the naming used in the formulas for columns D-G.

Chart 1--
Create a chart to show GPA change:

Select only the data in Column F. This would be from row 6 down through the end of the courses you entered.

Go to (Insert/Chart .../Chart Wizard Step 1 of 4//Standard Type/Column) and select the clustered column view. Click "Next>".

Select tab for data range and select columns (Chart Wizard Step 2 of 4/Data Range/Columns).

Select tab to enter Series information (Chart Wizard Step 2 of 4/Series/) where series one name = GPA. Click "Next>".

Select the tab titles to name your chart "YI (your initials, first, last) GPA" (Chart Wizard Step 3 of 4/Titles/Chart Title = YI GPA). Click "Next>".

Place your chart in a location (Chart Wizard Step 4 of 4/Place chart: As new sheet: GPA) as a separate sheet with the name GPA and click "Finish".

Chart will be listed in the bottom left corner of your sheet where sheets are shown.