Uploaded by djdates on 13.06.2010

Transcript:

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.

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.