Welcome to Excel and Finance video number 6.
Hey, if you want to download this workbook and follow along,
click on my YouTube channel, then click on my college
[INAUDIBLE] link, and you can download the workbook Finance
and Excel chapter 00.
If you're in the class, go to our class website.
Hey, we got to talk about cell references.
Hey, but wait a second.
I thought we already talked about cell references
way back here when we were talking
about formulas and functions.
Yeah, well we talked about them, but we only
talked about them when using a single formula
in a single cell.
In this video, we got to talk about cell references-- what
happens to them when you copy the formula?
Now, here's our deal.
We're going to have a lot of--
most of our calculations are going
to be single cell calculations in this class.
We're going to be doing big mathematical financial
calculations.
But sometimes we're going to have templates where
we have a formula, and we're going
to need to do the same formula over and over and over again.
So we want to avoid doing this.
Here's what we did in earlier videos.
We said if we want to calculate the net cash flow for the day,
we say cash in minus cash out.
We had more cash going out than coming in.
So we were down minus by 4,000 some odd dollars.
Now, we could come down here, and we could
do the same formula again--
in minus out, Enter, equals in minus out.
Now, by default, when you put a cell reference in,
all you see is the column reference and the row
reference.
OK.
I'm going to hit Enter.
Let's highlight this column and hit Delete.
And let's try this again.
Equals-- and I'm going to say cash in,
which is two cells to the left.
Now, from the point of view of the formula,
it's not looking at B7.
It's looking at two cells to my left,
minus cash out, which is one cell to my left.
Now this is the default behavior in Excel.
When you type in B7, if the formula is just
going to stay in one cell and never be copied, then it is B7.
But if you're going to copy it, that's not B7.
It's a relative.
It means relative to me, the formula, where am I looking?
One, two.
That's why when we copy this down, [INAUDIBLE],,
it'll always look two cells to the left minus one cell
to the left.
And I'll prove it you.
B7.
That is not B7.
Control Enter puts the formula in the cell
and keeps it highlighted.
Now we've done this so far a couple
of times in these videos.
Selection cursor, when I move the selection cursor
next to that little fill handle, boom.
I see the crosshair or Angry Rabbit.
I can click and drag all the way down.
Wow.
Let's just click right here and hit the F2 key.
Look at the-- this is called range finder, when the cell
references light up with colors, and then the cells themselves
have a rectangle around them.
That's called range finder, like a rainbow range finder.
We can see, sure enough, that is not B7.
Notice up here that was B7.
But when it came down here, it changed to B8.
That's because it's not really B8.
It's always going to look two to the left minus one to the left.
When I come down--
Enter, F2, Enter, F2, Enter, F2-- every single one
obeyed perfectly.
They're called relative cell references.
Now, a lot of times, that's perfect.
Let's go down and look at another example.
What happens here?
I want to say, I have these bunch of loans
and a bunch of amounts.
And we're going to get the same rate.
But we just want to figure out what amount we want.
What amount of interest can we afford in our monthly budget,
or annual if this is the case?
Equals this, one cell to my left, times C16.
Enter.
Equals one cell to the left times C16.
Notice this blue one is moving down each time,
but for whatever reason, this is--
we need to always look at C16.
So really equals times C16.
I do not want to have to type all these in.
So notice what I did.
I went one too far.
There is a few ways to edit this.
One way is to double click and click right there.
Another way is put it in edit mode,
and you can actually click on the side right here
with that-- that's called a move cursor,
when you see that four-way black arrow under the white arrow,
you can drag it.
All right.
Let's delete all of these and type it again.
Equals this times this.
Now, in this example up above, we copied it down.
Let's see what happens here.
The default behavior is relative cell reference.
So when I copy it, uh oh.
We got all sorts of trouble.
Let's just look right.
VALUE!
Means you have an incorrect calculation or formula element.
Let me just hit F2 and look here.
Well, VALUE!
Error means you can't take a number times a word.
And then here, I don't know what that's-- oh,
that's a huge number.
It's saying that one times that one.
But notice what happened.
Up here, it was C16.
But it's not really C16.
It's one, two cells above me, from me, the point
of view of the formula.
So when I came down one, notice it obeyed.
That cell reference is two above.
Enter, F2.
It's still two above.
So we don't want a relative cell reference.
I'm going to delete all that.
I'm going to click Delete.
And we're going to type our formula.
Equals one cell to my left, which I do
want as a relative cell reference, times--
and watch this.
When I click on C16, right now the default behavior
is relative cell reference.
But if you hit the F4 key, it puts the magic symbol in,
two dollar signs.
Now, in our first video, we talked about Excel.
There's only two directions, a column and in this case
it's a row reference.
So we'll put two dollar signs.
It means dollar sign in front of the column,
and dollar sign in front of the 16.
It is locked.
Now when we copy it down, it's going to remain on C16.
Now, there are other types of cell references.
But in this class, we're only going
to basically use these two types of cell references.
All right.
Let's Control Enter.
Control Enter puts the formula in the cell,
keeps the cell selected.
And then let's try it.
Now, I'm going to click.
I see my Angry Rabbit.
Click and drag.
And then I'm going to come down to the bottom and hit F2, edit.
And sure enough, that is awesome.
Did that save us a lot of time?
One cell to my left times locked our annual rate in C16.
That's called an absolute cell reference.
I always call it lock, because it's locked on that cell.
But absolute and relative cell references.
Those are the two types of cell references
we'll use in this class.
One other thing.
Totally important when you get to a situation like this.
Equals relative cell reference-- one to my left-- times C16,
and then hit the F4 key.
Control Enter.
Watch this.
Remember we clicked and dragged?
As long as there's stuff to the left,
if you double click the fill handle-- ready?
I'm going to double click.
That's the Angry Rabbit.
I'm going to double click it, and it automatically
sends it down.
And it goes, goes, goes, goes, until it sees a blank here,
and then it stops one cell above.
All right.
Relative, absolute.
Those are the two cell references
we'll see in this class.
All right.
We'll see you next trick.