Welcome to Office 2013 class video number 39.
OK, if you want to download this workbook
and this workbook is for videos 35 to 39, click on the link
below the video or go to our class website.
Hey, we got to talk about how to orientate formula
inputs to allow mixed cell references.
Now, in our last video, we learned about mixed cell
references for the first time.
But there are some situations where you can use mixed cell
references and some where you cannot.
Now our example here is like last video.
We're creating a net income projection.
So we have revenues, we have in our formula table
our expenses listed, and for each column,
we have to calculate the expenses.
Now the question is, if you're setting this up yourself,
you're in charge of this budget.
Here's your expenses listed vertically
in the formula table.
Do you set up your assumption table
with the expense labels listed horizontally or vertically?
If you want to use mixed cell references,
you have to have labels in the formula table
and labels in the assumption table parallel to each other.
Vertical, vertical.
If our expenses were listed horizontally,
then we would use this table.
Horizontal, horizontal, or vertical, vertical.
In either one of those cases, you
can use mixed cell references.
Now why do we even worry about this?
Well, what's the advantage of mixed cell references?
If we didn't know how to do mixed cell references,
we would have to create one, two, three, four, five, six.
That means type out six individual formulas.
If we know mixed cell references,
we simply create one, copy it down and then over.
All right.
Vertical, vertical.
And on the test, I will try and trick you.
I will give you both and you'll have to remember this rule.
All right, so here we go.
January revenue times the first expense percentage.
And now we either use the sledgehammer method
or the two question method.
I'm going to use the sledgehammer method here,
and then our next example, we'll use the two question method.
All right, so I'm totally confused,
I have no idea what to do.
But I do know that this whole column needs January revenue.
So watch, I'm going to look at the B6, Control Enter,
copy it down one, F2.
Well, the six turned into a seven.
So I need to stop that six from changing into a seven.
So I go back up to this F2.
And I have to put a dollar sign in front of the six
to stop it from moving.
F4, F4 twice.
All right, Control Enter, copy it down one.
I could copy it all the way down,
because if it's working in the cell below,
it will work all the way down.
So we're going to save space and just go back and forth in these
as we do our sledgehammer method.
All right, so B6 is working as we copy down.
Now, still talking about B6, let's copy this to the side.
F2.
Did B6 change to C6 like we wanted?
Yes it did.
So the blue cell reference is done.
Now I come back to the original cell
in the top left corner, F2.
B17, right, it's pointing at cost of good sold, 25.5%.
Control Enter.
Copy it down.
F2.
It's pointing towards other expense, other expense.
That 4.5%.
So the sledgehammer method here says yeah,
it's moving properly.
Now, let's copy it.
We still need to think about C17.
Well here, look at that.
Without even thinking about what changed
and what didn't, I can clearly see
that the orange cell is in the wrong place.
But do need to decide where that dollar sign goes.
So F2, B17 changed to C17.
I need to stop the B from changing to C.
So I'll go back, F2, and I've touched somewhere
and hit F4 key one, two, three times.
And there B locked but not the 17.
Control Enter.
I'm going to copy it down one, F2.
Everything's looking good.
Copy it over one, I got my fingers crossed, F2.
It's looking good.
Now I'm going to delete both of these.
Now I'm going to copy this down, click and drag.
And then it's a 2-step process if you're
using your angry rabbit.
going to re-grab it and click and drag.
Go to the diagonally furthest one away and F2.
That is absolutely amazing, look at that,
it got the depreciation 12%.
There's the depreciation line.
And it got the June revenue.
That's amazing.
And what did we do?
We saved a lot of time while creating this budget.
Now we want to think of something else here.
From earlier videos, F2, learned that if you're multiplying
or dividing decimals, and we are here, we have 25.5%
or underneath there is 0.255, if we're multiplying in this case
decimals and we're using that formula
result in a subsequent formula, we
need to use the round function.
I'm going to hit F2 and add the round to this.
I'm going to come to the end.
Remember, if you see your selection cursor,
you don't want to click.
It's only when you see your I-beam cursor that you click.
We're rounding to the penny here,
so comma 2 closed parentheses.
Control Enter.
I'm going to copy it down.
I'm not going to double click because I
don't want to replace all these other ones.
And then click and drag over.
Go to the diagonally furthest one away, F2.
Still looking good.
Now with that in mind, I want to go back
and look at our last video.
Cell references 4.
Notice here we were calculating with the future value
our pension.
We were never using these in any subsequent formulas,
so I don't care about rounding them,
I'm just looking at these formula results.
If I go over to CR3, here's another example.
Now notice I have this highlighted
all of the expense formulas.
So I'm going to do our trick with the active cell.
Anything F2 that's in the active cell I can populate all the
highlighted cells with whatever I
put in the cell using Control Enter.
I'm going to edit the formula.
Round.
This meets our definition.
We're multiplying and dividing decimals,
we're using the formula result in subsequent formulas,
so I want to make sure that this is rounded to the penny.
Money, right?
Here, edited formula, repopulate it
into all the highlighted cells by using Control Enter.
Go to the diagonally furthest one away,
F2, absolutely beautiful.
Over here we had number 2, CR2, integers not a problem.
CR1.
This example here, we actually never did anything
with these numbers except for look at them.
If we had a total at the bottom, you absolutely
would use the round function.
All right, we're going to go back to our AT sheets.
There we go.
We did a net income projection using mixed cell references
and round.
Now we want to go over to AT1.
Here's a different example.
We have payroll.
The name, we have January sales commissions base salary.
There's our gross pay.
But we need to calculate the FICA deduction, the Medicare
deduction, and the federal withholdings.
The question is, do we use this assumption table or this one?
It's easy.
You just look.
Our labels, are they orientated horizontally or vertically?
They're horizontal, so the assumption table
has to be horizontal.
So we can create our base formula
and then ask our two questions about the cell references.
Gross pay times the FICA deduction.
Now we ask two questions of each cell reference.
There's our gross pay.
Well, we're going to have to use gross pay for Medicare
deduction and for federal withholdings.
So that means when we're copying across the columns,
we need to have it locked.
So I'm going to hit the F4 key one, two, three times.
When I copy this down from Amy's gross pay to Paul's and then
Stevie's, I need it relative.
So no dollar sign in front of the number.
Now I click my cursor somewhere touching the next cell
reference and I ask the two questions.
When I copy it over to the side from FICA to Medicare deduction
and then federal withholdings, do I want this F12 to be locked
on 6.2% , or do I want it to move?
I want it to move.
So when I get to the Medicare, I need to be looking there.
When I get to federal withholdings,
I need to be looking there.
So relative moving across the columns.
Now when I copy it down from Amy to Paul to Stevie,
do I want this to move, or do I want it locked?
I want it locked.
So since I'm copying down across the numbers or the rows,
I lock the 12, F4, F4.
Only the 12.
Control Enter.
I can copy it down and I can copy it over.
I'm going to go to the diagonally furthest one away
and hit F2.
That is amazing.
It's got gross pay and it's got the federal withholdings.
Now this is a situation.
We are definitely multiplying or dividing decimals
and we're using each one of these formula
results in a subsequent calculation or formula.
So we have to use the round.
F2.
Round.
Comma 2 to the penny.
Control Enter.
Click and drag down, let go, re-grab it, and over.
Click on the last diagonally furthest cell away, F2.
That is amazing.
Now here's something else amazing about mixed cell
references and the fact that we have our labels
in the table and our labels down in the assumption table
not only horizontal, but they're directly lined up.
If your boss comes in and says, hey, we have a new deduction.
Take the next two hours off and build
a new template for our employee take home pay.
You kind of look over your shoulder your boss
and you say, oh you mean like this?
And you simply right click the H, point it to insert,
this will insert a whole new column.
You come down here and you say, oh
hey boss, what was the deduction you wanted?
It's going to be pension.
Enter.
What's the rate?
Oh, we're going to start at 2.
So you type a 2.
So you notice that it's pre-formatted, enter.
And then you come up here and you'll
highlight the entire column.
There's the sum.
Not only that, but look, our label at the top,
we didn't even retype these labels.
We were smart, we had a formula looking at them.
And then our lovely little mixed cell reference formula
that's looking at gross pay and the proper percentage.
I can simply highlight this entire column and point
to the fill handle and click and drag.
And just like that, you're kidding me, gross pay
and our new inserted pension percent.
Absolutely working perfect.
You can click in each one of these.
We even were smart, we didn't do plus plus plus,
we did the sum function, so when we inserted that column,
it updated perfectly.
So assumption tables, the orientation
of our formula inputs, very important.
In fact, mandatory that they're parallel
to the labels in the table if you
want to do mixed cell references.
All right, we'll see you next video.