Uploaded by dbonizio on 05.10.2010

Transcript:

Hi, this is Dawn Bonizio and I’d like to explain to you when and how to use certain

cell address types in Microsoft Excel on Windows. As you may recall from Kelly’s screencast,

Excel allows you to use formulas in order to make calculations with data entered into

a spreadsheet. Here is an example of a useful way to use Excel to analyze data about candy

sales in an elementary school. To make this whole process efficient, you’ll

want to type a formula once and reuse or copy it into multiple cells without retyping it.

This is when you must know the difference between relative, absolute, and mixed cell

address types. After we work through creating a spreadsheet like the Candy Bar example,

you’ll better understand what I mean. Before we look at the details of the candy

bar spreadsheet, let’s take a look at a simpler one involving students’ test scores

that you’ve already seen in Kelly’s screencast on working with formulas in Excel. Rows are

horizontally across the template, numbered vertically on the left. Columns are vertically

up and down the template, and are labeled with letters in alphabetical order across

the top. “Student name” is located in the cell whose address is A1, “Test 1”

is located in B2, and John’s second test score is recorded in C3.

Recall from Kelly’s screencast that we can use the AVERAGE formula to get the test average

for the first student, and then fill it down for the remaining students. In order to fill

in the averages for the rest of the students, you’ll want to grab the bottom right corner

of F2 and drag down through the rest of column F all the way to Elizabeth’s information.

As you can see, when you swipe across the spreadsheet, the formula changed down column

F as needed in order to correctly calculate each student’s test score average.

“Relative” cell address types were used throughout this spreadsheet. “Relative”

means that as the formula was copied, each cell address’s row number changed relative

to the row where it was located. For example, see that B2 changed to B3 changed to B4 and

so on all the way down the column. Now, let’s return to the Candy Bar spreadsheet.

In cell C6, I created a formula that calculates the projected funds raised from candy bar

sales based on students per grade level and number of candy bars sold per student. This

formula in cell C6 reads “=B6*C5*C3”. So notice that this correctly calculates the

funds that would be raised by 60 kindergarteners if they sold 5 candy bars each. It multiplies

the number of students by the number of candy bars by the net profit per candy bar. Now

just like in our earlier example, I should be able to drag the bottom right corner of

this cell down through C11 and over to column F in order to fill out the rest of the spreadsheet.

Upon observations of the data in the spreadsheet, you should notice that something clearly went

wrong. “What went wrong” you ask? The calculations are inaccurate because relative

cell addresses were used in the spreadsheet. Sometimes, like in this example, we don’t

want the cell addresses in a formula to change row number and column letter relative to location.

In the formula typed into C6, I multiplied 60 Grade K students, 5 candy bars, and the

net profit per candy bar in order to figure out what the total funds raised would be if

all Grade K students sold 5 candy bars each. The number of Grade K students has the cell

address of B6. As I use my formula throughout the spreadsheet, I want to change the Students

per Grade Level, which is a change relative to row but not column. The 5 candy bars per

student has the cell address of C5. As I use my formula throughout the spreadsheet, I want

to change the Number of Candy Bars sold per student, which is a change relative to column

but not row. The net profit per candy bar has the cell address C3. As I use my formula

throughout the spreadsheet, I do not want this cell address to change relative to row

OR column. If you want a cell address to change throughout

a table relative to row but not column, you have to use the MIXED cell address type that

requires a dollar sign next to the column label. That dollar sign means that you do

not want the column letter to change relative to the column of the cell that the formula

is put into. If you want a cell address to change throughout a table relative to column

but not row, you have to use the MIXED cell address type that requires a dollar sign next

to the row label. That dollar sign means that you do not want the row number to change relative

to the row of the cell that the formula is put into.

If you want a cell address to stay the same regardless of the location where you reuse

a formula, you have to use an ABSOLUTE cell address with a dollar sign to the left of

the column letter and a dollar sign to the left of the row number.

In order to have my first cell address of the formula change relative to row but not

column, I have to use the proper mixed cell address type. The proper mixed cell address

in this case would be $B6. Notice that the RELATIVE cell addresses used previously did

not have any dollar signs, but in this mixed cell address there is a dollar sign to the

left of the column label. In order to have my next cell address of the

formula change relative to column but not row, I have to use the mixed cell address

C$5, with a dollar sign to the left of the ROW label.

In order to have the last cell address of my formula, the net profit per candy bar,

not change relative to NEITHER row NOR column, I have to use an ABSOLUTE cell address type.

This looks like $C$3, with dollar signs next to the ROW AND COLUMN.

Now I’ll be able to get the correct calculations in my table. The last step would be to drag

the bottom right corner of cell C6 down through the last row and across the last column of

the spreadsheet. WALA! Now we have the correct calculations, because

we used the correct cell addresses. For example, in cell D8 the formula is looking for the

number of students in cell B8 and the number of candy bars sold per student in D5 and multiplying

these values together by the net profit per candy bar located in C3. In all of the cells

that I copied the formula to, it is always looking to multiply what is in C3, the net

profit per candy bar. This all is exactly what we want to happen.

With a little knowledge of types of cell addresses, you only had to type in the formula once and

could then copy it all the way across and down the spreadsheet without retyping it for

each cell, and then, could end up with correct calculations! If you were wondering about

projected total funds raised from candy bar sales in this school, now you’ve got lots

of great information to work with. I hope that now you can understand what relative,

mixed, and absolute cell address types are and when you’ll want to use each type. With

this new knowledge, you can go ahead and complete accurate calculations quickly and easily.

cell address types in Microsoft Excel on Windows. As you may recall from Kelly’s screencast,

Excel allows you to use formulas in order to make calculations with data entered into

a spreadsheet. Here is an example of a useful way to use Excel to analyze data about candy

sales in an elementary school. To make this whole process efficient, you’ll

want to type a formula once and reuse or copy it into multiple cells without retyping it.

This is when you must know the difference between relative, absolute, and mixed cell

address types. After we work through creating a spreadsheet like the Candy Bar example,

you’ll better understand what I mean. Before we look at the details of the candy

bar spreadsheet, let’s take a look at a simpler one involving students’ test scores

that you’ve already seen in Kelly’s screencast on working with formulas in Excel. Rows are

horizontally across the template, numbered vertically on the left. Columns are vertically

up and down the template, and are labeled with letters in alphabetical order across

the top. “Student name” is located in the cell whose address is A1, “Test 1”

is located in B2, and John’s second test score is recorded in C3.

Recall from Kelly’s screencast that we can use the AVERAGE formula to get the test average

for the first student, and then fill it down for the remaining students. In order to fill

in the averages for the rest of the students, you’ll want to grab the bottom right corner

of F2 and drag down through the rest of column F all the way to Elizabeth’s information.

As you can see, when you swipe across the spreadsheet, the formula changed down column

F as needed in order to correctly calculate each student’s test score average.

“Relative” cell address types were used throughout this spreadsheet. “Relative”

means that as the formula was copied, each cell address’s row number changed relative

to the row where it was located. For example, see that B2 changed to B3 changed to B4 and

so on all the way down the column. Now, let’s return to the Candy Bar spreadsheet.

In cell C6, I created a formula that calculates the projected funds raised from candy bar

sales based on students per grade level and number of candy bars sold per student. This

formula in cell C6 reads “=B6*C5*C3”. So notice that this correctly calculates the

funds that would be raised by 60 kindergarteners if they sold 5 candy bars each. It multiplies

the number of students by the number of candy bars by the net profit per candy bar. Now

just like in our earlier example, I should be able to drag the bottom right corner of

this cell down through C11 and over to column F in order to fill out the rest of the spreadsheet.

Upon observations of the data in the spreadsheet, you should notice that something clearly went

wrong. “What went wrong” you ask? The calculations are inaccurate because relative

cell addresses were used in the spreadsheet. Sometimes, like in this example, we don’t

want the cell addresses in a formula to change row number and column letter relative to location.

In the formula typed into C6, I multiplied 60 Grade K students, 5 candy bars, and the

net profit per candy bar in order to figure out what the total funds raised would be if

all Grade K students sold 5 candy bars each. The number of Grade K students has the cell

address of B6. As I use my formula throughout the spreadsheet, I want to change the Students

per Grade Level, which is a change relative to row but not column. The 5 candy bars per

student has the cell address of C5. As I use my formula throughout the spreadsheet, I want

to change the Number of Candy Bars sold per student, which is a change relative to column

but not row. The net profit per candy bar has the cell address C3. As I use my formula

throughout the spreadsheet, I do not want this cell address to change relative to row

OR column. If you want a cell address to change throughout

a table relative to row but not column, you have to use the MIXED cell address type that

requires a dollar sign next to the column label. That dollar sign means that you do

not want the column letter to change relative to the column of the cell that the formula

is put into. If you want a cell address to change throughout a table relative to column

but not row, you have to use the MIXED cell address type that requires a dollar sign next

to the row label. That dollar sign means that you do not want the row number to change relative

to the row of the cell that the formula is put into.

If you want a cell address to stay the same regardless of the location where you reuse

a formula, you have to use an ABSOLUTE cell address with a dollar sign to the left of

the column letter and a dollar sign to the left of the row number.

In order to have my first cell address of the formula change relative to row but not

column, I have to use the proper mixed cell address type. The proper mixed cell address

in this case would be $B6. Notice that the RELATIVE cell addresses used previously did

not have any dollar signs, but in this mixed cell address there is a dollar sign to the

left of the column label. In order to have my next cell address of the

formula change relative to column but not row, I have to use the mixed cell address

C$5, with a dollar sign to the left of the ROW label.

In order to have the last cell address of my formula, the net profit per candy bar,

not change relative to NEITHER row NOR column, I have to use an ABSOLUTE cell address type.

This looks like $C$3, with dollar signs next to the ROW AND COLUMN.

Now I’ll be able to get the correct calculations in my table. The last step would be to drag

the bottom right corner of cell C6 down through the last row and across the last column of

the spreadsheet. WALA! Now we have the correct calculations, because

we used the correct cell addresses. For example, in cell D8 the formula is looking for the

number of students in cell B8 and the number of candy bars sold per student in D5 and multiplying

these values together by the net profit per candy bar located in C3. In all of the cells

that I copied the formula to, it is always looking to multiply what is in C3, the net

profit per candy bar. This all is exactly what we want to happen.

With a little knowledge of types of cell addresses, you only had to type in the formula once and

could then copy it all the way across and down the spreadsheet without retyping it for

each cell, and then, could end up with correct calculations! If you were wondering about

projected total funds raised from candy bar sales in this school, now you’ve got lots

of great information to work with. I hope that now you can understand what relative,

mixed, and absolute cell address types are and when you’ll want to use each type. With

this new knowledge, you can go ahead and complete accurate calculations quickly and easily.