## Creating a GPA Calculator Step-by-Step Tutorial

NOTE:  There is are formula errors in the printed CIN article.  All of the letter grades should be enclosed in double-quotes for example "A".  The comma follows the double-quotes.  Jeanne Sewell

Purpose:

At the end of this tutorial the learner will be able to:

1. Create a GPA calculator using Microsoft Excel.
2. Use the formulas for current date and time; logical functions, sum, and averages in a spreadsheet.
3. Use conditional formatting for cell values.
5. Save a spreadsheet as a template file for reuse.

Nurse educators, administrators, and project managers are often required to analyze numerical data in an expeditious manner. For educators, frequent tasks include calculating grades and grade point averages (GPA), and creating simple budgets for departments or grants. All of these can be automated and simplified with a technological solution.  Microsoft Excel, electronic spreadsheet software, is an easy-to-learn tool for organizing, displaying, and analyzing numbers. Excel can also perform the multiple calculations we frequently need in the workplace.

The Nursing Science GPA Calculator demonstrate how Excel can be used to perform calculations, to format data according to rules, and to change a “letter” to a “number” automatically.  In the following example, we will create a spreadsheet that calculates student GPAs and can be used by faculty and students, online or stored on a disk, and then save the calculator as a template for future use. The GPA Calculator is based upon the 4-point grade scale using the grades A, B, C, D, and F.

Figure 1 - GPA Calculator

You can download the Excel Spreadsheet example and adapt it for your use by simply replacing the names of the Class, Course, and Credits.  The formulas in Columns E and F are used to generate earned credit hours and quality points using data in the Grade column (Column D) and the Credit Column (column C).

Create a New Worksheet

When you open Excel, the default file is a workbook that consists of three spreadsheets.  We will be working with only first spreadsheet in the workbook.  Double-click on the “Sheet 1” tab at the bottom of the worksheet and rename it GPA Calculator to make it easy to identify. Next, save the file and name it GPA Calculator. As you type in data into the cells, you may see a string of ##### instead of what you typed if the column width is narrower than the data you typed.  If you would like to adjust the width of the columns, simple – just place your cursor between the column letters until it turns into a cross hair and either double-click to auto-adjust the width or use the mouse to click and drag to adjust the column width.  Remember to save your work frequently.

Entering Information into the Worksheet with Current Date & Time

Now you are ready to enter data.  Remember that columns run from top to bottom vertically and are labeled using the alphabet.  The rows or records run from left to right horizontally and are numbered.  The GPA calculator will have a title, and include the date and time that it is completed.  The date and time will be automatically generated from a formula when the worksheet is updated.

1. To title the form, click on cell B1.  Type            Nursing Science GPA Calculator

2. To show when the file was updated, click on cell E1.  Type       Updated

3. To show the current date, click on cell F1.  Type           =NOW()  Remember to place the equal sign = in the formula.  Remember to resize the column to get rid of the ####.

4. To have the cell formatted for a date, right-click on cell F1.  On the pop-up menu click on Format Cells > Number > Date.

5. Select a date format that shows both the date and time.

The formula in cell F1, =Now(), automatically shows the current date and time to be able to identify the most current form, if the GPA has had to be calculated more than one time. This formula will cause cell F1 to always show today’s date if the worksheet was calculated; it is not updated continuously.  The NOW function is especially helpful if you keep a printed version for your files. Of course, you may prefer to enter the date manually.

Now we are ready to identify six column names for the GPA calculator:  Class (the course classification); Course (the course number and name); Credit (credit hours); Grade (the grade the student made in the course); Earned Credits (the credit hours earned); and Quality Points (the quality points earned). Before you begin, make the columns wider so that you can see the data that you type into the cells.

1. Click on cell A2.  Type Class

2. Use the Tab key or point and click the mouse to move to cell B2.  Type           Course

3. Repeat Step 2 to enter the remaining column headings: Credit; Grade; Earned Credits; Quality Points.  Resize the columns so that you can view the column headings.

4. The next step is to enter all of the course information. Enter the Class, Course, and Credit data using Figure 1 as a guide.

5. In cell B10, type Total Credits/QP. In cell B11, type Science GPA.  Highlight cells B10 and B11, and then tap the Right Align icon in the menu bar.  When you finish, resize the columns so that you can view all of the data.

You are ready to enter the formulas that calculate credit hours, quality points, and the GPA.

Creating Calculated Fields

The grade point average is the sum of quality points divided by the number of earned credits.  We need formulas to calculate the quality points and earned credits for each course listed on the GPA calculator. Afterwards, we will create a formula to sum the total earned credit hours and quality points.  Finally, we will create a formula to calculate the GPA.  To make things easier, we will create the formulas just once, then copy and paste them into the associated cells.

The earned credits and quality points are calculated using the logical function, IF, a very powerful formula.  The IF formula is a conditional formula that returns one value if the condition you specify is TRUE, and another value if the condition is FALSE.  You can include (nest) up to seven IF statement in a formula.  If you make a mistake, don’t panic.  Use Undo (Edit > Undo Format Cells) to restore the changes.

Step A: Formula for Earned Credit Hours

The formula in Column E for earned credits hours looks at the Grade and returns a numerical equivalent for the grade.  In cell E3 type in the formula to determine the earned credit hours.

Type =IF(D3="A",C3,IF(D3="B",C3,IF(D3="C",C3,IF(D3="D",C3,IF(D3="F",C3,""))))) and tap the Enter key. Because there is no data in the Grade column, after you tap Enter nothing will show in that cell, but the formula will be visible in the formula bar.

What this somewhat intimidating formula means is this:

• If the value in cell D3 is an “A” (the condition is TRUE), display the credit hours shown in C3.

• If the value in D3 is a “B”, display the credit hours shown in C3.

• If the D3 is a “C”, display the credit hours shown in C3 and so on.

• The two double quotes at the end of the formula mean that if D3 is not equal to an “A”, “B”, “C”, “D”, or “F” (the condition is FALSE), leave E3 blank.

Step B: Formula to Calculate Quality Points

The next step is to type the formula to calculate the number of quality points.  The Quality Points formula uses data for Grade and Earned Credits.  The formula for Quality Points in Column F looks at the Grade in Column D and multiplies the associated Credit Hours in Column E times the equivalent Quality Points.  In the example, we use the 4-point scale where an “A” is 4 points, a “B” is 3 points, a “C” is 2 points, a “D” is 1 point, and an “F” is 0 points.  Click in cell F3. Type =IF(D3="A",4*E3,IF(D3="B",3*E3,IF(D3="C",2*E3,IF(D3="D",1*E3,IF(D3="F","0",""))))) and tap the Enter key.

What the Quality Points formula in F3 means is this:

• If D3 is an “A” (the condition is TRUE), then multiply the value in E3 (Earned Credits) times 4 (the quality points for an “A”).

• If D3 is a “B”, then multiply the value in E3 (Earned Credits) times 3 (Quality Points for a “B”).

• If D3 is a “C”, then multiply the value in E3 (Earned Credits) times 2 (Quality Points for a “C”) and so on.

• If D3 is a “D”, then multiply the value in E3 (Earned Credits) times 1 (Quality Points for a “D”).

• If D3 is an “F”, then the quality points for an “F” are 0.

• If D3 is not equal to “A”, “B”, “C”, “D”, or “F” (the condition if FALSE), leave cell F3 blank.

Test the formulas to make sure that they work correctly.  Type in the letter grade A in cell D3.  You should see the number “3” in the E3 and the number “12” in F3.  Test the formulas with the other letter grades, B, C, D and F.  Type in something other than the letter grades and nothing should happen.

Now that we have created the formulas that calculate earned credit hours and quality points, we need to copy the formulas into cells for each of the courses.

1. Click on cell E3 and drag the mouse to F3 to highlight the cells.

2. Right-click the mouse and select Copy.

3. Highlight the cells E4 though F9.  Right-click the mouse and select Paste.

Step C: Formulas to Sum Credit Hours and Quality Points

The next step is to create formulas to sum the credit hours and quality points.  After we create the final two formulas, we will be ready to test the GPA calculator again.

1. Click on cell E10, and then click on the AutoSum icon in the menu bar (See AutoSum figure).

2. Click in Cell E3 and drag the mouse to highlight Cells E3 through E9. The formula (=SUM(E3:E9) to sum their values is visible in the Formula Bar.

3. Tap the Enter key to accept the formula.

4. Right-click on E10 and select Copy and then click on F10.  Right-click and paste the formula into the cell.

5. Test the formulas once again using all of the grades.  See the AutoSum Figure where showing testing with the letter grade “A.”

Figure 2 – Sum Formula for Earned Credits

Step D: Formula to Calculate the Science GPA

The last formula we create will be to calculate the Science GPA by dividing the sum of quality points in cell F10 by the sum of earned credits in cell E10.  We’ll use another IF statement to prevent the error message “#DIV/0!” from showing up when the quality points in F10 is a “0” because no grades or the GPA is a 0 have been entered into the GPA calculator the GPA is a 0.

1. Click on cell F11.  Type =IF(F10=0, "0", F10/E10)

2. Tap the enter key to accept the formula.

3. Test the GPA calculator by entering grades once again. Remember to save your file frequently.

Figure 3 shows all of the formulas in columns E and F used for the GPA calculator.  To view the formulas in Excel, click the Control key and the tilde (~) key at the same time.  Click them once again to to return to the standard view.

Figure  3 - Formulas used in the GPA Calculator

Using Conditional Formatting

As noted earlier, if the student has made a D or F in a science course, the cell with the grade should be displayed in a light orange color as a flag. The conditional formatting feature allows you to specify a cell shading or font color for a given condition.  You can specify up to three conditions. The condition is not case sensitive; you could type a “D” or “d” and the conditional formatting will still work.

1. Click on all of the cells from D3 through D9.  Using the menu bar select Format > Conditional Formatting.

2. For Condition 1, select Cell Value Is from the 1st drop-down menu and then select equal to from the second drop down menu.  In the last box type =”d”

3. Click on the Format > Patterns.  Select the light orange (4th row down and 2nd color from the left). Click OK.

4. Click Add to add a second condition.  Repeat steps 2 except type the new condition, the letter “f”

5. Repeat step 3 to specify a cell color for the condition.  Click OK.

Figure 4 – Conditional Formatting

If the cell value for the Grade is equal to either a “D” or “F”, the cell will turn a light orange color (See Conditional Formatting figure).  The conditional formatting has been applied to all cells where you will enter a course grade.  You won’t see any change in the color of the cells until you enter a grade of “D” or “F.”  Next, test the GPA calculator again.

Control and Protect Formulas

Finally, “protect” the formulas you created to prevent the user from accidentally corrupting them or deleting them.  Excel offers the ability to protect part or all of a workbook, both with and without a password.  IMPORTANT: All of the worksheet cells are “locked” by default, but only if the worksheet is protected.  We need to specify the cells that we want the user to be able to modify, “unlock” those cells, and then protect the worksheet to prevent data entry in cells where there are formulas. We do not need to use a password to protect the data. Click in cell D3.  Highlight D3 through D9.

1. Right-click > Format Cells > Protection > uncheck the Locked check box (remember that the cells are locked by default).

2. Click anywhere on the spreadsheet to clear the selection, and then click on the menu bar and select Tools > Protection > Protect Sheet.

3. Remove the check by Select locked cells and then click the “OK” button.

Figure 5 – Protecting a Worksheet Without a Password

By placing a checkmark by the “Select unlocked cells” the user will have access only to the unlocked cells.

Test the Calculator

It is extremely important to test each condition specified in a formula before you disseminate the new form for others to use.  The easiest way is to first enter course grades of all A’s – the GPA should be 4.0; enter all B’s and the GPA should be 3.0, enter all C’s and the GPA should be 2.0, enter all D’s and the GPA should be 1.0, and enter all F’s and the GPA should be 0.  Make sure that the GPA calculates correctly.  When you enter a D or an F, the cell shading should change to the light orange color.  If you note any errors, unprotect the worksheet, make the necessary corrections, and then protect the worksheet again.

An example of the finished GPA calculator is shown in Figure 8.  As noted earlier, the grades are not case sensitive; you can use either upper or lower case for the grades.  The conditional formatting provides a visual alert about low course grades.

Once you are satisfied that the GPA calculator is working correctly remove all of the grades you used for testing purposes in Column D.

Figure 6 – Testing the GPA Calculator Using Sample Grades

Save the Worksheet as a Template File

The GPA calculator is now functional.  Go ahead and save the file as a template file.  The template file is a master file that is stored with all of the other MS Office templates.  This template can be accessed time and again and provide you a fresh start to a new grade calculator sheet.  It can also refresh your memory for the formulas you’ll need.

1. Click on File in the Menu bar > Save As.

2. Name the file GPA Calculator and then click the down arrow by the Save as type drop-down menu.  Select Template (*.xlt)

3. MS Office will automatically default to saving the file in the Templates folder (See Figure 9).  Click the “OK” button.

The next time that you select File > New in Excel, you should see the GPA Calculator template.  When you save a template file, Excel will save it as a worksheet file (.xls) by default.  You can download the GPA calculator used for this example from http://hercules.gcsu.edu/jsewell/CIN/

CONCLUSION

Excel can be used for many purposes because every cell can be uniquely formatted with text, numbers, and formulas.  In this example, we used Excel to create a GPA Calculator using cells formatted with formulas.  The NOW date formula was used to time and date the spreadsheet when it was opened.  The IF formulas were used to calculate earned credit hours and quality points for a given letter grade.  Finally, the AutoSum and division formulas were used to determine a GPA.  The conditional formatting feature provided us with a visual flag for certain data entries.  The Protect Worksheet prevented inadvertent formula corruption.

The formulas used in this example can be used for a variety of purposes such as budget analysis and patient outcomes analysis.  With a little planning and some practice, you should be quickly on your way to create tools that will assist you in analysis of data.  We have only covered a few of the many formula features in this example.  Enjoy the journey as you venture out developing your Excel software skills.

### References

1.         Beeson SA, Kissling G. Predicting success for baccalaureate graduates on the NCLEX-RN. J Prof Nurs. May-Jun 2001;17(3):121-127.

2.         Roncoli M, Lisanti P, Falcone A. Characteristics of baccalaureate graduates and NCLEX-RN performance. J NY State Nurs Assoc. 2000;31(1):17-19.

If you have any questions, email me at the email address shown below.

Jeanne Sewell, MSN, RN
Assistant Professor
Department of Healthcare Systems & Informatics
Georgia College & State University
Milledgeville, GA 31061
Email:jeanne.sewell@gcsu.edu