Welcome to Excel
basics video number 16. And this video we got
to talk about mixed cell references, and formulas,
and functions, to save time. Now here's the thing,
this is a budget with 12 months, and
13 different expenses. That means there's 156
different formulas here. Now imagine if we had to
create every single one of those formulas individually,
or if like in this class so far, we only knew relative
and mix cell references, we'd have to create 12
different individual formulas. That would take a lot of time. But when we learn
mixed cell references, we will be able
to create this one formula, in the top left
cell, and then copy it down, and over. All 156 formulas,
with a single formula. And we're going to see a
bunch of amazing examples, multiplication table a
pension, or retirement example, a payroll example. All 5 examples, will involve
using mix cell references, to save time. Now I want to go over to the
sheet multiplication table, and this is our first example. We're going to see how to create
1 formula, and copy it down, and over to 144 cells. And in every single
intersecting cell, it will know to get the correct
row, header, and column header. Over here, it will
know to get 9 times 9. Right here, it will
know to get 5 times 12. That is going to be
a big time saver. Now this example is
not a business example, if we go over to the
sheet budget answer. Now this is a real world
accounting, budgeting example, we have 12 months, and
13 different expenses for the income statement. And this one formula
right here, add 2. We'll be able to create this 1
formula copy it down, and over. Now, this is set up exactly
like our multiplication table. If I hit F2 right here,
notice every single cell in this whole table,
will always have to look at the revenue
at the top of the column, and the expense percentage
at the front of the row. Every intersecting
cell is the same, so when I copy this
to the side, it's always looking up to the
top to get the revenue, and over to the side, to
get our expense percentage. Now this is a business
example, we'll do this next. But when learning
mix cell references, I like to start with this
multiplication table, because it's nice and simple,
one formula copy it down, and over. And that one formula, will
have to be the same formula in every single
cell, if we're going to put one formula there, and
copy it to the entire range. Now the pattern that each
formula will share is this, the formula will always have to
look to the top of the column to get the number,
and we'll have to multiply it by the number
at the front of the row. I hit enter and come over to
any other cell, the pattern's the same, equals go to the top
of the column, get that number, and multiply it by the number
at the front of the row. Now I'm going to hit
enter, delete all of this. Now before we create our
mixed cell reference formula, I want to show you how we would
have to create this table here, if we only knew relative an
absolute cell references. And, here's the sad
news, the vast majority of Excel users on
the planet Earth would have to do it using only
relative, and absolute cell references. So I'm ready to play
a little funny music, and do this in high speed. That would take much too
long, 12 different formulas, each one created
individually, and we had to copy them all down. So rather than do
all of that, we're going to create one formula,
and copy it down, and over. So this will be the
first time, we've seen mixed cell references. Now before we create our
formula, let's look at a cell reference, I want to say equals,
and just click on before. I want to actually copy
this down, and to the site, and notice what happens
to the cell reference. We want to notice what
is moving, or changing, as I copy down, and
as I copy to the side. Right so, I'm going
to Control-Enter, remembering that
there's a 4 there. Control-Enter, and I'm going
to copy it down 2 cells, go to the second
cell and hit F2. Oh, B4 changed to B5. So it looks like, as I'm
copying a formula down, the rows change. Enter-F2, sure enough
B stays the same. But as I copy down across
the rows, the numbers change, 4-5-6. Now, let's go back
up to this one, and copy it to the side 3 cells. In the first cell F2, notice
it says before tab-F2. Oh, what's changing? The column reference, that means
as we copy across the columns, the letter is changing. The column reference changed
from B to C. Tab-F2 to it change from C, to D. All right, that's going
to help us decipher, or figure out what type of
mix cell reference we want. I'm going to delete all this. Now I'm in cell
B5, and I'm going to create my base
formula, equals up arrow times, left arrow. Notice for that one cell,
that would work, 1 times 1. Now the trick to
mix cell references is, when you have
a cell reference, I need to ask 2 questions of it. And then once I answer
those questions, I go onto the next
cell reference, and ask the same 2 questions. Now why two questions? Well because, when
you're copying a formula, you could only copy it
in 2 directions, down across the rows, or to the
side across the columns. All right, I'm going to pick one
of the cell references first. Make sure your cursor
is touching B4, and now I'm going
to ask the question. For B4, when I copy the formula
down, what do I want it to do? Do I want it locked
on before, or do I want it to move relatively? Well of course, every
cell in this column has to be locked on B4. Now think about this, what is
going to move as I copy down? Well of course the 4 is
going to change to 5 and 6, and I want to stop
it from moving. So guess what? I need dollar sign
in front of the 4. If I hit the F4 key as we've
learned in this class so far, that puts in 2 dollar
signs, but that is not a mixed cell reference,
that's called an absolute cell reference. But watch this, we can hit
the F4 key a second time wow, it's putting the dollar sign
just in front of a 4, just in front of the row reference. Now let's just see what the F4
key does, if we hit it again. Oh, it puts dollar sign in front
of only the column reference B. Hit F4 again, wow it cycled back
to a relative cell reference. Now I'm going to hit
F4 a bunch of times, and I want you to notice, it's
just like a merry go round. It is cycling through, each one
of the different types of cell references, relative cell
reference, absolute cell reference, mixed cell reference
with only the row locked, mixed cell reference with
only the column locked. Now, I'm going to
hit F4 1, 2, 3 times, and stop when the dollar
sign is in front of the 4. That dollar sign will stop
the 4, from going to 5, 6, and so on. All right so as I copy down. It's locked on before,
and that will work. Now I need to ask the
second question of before. When I copy the formula, across
the columns, to the side, do I want it to move
relatively, or do I want it locked on cell B4? I want it relative. Notice B4, there
it is right there. But if I copy the formula to
the next cell, I really want C4, so I'm not going to put a
dollar sign in front of the B. If I do not put a
dollar sign there, then it's allowed
to move to C, D, and so on, as we copy
it across the columns. All right, so we answered
both questions for B4. Now we put our cursor in A5,
and I'm going asked the same 2 questions. As I copy the formula down,
what do I want A5 to do? Do I want it to move relatively,
or do I want it locked? Well of course, for this
one notice down, down. I always want the formula
looking 1 cell to the left. Every single formula
in this column has to have A5 change, as
a relative cell reference, as I copy down. So I'm definitely not putting a
dollar sign in front of the 5. All right, the second
question about A5, what do I want A5 to do
when I copy to the side, across the columns? Well, think about
this, what would change if I copy this right now? A5 would move to B5, and
that's not what I want. I want it locked on A5. So now I'm going to hit the F4
key, 1, 2, 3 times, and that's what I want. I do not want a dollar
sign in front of the 5, I want it to move
relatively as I copy down. But as I copy to
the side, I need to stop that A from moving
to B and C. And there you go, we answered both
questions, for A5 and B4. Now let's try it, Control-Enter. And I'm going to copy
it to the whole range, but you can't click
and drag diagonally. You have to either
copy it down, let go, and then copy it to the
side, or copy it to the side, let go, and copy it down. I'm going to double click, and
send it down, and then come down to the bottom, and with
my cross-hair or angry rabbit, click and drag. Immediately come to the
diagonally furthest one away, and hit the F2 key. That is absolutely amazing, it
totally got the column header, and totally got the row header. Escape any cell
that you pick, F2. It got it exactly right,
it followed the pattern, it's totally getting column
entered times, row header. Escape back up here,
F2, that is a mixed cell reference with only
the row locked, that's a mix cell reference with
only the column locked. Now before we move on
to our next example, I want to delete all of these,
and show you 2 more things. Now we just created our formula
using the 2 question method. The other method is called,
the sledgehammer method. I'm going to create the
formula, Control-Enter, and this is the
sledgehammer method. I'm in a copy it down
1, click in the cell F2, and I'm looking to
see what changed, that wasn't supposed to change. Well of course B4 changed to B5. So the 5 changed, so
I'm going to escape, come up to the top cell F2,
and I'm putting a dollar sign in front of the thing that
changed, because I don't want the 4 to change to a 5. So I'm going to
hit F4, 1, 2 times. Now I'm in Control-Enter,
and copy it down 1 cell, come to the second cell, F2. Right there, it's
totally correct, the blue one and the red one,
column header, and row header. So it's working when we go down. Now escape, let's
come back up here F2. A5, I'm going to copy
this to the side, escape, and drag it one over. This is the sledgehammer
method, so I'm going to put the second
cell in edit mode, in F2. I'm looking, and seeing that
the A5, moves to B5, so it's the A, that went
to B. So I'm going to escape, go back
to the first cell, F2 and put a dollar sign in
front of the thing that was moving, that I want
to stop from moving. So I hit the F4, 1, 2, 3 times. Now I'm going to
the Control-Enter, copy it down because
I changed it right. Hit F2, that's working, escape,
copy it to the side, and F2, and you gotta be kidding,
the sledgehammer method. I just kept looking
to see what changed, always going back to
the original cell, and putting a dollar signs
in front of the things that were changing, that I
didn't want to change, and I have the formula. Now I'm going to copy this
down, and then to the side. Go to the last cell
F2, absolutely amazing. One other amazing
trick, and watch this, I am allowed to highlight
a bunch of cells, that are all going to
get the same thing, I can highlight in
advance all the cells. And notice, the active
cell is the light colored cell, equal sign, up
arrow, hit the F4 once, and twice, times left arrow. I'm going to hit the F4 key 1,
2, 3 times, and look at that. There's the correct
formula that I need to populate,
into all the cells. So I can Control-Enter to put
whatever in the active cell, into all the cells. Now, I can go to the diagonally
furthest one away, and hit F2 and that is working. All right let's go look at some
business examples of where we can use mixed cell references. Let's go to video 2. Now in video 2 we
did a budget, we had a cell that pointed
to the first revenue for the first
period, then we had a formula that
calculated incrementally an increase in revenue, and
then F2 we calculated expenses. Now this whole row
represents all the expenses. So 85% of every dollar
that's into the business, is used up for expenses. But that's not a very
realistic example. Let's go over to
the sheet budget, this is a much more
common example. We don't have one expense, we
have 13 different expenses. And the accountants
already figured out for each one of our categories
for expenses, what's the percentage of revenue is. So for example,
for cost of goods sold, for every $1 into the cash
register 37 and 1/2 pennies is used up in cost of
goods sold, 13 and 1/2 for salary and
wages, one penny goes to repairs and maintenance. So all of these
percentages, we need to use to calculate the
budgeted, or estimated expense for each one of our months. And, since there's 12
months, and 13 expenses, that's 156 formulas,
that were going to be able to create
a single formula, copy it down, and
over, and we're done. Now let's go ahead, we
have a start revenue, and a assumed revenue
increase for each month. We're going to click in the
first cell for January revenue, E5 equals, and when I click
on the cell and the assumption area, that has start
revenue for January, tab. Now I actually color coded
each one of the formula cells, with a slightly
different formula, just so when we're learning,
we know a single formula goes there, these are all the
same formulas, same, same, and so on. All right February, now we
did this in video number 2 and this is video number 16. In video number 2 we had 1 plus
the assumed percentage revenue increase. But here we don't
have it, so we're going to actually do the 1
plus the percentage increase, in our actual formula. Now the pattern here, is
for every single month. We're going to look
to the previous month, and increase it by 1.5%. So our formula for February
is this, equals left arrow, that's a relative cell
reference that will always look to the previous period,
for last month's revenue, times and I want the complete original
amount from the previous month. So I put 1 plus, the assumed
revenue increase, which is revenue increase over there. Now, I'm only copying this to
the side, and I need B5 locked. I absolutely could hit
F4, that would work. But I want you to
challenge yourself, every single time you
copy your formula, and it's not a relative
cell reference, I want you to put the minimum
number of dollar signs. Now, I'm copying this to the
side, across the columns. So I only need to stop
the B from moving. So I'm going to hit
the F4, 1 and 2 times. That formula will work, whatever
the previous month's revenue, times 1 plus the assumed revenue
increase, , close parentheses, Control-enter, copy
it to the side. Go to the last
cell and hit F2, I can already see,
because I didn't add any number formatting, that
I have extraneous decimals. We should have
already guessed, I don't know how many times this
in the class so far, 5, or 6, or 7 times. Any time you have
extraneous decimals and you're multiplying,
you're going to run into extraneous decimals. We're dealing with
money, so that 2/10 of a penny right
there, 8/10 of a penny, we got to get rid of it, or
we have to properly round. This will be 82,
this will be 39. And the reason why, is
we're dealing with money, and guess what,
we're going to use every single one
of these formulas in a subsequent formula
over here, which is adding. Some I'm going to come back
to the original cell, F2 and I'm using the
round function. I see it in my blue
dropdown, so I hit Tab with my cursor I click at the
end, comma, number of digits. By now we know that 2 means
we're rounding to the penny, closed parentheses,
Control-enter, copy it to the side. Absolutely beautiful. We can add currency or
accounting if we want, I'm not going to. Click in the total cell
Alt-equals, then look at that, it got it right. Now I hit Enter,
and the sum function is not adding any extraneous
pennies, because we rounded. Now let's come to our expenses,
every single one of these cells gets the same formula. I'm going to click on
the top cell, equals, and our base formula is
this month's revenue, times the expense
percentage for this row. Now, I'm going to do
the 2 question method, I'm clicking my cursor in E5,
as I copy down across the rows, do I want it locked
on January, or do I want it to move relatively? I want it locked. I'm copying across the
rows, so that reminds me that I need to stop
the 5 from moving. So I'm going to hit the
F4 key once, and twice. Now the second question about
E5, when I copy it to the side. Do I want it locked
on January, or do I want the blue box to move to
each one of month's revenues? I want it to move, I want E to
move to F, and G, and so on. So I'm done with E5,
now I click in B7. When I copy down to
salaries, and rent, do I want that orange one to be
locked on cost of goods sold, or do I want it to move to
salaries, and then rent, and so on? I want it to move as a relative
cell reference, as I copy down. I want B7 to move to
B8, and B9, and so on. Now the second question, when
I copy to the side to February, and March, I'm copying
across the columns. I'm going to E, F, G and so on. Do I want it locked on B7? You bet I do. So since I'm copying
it across the columns, I need to stop
that B from moving. So I hit the F4 key, 1, 2,
3 times, dollar signs only in front of the
B, and not the 7. Now I'm going to
Control-Enter, copy it down. I do not want a
double click there, because I have other
formulas that go below. Notice I had to stop and let go. By the way controls,
you cannot do this, no matter how hard you
try to drag it diagonally, it's never going to work,
it's a 2-step process. So I let go, then I
re-grab the fill handle with my angry Robin,
and click and drag. Now I'm going to click on
the last cell and F2, that is absolutely amazing. Now tons of extraneous
decimals, we absolutely need to round and watch this. I'm going to highlight
the entire range, active cell at the top. I'm hitting F2, now in our
multiplication table example, I showed you how to create a
formula in the active cell, and then use Control-Enter
to populate it through the whole
range, but here we're going to edit it and
then populate it, throughout the whole range. So round tab, i-beam at the
end, comma 2 closed parentheses. I'm Going to use
Control-Enter to populate it throughout the entire
highlighted range, Control-Enter,
that is beautiful. Last cell, F2, it's still got
the right cell references. That's 156 formulas
that we created, with just 1 individual formula. All right now we come
to total expenses, Alt-equals it guessed
right, so I hit enter. Net income equals,
and then I click on total revenue for January,
minus total expenses, Control-Enter. Now these are 2
different formulas, I'm going to highlight
them both, point to my fill handle, and when I
see my Angry Rabbit, I'm going to click and drag. Go to the last cell F2,
that's looking good. Enter F2, that's also
looking good, escape. Now here's a great
trick, I'm going to highlight the entire
range in our keyboard, for the sum function
in the active cell. Alt-equals, whoa look at that. If you highlight a bunch of
cells either next to a data set, or below, and
use Alt-equals, it will assume we want to
highlight the whole row. Now I get nervous
anytime I do that, so I do that in the Alt-equal,
but I immediately hit F2, just because when
we highlighted Alt-equals, it didn't give us the chance
to verify, so I hit F2 and I'm verifying, I hit Enter. Now if we want, we
can come and highlight using and my control trick. Control 1, and I can use
whatever number formatting I want, I'm going to use
currency, click OK. And that is amazing,
we can come, and if our assumed
revenue is actually 4,500, as soon as I change
that, and hit Enter, everything will update. Rent was not 0.75,
it was actually 5, so when I changed that instantly
that row, total expenses net income changes. Now I'm going to control zz. That is a beautiful
budget example, we created it quickly,
with mixed cell references. Now we need to talk about
these assumptions tables. So I'm going to go over to the
budget and assumption table. Now here's a little
mini-version, I've already created
all of the revenue, total expenses net income. All I want to do is
think about, when we can use mixed cell references. And here's the rule,
it's very simple. Notice the labels here
are listed vertically, so when I come here, and try
to use mixed cell references, my assumption table
has to be oriented the same direction,
vertical, vertical. So the assumption table has
to have the labels and numbers listed vertically, and
the formula table also has to have the labels,
and then our formulas will be vertical also. This will not work, you cannot
have horizontal and vertical. Now the problem is, textbooks
that you get in school, and assignments you
get in your job, the accounting
department sometimes doesn't know about
mixed cell references. So they'll send you
over a table that has vertical labels, and
horizontal assumptions, and you have to
go ask them, can I please change it from a
horizontal to a vertical, so I can use mixed
cell references? Now one easy way to do this,
is if you copy this Control C. If I come over to
the side and Control V, this control smart tag
has an awesome option. I can click the
little down arrow, and say please
transpose, and just like that, I turned an
assumption table that will not allow mixed cell references,
to one that will. This is vertical,
this is vertical, so this would work
with this table. Now this table is
vertical, we could use a horizontal
assumption table if these labels were
horizontal up here. That's why right here it says,
either horizontal, horizontal works, vertical
or vertical works. But if you have one
that's horizontal and one that's vertical,
it will not work, you cannot use mixed
cell references. All right, so let's
create our formula. If I highlight, and
we'll use our trick, we'll create our formula in the
active cell, equals round tab, up arrow and I'm
asking the 2 questions. When I copy B5 down
do I need it locked? Yes I do, so I am going to hit
the F4 key once, and twice. When I copy the F5
to the side, do I want it to move to February? Yes I do, So no dollar sign in
front of the B, times and I'm clicking on the assumption
table with the vertical labels and vertical numbers. Now I'm asking the
question when I copy down, from cost of goods
sold to operating, do I want this to move to
operating an administrative? Yes I do, so I don't want
to lock it when I go down. When I copy it to the
side, do I want it to move relatively, which
means the orange cell would move to the empty cell, and
then the cell with text? No I don't, I need
it locked since I'm copying across the columns. I need to stop
that B from moving, so I hit the F4
key 1, 2, 3 times. Now that will work,
2 closed parentheses, I'm going to
populate this formula into the highlighted
range using Control-Enter. Here's a useless keyboard
if you use Control-Period. Control-Period
will always toggle between the corners of
the highlighted range, so I could control
period-period and F2 to verify that the cell references are in
the correct location, Escape. All right, let's go look
at another great example. We want to go to
the sheet pension. And in this business example,
we have a pension, or retirement table here that we
want to fill out, and we're assuming
that we will deposit $3,000 into some
investment vehicle, whether it's bonds,
or mutual funds, or stocks, or pension,
or an annuity, but we're putting $3,000
in at the end of each year. Well since we want to see
what will happen at 3%, 4%, 5% assumed return for
each year, and see what happens if we save over
longer, and longer periods of time. I need the same calculation in
every cell, and look at this, it's always going to need
to know for this column 5%, and for this row the 35 years. When I come down here,
this needs 6 and 40, so it's like our
multiplication table, or our budget, every
intersecting cell has the input at the
top of the column, and the head of the row. Now this is a fancy
finance formula, and in my finance
class, we get to talk about the math behind it. But luckily, Excel has this
great function called FV, and FV means future value. Future value is similar
to the PMT function we studied a few videos ago,
to calculate a loan payment. Future value has similar
arguments to PMT, we need to put in a period rate,
the total number of periods, and the amount of the
payment each period. And just like our
PMT function, we're going to ignore the
last 2 arguments. Any time you see an
argument in square brackets, just like we saw in PMT
and in the look-up, , if you know what the default
is, you can leave those out, and we do not need those. So all we have to do is put
in the first 3 arguments. Now rate, that's
the period rate, we're annual so we click on
that top cell right there. Now we're not going to worry
about the cell references yet, comma total number
of periods, well that's going to be a number of
years, comma payment, that's the amount you put at
the end of each year, and that's our 3,000. Now if we had an amount in
the bank at the beginning, that's what we put
in for present value. If we leave that
out, it assumes zero. And type, just as we talked
about with the PMT function, type is either at the
beginning of the period or the end of the period. Most consumer loans and
investment vehicles, assume that you put the
amount in at the end, and that's the
default. So we don't have to put either one
of those arguments in, closed parentheses. Now I'm at a one by one click
in each cell reference and ask, what we wanted to do
throughout the copy action? Now as I copy this
formula down, I'm going to move this
out of the way. As I copy this formula down,
what do I want B6 to do? Well, as I copy down, I
need it locked on that 3%. So since I'm copying
down across the numbers, I'm going to hit the F4 key,
once and twice, lock in the 6, that six will not
move to 7, 8, 9. Now B6, when I copy to the
side across the columns, do I want B6 which is that 3%,
to move to C6 which is the 4%? Yes I do, so no dollar sign
in front of the B. Now A7, that's the number of years, as
I copy down across the rows, do I want the
orange one to move? Yes I do, I want it to
move as I copy down, as a relative cell reference. So no dollar sign in
front of the number. Now A7, as I copy
across the columns, do I need every single
formula locked on the 10? Yes I do, so I need to
hit the F4 key 1, 2, 3 times, to stop the A
from moving to B and C, as I copy it across the columns. Now the third cell reference,
this is the payment up here. What do I want to
do is I copy down? Well I need it locked. What do I need to do as
I copy it to the side? Well I need it locked. Every cell for future
value needs the 3,000, so I simply hit the
F4 key one time, and there it is, that's our
first formula where we've seen mixed cell reference with
the row locked, mixed cell reference with the
column locked, absolute. Now we can Control-Enter,
I'm copying it to the side, and then double clicking,
and send it down. I'm going to the diagonally
furthest one away, and click or use
Control-Period-Period. I see that it's highlighted
F2, and look at that, totally got the column header, the row
header, and the locked 3,000. All right, that was a
great pension example using mixed cell references. Now we have our last
example, payroll. I'm going to go over
to the payroll sheet. We have names, January
sales, we've already calculated commissions, there's
our base salary, and gross pay. We added those 2 up, now
we need FICA deduction, Medicare deduction, and federal
withholding's deduction. Down here I have a
horizontal assumption table, our labor is all horizontal,
so we're going to use this one. On the test, I will
try to trick you. I will give you
both, and you have to figure out which one to use. We need gross pay,
times our FICA, and then we need to
copy it down, and over. So in the top cell,
Equals Round, now we have extraneous decimals,
we are dealing with money, and we're using this
formula result in totals down here, and off to the side. All right, there's the gross
pay, times the FICA deduction. Now for each cell reference,
I need to ask 2 questions. When I copy E5 down,
do I want it locked or do I want it relative? As I copy this one down
it's got to move relatively, so I'm not putting a dollar
sign in front of the 5. As I copy to the side, to
Medicare, and then to federal, do I want it locked on E5? Yes I do, I need to
stop the E from moving, as I copy across the column. So you hit the F4
key 1, 2, 3 times. Now I go to the next
cell reference, F14. As I copy down, do I want
that cell reference to move? No I don't, I want
it locked on 14. So I have to hit the
F4 key once, and twice. The 14 is now stopped, it will
not move to 15 as I copy down. Now, as I copy to the side
from FICA to Medicare, do I want the orange one to
go from FICA to Medicare? Yes I do, so no dollar
sign in front of the F. Now i-beam cursor at the
end comma, 2 for Penny, closed parenthesis,
Control-Enter, copy it down, and copy it to the side. Go to the diagonally furthest
one away, and hit F2. I'm verifying that it has the
correct gross pay, and tax rate, and it does. Now here's the cool thing
about mixed cell references, and I happened to set up
my table horizontally, and I put the assumption
table directly below. And now, the boss comes in
and you've created this table, and the boss says oh I forgot
2% pension withholding, and you say oh, that's
no problem boss. And the boss says, take
the rest of the afternoon off, and fix the table, and make
all the calculations correct. But you look over your
shoulder, and you just say, oh you mean like this boss? And you come up
to the age column, right click-Insert
to insert a column. Notice it automatically
pushed over the formulas, and the federal
withholding, and you said, oh you wanted pension right? Enter, and you said it was 2%,
so I hit Enter, it's got 2% and now you simply sense
that formula, is set up for mix cell reference. And this formula already
has a sum, you simply highlight the column,
and copy it over. You can even check
it if you want, it's totally working perfect,
these formulas over here are totally working perfect. This one right here
totally working perfect. You only need to make sure
that it says pension here, so you were tricky. F2, you even got the
labels from up here down in the assumption
table, so now you're just going to copy that over. That is the power of
mixed cell references. Now I got to tell you 2 stories. When I worked for
Broderick consulting, something very similar
to this happened. I was working on a template
that I had amended, so it was efficient, and the
boss did walk in and said hey, can you make these improvements? And I literally looked over my
shoulder, inserted a column, added a couple month labels,
copied the formulas over, and in 5 seconds I had
updated the template. And you can imagine
how happy the boss was. Not only that but we've
seen the round function, what five, six, seven, eight,
nine times in this class. and it is one of those things
that most people don't do. And what that means is so many
spreadsheets are incorrect. Now they may be only
off by a few pennies, but that matters,
and we've got to go look at YouTube, at our
class video number 9. So here's our class video number
9, about number formatting and round and look
at this, I want to zoom in and look
at this comment. This You-Tuber
said, here's a story a teeny little
recent glory at work. It happened a few
times in the past that the accounting
sheets I received daily from the billing guys, were a
penny or 2 off, here and there. And since I have to receipt the
amounts in our billing system, I had trouble
finding the missing and/or overpaid pennies. Now here comes this video,
I have watched yesterday. Straight this morning
checked all the sheets for rounding errors,
and sure as sunrise, there was the solution
with a number formatting. Showed off to the
boss, who could not thank me enough for the insight,
and I had the delightful task, to teach the billing
guys the round function. Now this is just
one anecdote, and I tell you anecdotes about
my work experience, but this could be happening
thousands of times, every day. And guess what? In your job from
now on, you can be the one, F2 to build
the efficient template, and always remember, in our
case to use mix cell references, and use that round function. All right, there are some
homework problems over here for you to practice. And in this video
we definitely saw how to use mixed cell
references in a payroll example. Over on pension, we saw how
to use mixed cell references, and in a finance,
or pension example, we talked about how
setting up your assumption table with the correct
orientation is required, if you want to use
mixed cell references. Over on budget,
we saw a situation where we had 13
expenses in 12 months, and we mixed cell references to
quickly create those formulas. And we started it off
with the multiplication table, a great trick
for teaching yourself, or if you forget. Six months from
now you have a job, and you forget how to do
mixed cell references, just whip out a
multiplication table, remind yourself how those
mixed cell references work, and you're good to go. All right, if you
liked that video, be sure to click that
thumbs up, leave a comment and so, because there's lots
more videos to come from Excel is fun. Next two videos,
Excel basic 17 and 18, we'll talk about some accounting
formatting, and defined names. All right, we'll
see you next video.
