Excel 2007 Tutorial 1.1. Calculating your GPA

Spreadsheets are one of the more common software applications used in the workplace. A spreadsheet
is a virtual worksheet, where numbers are calculated based on other numbers in the worksheet.
Spreadsheets are most useful for performing a what-if analysis where a few numbers are
changed and those changes propagate to other numbers on the worksheet, updating the calculations
automatically and allowing us to see the impact of our changes.
In this video, we will be using Microsoft Excel 2007. However, the concepts and techniques
are not exclusive to Microsoft Excel, and may be applicable to other spreadsheet applications
such as Google Docs and OpenOffice. In this video, we will be looking at how you
can use a spreadsheet to perform a what-if analysis on your GPA, if you happen to use
a 4.0 Grade Point Average scale. If you do not use a 4.0 GPA scale, you could adapt this
spreadsheet to your grading system. During this video, we will introduce some
terminology and techniques that may be new to you. Please feel free to pause this video
any time you like or replay previous portions of the video.
Opening the spreadsheet available on my website, I see that the spreadsheet consists of rows
and columns. The rows are indicated by numbers on the left side of the screen while the columns
are indicated by letters on the top of the screen. The intersection of each row and column
is called a cell. If I click on a cell, I can see that I have
selected that cell and a black border now exists around the cell. I can see that the
current row and current column have been highlighted. If I look in the name box, I can see the cell
address, which is the combination of the column letter and the row number. If I look at the
bottom of the screen, I can see my current worksheet, and any other worksheets that exist
in this file as additional tabs. The file itself, which might contain multiple worksheets,
is called a workbook. If I look in the formula bar, I see the contents
of the cell. Formulas are spreadsheet calculations performed within a cell.
For our purposes, we want to replace the red text within these cells with the appropriate
formulas. The 4.0 scale GPA is computed by converting
a letter grade to a number from 0 to 4. This number is the number of Quality Points that
will be earned for each credit that the class is worth. An A is worth 4 quality points per
credit, a B is worth 3 quality points, a C is worth 2 quality points, a D is worth is
1 quality point, and a failing grade is worth zero quality points per credit.
In our current spreadsheet, these quality point values have been entered manually. In
a later video, we will look at how we can have the quality point values appear automatically
based on the letter grade. We want to calculate the Quality Points earned
by multiplying the number of credits for the course by the quality point value for the
grade. In order to enter our formula into Excel,
we simply select our cell and begin typing. First we will enter the equal = sign. Any
time the equal sign is the first character in a cell, this means that the cell contains
a formula or calculation. Cells that simply contain a number, text, or a date will not
contain an equal sign as the first character. Once we have entered the equal sign, we can
click on the cell reference for the number of credits.
We then enter an asterisk *, to indicate that we want to perform multiplication, and click
on the cell for the quality point value. We press enter and the calculated value appears
within our cell. If we reselect our cell, we can see our calculation formula in the
formula bar. We can edit this cell by editing the formula in the formula bar.
We can also edit the contents of the cell by double clicking on the cell or pressing
the F2 key. When we edit the cell this way, it shows the cell addresses with color coding
that matches the borders of the highlighted cells.
This way we can immediately see which cells are impacted by our formula based on the color
coding. I can also create the formula by typing cell
references instead of using the mouse. Or I can create the formula by using the arrow
keys on my keyboard. I can add the equal sign =, press the left arrow until I am highlighting
the cell containing the number of credit, add the asterisk *, use the left arrow to
select the quality point grade cell, and then finish the formula by pressing enter.
Once I have created all of the formulas for this column, I can create a calculation for
the total of my QP earned. Instead of using the asterisk * for multiplication,
I can use a plus sign + for addition. Entering an equal sign = to begin my formula, I can
then click on each cell and add a plus sign + between each cell address.
If I double click on the cell I was working on, I can see each of the cell references
that I was working with. This is not really the best we way we can perform this calculation.
If I had clicked on the wrong cell, I might have picked an adjacent cell resulting in
an incorrect calculation. Instead of selecting each cell I wish to add,
I can click on the cell I would normally enter my formula in and use the AutoSum button in
the Home ribbon. When clicking on AutoSum, Excel automatically creates a formula that
will sum the values for this column. It does this by using a function. A function
is an excel command that performs a specific task in a formula. As this is a formula, we
still have our leading equal sign =. After the equal sign =, we have the SUM function
keyword, which indicates that the selected numbers will be summed. After the keyword,
we have a parenthesis ( ) which indicates the information the function will be operating
on, and inside the parenthesis ( ) we have the cell addresses that will be summed. What
you might notice about these cell addresses is that only the first cell and the last cell
are actually referenced. The colon : in between the two addresses indicates that this is a
range of cells and all cells between first and last cell will be included.
We can press enter and see the resulting calculation. We will talk more about functions in a later
video. Finally, we will calculate the GPA for this
first semester by dividing the sum of Quality Points earned by the sum of the number of
credits. We will type the equal sign to begin our formula, click on the sum of Quality Points
earned, press the forward leaning slash / on the keyboard which indicates division,
and then click on the cell for the sum of credits.
Pressing enter, we see our calculated GPA on the 4.0 scale for this first semester.
When it comes to using Excel, and really computers in general, there is an overall philosophy
that if you are taking a lot of effort to do something, you probably are not doing it
the most efficient way possible. Computers should be used to make our lives easier, not
produce more work. So we will fill out the two remaining semesters
with this philosophy in mind. Instead of recreating the same formula, over
and over again, we can use copy and paste. Notice that when we copy and paste a cell
containing a formula, the cell addresses are automatically updated relative to the location
we pasted our cell to. These are called relative cell references. In a later video, we explore
relative cell referencing, and the counterpart method, absolute referencing, in more detail.
We can also paste a cell into multiple cells at the same time, by copying the cell and
then selecting the multiple destination cells before pasting. We can do this with our other
formulas as well, including the formulas that contain functions, and the references are
automatically updated to reflect the new location of the formula.
For our third semester, let us take a look at another technique for rapidly populating
cells. I will copy and paste a single cell containing
my formula to the new semester. However, this time, instead of pasting to multiple cells,
we will paste to a single cell and then use the feature called the fill handle.
As we move the mouse cursor around the selection, you may notice that cursor changes.
If the cursor is a white cross with black borders, this means that dragging the mouse
will create a selection of multiple cells. If the cursor is a black solid cross with
arrow heads on each end and overlaid with a white arrow head cursor, this means that
dragging the mouse will move the current cell. If the cursor is above the fill handle, the
black square set in the lower right corner of the selection border, the cursor will turn
into a black cross with no arrow heads. This means that dragging the mouse will replace
the destination cells with a series of values depending on the current cell.
The fill handle is a very convenient tool that looks at your current cell selection
and tries to predict how you would like adjoining cells to appear. In the second worksheet in
this file, we have a number of examples of how the fill handle might be used. The fill
handle can create a series out of numbers, text, dates, and formulas.
In some cases, we might want a series to skip numbers or behave differently, such as counting
by fives. To achieve this effect, we just need to start with a selection showing an
example of the difference we want to see between each item in the series. The fill handle isn’t
perfect, but it is a great time saver. Returning to our original worksheet, let’s
add our totals for total credits attempted, total Quality Points earned, and our current
GPA for our three semesters of college. Once we have completed our worksheet, we can
right click on the tab for the worksheet and duplicate it, by clicking on “Move or Copy”
and checking the box for “Create a copy”. We can then rename our copied worksheet, using
the original sheet to hold our current grades, while we use the copy for our What-If scenarios.
As we make changes in the values to our What-If worksheet, Excel automatically recalculates
our worksheet and we can see the impact that it has on our overall GPA, but without impacting
the data that exists on our original worksheet.