Your Grade Point Average 2007 version

  It's all about the Formulas and Cell References (Cell Names)

Use Excel to create a table for your current classes and grades.   Summer School students use Spring Semester classes.

Things to remember:

• No spaces in formulas
• When counting, remember to count only cells with numbers, not mixed with Alpha characters
• To delete a row:  Home/Delete/Sheet Row
• To add a row or column:  Home/Insert/Sheet Rows
• Formulas:  these have no spaces in Excel, but so that you can read the words,  there are spaces in formulas
• To add    =sum(cell name + cell name)
• To subtract     =sum(cell name - cell name)
• To divide        =sum(cell name / cell name)
• To multiply    =sum(cell name * cell name)
• To add a series of cells     =sum(cell name : cell name)
• To count cells                =count(cell name : cell name)
• To average cells            =average(cell name : cell name)

1. Open a new file
2. Change the layout of your worksheet to “Landscape” (Page Layout/Orientation/Landscape)
3. You will be setting up a table that will calculate your Grade Point Average (GPA).
4. In Cell A1, title your table as follows:

“(Your Name’s) GPA”

Select cell A1 and change the font of the title to size 18 and bold. Then highlight the necessary cells and center the title across your table headings using the “merge and center” function.

· Merge and Center

1.  Type in text

2.  Click away from text

3.  Click over cell A1 and get the white cross sign

4.  Drag mouse across cells to be merged and center

5.  Home/Alignment/Merge and Center.  Click on the merge and center icon

6.  Change the font size to 18

7.  Change the color of the cell and put a border around.  Right mouse click/format cells/fill or borders.  Change color and color border

1. In cell A2, insert today’s date using the formula =TODAY( )
2. Then create column headings for your table. In the following cells, type in the appropriate Headings:

A3= Period #

B3 = Course Title

C3 = Teacher

D3 = # of Units

E3 = Letter Grade

F3 = Grade Points

G3 = Total Points/Class  

1. Make each column heading font size 12, bold and use the “AutoFit Selection” feature to resize each column width. Home tab/Cells/Format/AutoFit Selection

1. Using your six-week grade report, fill in your information in the first 5 columns.
2. Use the following information to fill in the “Grade Points” column:

	A = 4.0	A- = 3.7
B+ = 3.3	B – 3.0	B - = 2.7
C+ = 2.3	C = 2.0	C- = 1.7
D+ = 1.3	D = 1.0	D - = .7
F + = 0.3	F = 0.0

1. Use a formula to fill in the “Total Point/Class” column. (You must multiply the number of units by the grade points.)   =sum(D4*F4)
2. In cell C11, type in the label “Total Units”. Bold this label.
3. In cell D11, write an equation that sums the “# of Units” column. =sum(D4:D9)
4. In cell  C12, type in the label “Total Points per Class”. Bold this label.
5. In cell D12, write an equation that sums the “”Total Points/Class’ column =sum(G4:G9)
6. In cell C13, type in the label “My GPA”.
7. In cell D13, write an equation that will divide your “Total Points per Class” by “Total Units”. =sum(D12/D11). Color the cell. Format/Cells/Patterns
8. In cell F11 write "Total Number of Periods
9. In cell G11 count the number of classes that you have using this formula =COUNT(A4:A9). You will be counting the column with the Period Numbers, and only those cells with numbers in them.
10. In cell F12 write "Total Grade Points"
11. In cell G12 add all of the Grade Points together =sum(F4:F9)
12. In cell F13 write "My GPA"
13. In cell G13, divide cell your Total Grade Points by the "Total Number of Periods". =sum(G12/G11)   Color the cell the same as D13. Format/Cells/Patterns.  This number should be the same as the amount for “My GPA” in cell D13.
14. In cell F15 write "My GPA"
15. You want to create an average for your classes  The formula to do averages is "=average()".  Using your grade points write the correct formula in cell G15. = average(F4:F9).  Color the cell the same as D13 and G13. Format/Cells/Patterns.  This number should be the same as the amount for “My GPA” in cell D13 and G13.
16. Continue with the IF statements - CONDITIONAL FORMULAS  - If this happens then this, else this - LINK  to the instructions.  Continue working by clicking on the link.
17. Rename Sheet 1 (down at the bottom of the spreadsheet) to GPA.  Right mouse click on Sheet 1 and click Rename.  Type in GPA.
18.  Save this file as “GPA”

Modified 08/17/2009