CS130/230 Lecture 2
Cell Referencing and Filling
Thursday, February 5, 2004
Cell
references in formulas can be relative or absolute.
-
Relative
references: refer
to cell references in formulas in relation to the cell that contains the formula.
This is what we have used so far.
-
Absolute
references: refer
to cells in their absolute or fixed position.
Typically,
when creating SS, you want to get some pattern going so that you can do some
kind of fill either down or right. When filling, Excel adjusts the cell
references in formulas based on whether they are relative or absolute.
LetÕs go
back to the worksheet that you created last time. Right now A4 calculates the
sum of the values in A1, A2 and A3. What if we want B4 to calculate the sum of
the values in B1, B2 and B3, and C4 to calculate the sum of the values in C1,
C2 and C3, and so on till column E. Enter the values that we want to sum in B1,
B2, B3, C1, C2, etc.
Since the
formula in A4 uses relative cell references, you can just select that cell, and
drag from the bottom right hand corner to the right. This is called filling.
What youÕll
notice is that B4 now contains the formula =B1+B2+B3.
Question: How can you view the formula in a
cell?
An
alternative to dragging is to copy A4 and paste it in C4. This is also filling.
You are
just starting college and you have calculated your expenses for your freshmen
year as described below. You would now like to project what your expenses will
be for each category for the next three years assuming that there will be a 6%
increase in all expenses.
Category Expenses
Clothes
540
Entertainment
725
Miscellaneous
355
Room & Board 3480
Tuition & Books 5150
How would
you proceed?
Assume that
you now want to find out what your total expenses for each year will be. What
would you need to add to your worksheet?
Now suppose
that you would like to see what your expenses will be if the increase was 7% or
8%. What changes would we have to make to the spreadsheet (SS)?
Excel provides some advanced formatting capabilities to make your spreadsheet look more professional. The one that you should start using now is the cell formatting option. You get to this option by selecting the cell or cells you want to format and going through the format menu and clicking on cell then selecting the number tab. From here you can choose the type of number in your cell. The ones you are interested in now are the currency and percentage options.
From now on, any numbers that you have in your worksheet should be formatted appropriately.
What is the
difference between a relative and absolute reference again?
Absolute
references are specified using a $, so for example, a cell reference of $A$1
references cell A1 and any subsequent copy of the cell reference into another
cell still produces $A$1.
It is also
possible to make just the row or column reference absolute as follows: $A1 or
A$1.
Question: How do these two references differ
from $A$1?
Modify the
college expenses SS so that the user just has to change the yearly percent
increase and the rest of the SS is updated with the proper values.
Excel has
several error messages that you should be aware of and the main ones are:
-
# - A
##### error value occurs when the cell contains a number, date, or time that is
wider than the cell or when the cell contains a date and/or time formula that
produces a negative result.
-
#DIV/0!
- The #DIV/0! error value occurs when a formula divides by 0 (zero).
-
#na -
No information is available for the calculation you want to perform.
-
#NAME?
- The #NAME? error value occurs when Microsoft Excel doesn't recognize text in
a formula.
-
#NULL!
- The #NULL! error value occurs when you specify an intersection of two areas
that do not intersect.
-
#REF!
- The #REF! error value occurs when a cell reference is not valid.
-
#VALUE!
- The #VALUE! error value occurs when the wrong type of argument or operand is
used, or if the Formula AutoCorrect feature cannot correct the formula.
A
meteorology class found the average weekly temperature for each week of each
month for one year. The data follows. You are to find each of the following
using a SS:
1. The average monthly temperature for
each month.
2. The highest and lowest monthly
averages using two functions we have not discussed yet: maximum and minimum.
See if you can use the help feature to figure this out.
jan 33 36 29 31
feb 37 32 39 38
mar 43 47 38 45
apr 49 51 53 50
may 52 55 54 58
jun 56 62 61 60
jul 65 69 73 70
aug 72 74 68 69
sep 67 66 64 60
oct 63 65 60 59
nov 46 42 45 40
dec 38 35 36 35