Microsoft Excel Cell Address Screencast for Mac 2008


Uploaded by erincis220 on 04.10.2010

Transcript:
Hello, my name is Erin, and today I will be discussing how to effectively use cell address
types in Microsoft Excel.
Before we begin it is important to know what a cell address is. In Excel, each box where
you can put information has cell address, which consists of a column letter and row
number. For example, if you look where my cursor is currently, the cell address is B4.
The first type of cell address I am going to talk about is called a relative cell address.
Recall how Millie’s screen cast on formulas used a spreadsheet about test averages. Millie
was able to use a formula to find the class average of each test. Instead of writing a
new formula, she was able to “fill” the averages of tests as well as the class average
with the click of a mouse. If I look here my formula consists of finding the average
for student Rebecca from B2 to E2. Now, if I place my cursor in the corner of the cell,
my cursor changes and creates a fill handle. I then move down the spreadsheet and new averages
are created for each student. This is called autofill. If I look at the new averages given,
instead of averaging for student Rebecca, and new students average is calculated as
seen here with student Sarah. Because of the relative shift, the averages are filled relative
to the proper row we are trying to average. You can also do this vertically. If you wanted
to find each tests average, once again enter a formula to find the average for the first
test. When you fill across, the average shifts relative to the column we are averaging. In
looking at this example, when I filled across, the new test average that I wanted to find
shifted across as well.
While this is a useful tool, there are times when it is not beneficial for the cell address
in a formula to shift relative to a new row or column. In looking at this new set of data
about candy bars, to find the amount of money grade k would make if 5 candy bars were sold
by each student, the formula should multiply the price of the candy bar, the number of
students in the grade, and the number of candy bars sold. So here we have entered that formula.
When I fill across, you can see there is an error occurring. So what could the problem
be? Well, if we look at the formula for this cell, we want to multiply the number of candy
bars sold per student, the number of students in the grade level as well as price of the
candy bar. However, we see that the column letter has changed not only for the number
of candy bars sold per student but the price of the candy bar and the number of students
in the grade level. How to we fix this? Well, if we go back to the original equation, we
see that for C3, we do not want this cell to be shifting over columns relative to when
we autofill. To fix this, we put in a dollar sign. This dollar sign has nothing to do with
money, but in fact tells our formula that this cell should not be shifting by column.
Now if we look at cell C5, we see that in our formula we do want the cell to be shifting
over, changing the number of candy bars per student to fill in the table. Now, if we look
at B6, we see that we do not want this number to be shifting over because the number sixty
applies to this entire row. To stop that cell from shifting over, we again add a dollar
sign in front of the column letter. Now when I attempt to autofill I see there are now
values inputted. When I double check and check the formula of this cell, I see that we are
multiplying the correct three numbers. Now when I try to do this vertically, there is
another error occurring about value. Why did this happen? Well, if I look at the formula
for this I can see instead of multiplying the price of the candy bar, it has shifted
down to a value that we have already found, which is incorrect. Again when I look at C8,
instead of multiplying the number of candy bars sold per student, it’s multiplying
a random value because these both have shifted down. Now when I look at B9, I see that it
has also shifted down but it is the correct thing to do in this formula. How do I fix
this? Well, if I go back to my original formula, I can see that we do not want the row number
changing for the price of the candy bar. To stop this I again will put in a dollar sign.
Now when I look at C5, the number of candy bars sold per student, I also do not want
the row number to ever change so I place a dollar sign. Finally, when I look at cell
B6, I see that I do want the row number to change, because it will shift for the number
of student in grades. Now when I try to autofill, I see there are values put in correctly. To
double-check this, I click on a cell and I see that the correct numbers are being multiplied.
Now I am able to autofill for the entire table. To review these new types of cell addresses,
I see that in my formula column C and row 3 is never changing. For the number of candy
bars sold per student, the column is changing for different amount of candy bars but the
row is not. Now when I look at B6 I see that the row is changing and the column is not.
When something should not be changing, you put a dollar sign in front of the column letter
or row number.
I hope this has helped increase your knowledge on cell addresses, and if you have any other
questions, please check out our class wiki for more information