Welcome to Office 2013 class video number 35.
If you want to download this workbook,
and this workbook is for videos 35 to 38, click on the link
below the video or go to our class website.
Hey, we're still studying Excel.
This is Excel basics number 17.
And we have a great video here, because we're
going to see how to search for Excel function.
And we have two examples.
We're going to learn about the PMT
function and the median function.
Now, here's the deal.
Excel is supposed to make our lives easier, right?
So our first example.
This is a finance example.
And our goal is to calculate the monthly payment.
Here's the price of the car, here's the down payment,
here's the quoted annual interest rate
and the loan is going to be for five years.
Now before we go about the complicated task of calculating
this monthly payment, we actually
have to do some precalculations.
So for example, that's the price for the car
if there's a down payment of $10,000,
we have to figure out how much the loan is.
So we'll do a formula.
Equals, and I'm going to get the price
of the car minus the down payment.
Control Enter.
$24,799.
And the nice thing about building a template
like this, of course, if we change this to $5,000,
our loan amount instantly updates.
Control Z. The next calculation is, well,
they give us the annual rate, but if we're
calculating the monthly payment, we
need to calculate the monthly interest rate.
Now, if this is for the whole year
and there's 12 months in the year, then of course,
we need to chop that into 12 pieces.
So equals 4.8% divided by 12.
Now that's an example of a number
that's not going to change.
12 months in a year is not going to change,
so we can simply type it into our formula.
Enter.
So 0.4% will be our monthly interest rate.
Now next we need total months.
Anytime you're calculating a loan payment,
whatever the period, this happens to be months,
you have to figure out how many total periods.
Well OK, so five years, 12 months in each year, so
equals 5 times 12.
That number can change in our template,
that number's not going to change.
So we simply use a number there, use a cell reference
for the years.
If we were to change this to three,
instantly, it would update.
Control Z.
Now we have all of our formula inputs
to calculate a loan payment.
Now here's where the beauty of searching for a function
comes in.
This is the formula.
I'm in B13 F2.
That's the formula to calculate a loan payment.
Now this is a famous finance function for an annuity.
But even if you knew this function
and you were a finance person, there's a much easier way.
So here's the deal.
We click in the cell where we want the function to go
and we can search.
Now there is a keyboard shortcut, Shift F3,
I don't usually use that one, but that
will open the Insert function dialog box,
right next to the formula bar here insert function.
I can click on that.
That's called the f of x.
That's from algebra.
F of x button.
And there it is, Insert function.
Now I can type something and then click Go.
So I'm going to type how about monthly loan payment.
And then hit Enter or click Go.
And so it'll give you a list of functions
and sometimes you have to click through each one
and read the description.
So the first one, PV, returns the present value
of an investment.
Well that doesn't sound like it.
PMT calculates the payment for a loan based
on constant payments and a constant interest rate.
Hey, that sounds like it.
So you click through after you search
when you find the one you want, you click OK.
Now this function's argument dialogue box
is great because there are sometimes
like with this function many arguments to enter.
You can click through each one and it
will have a description of what should go there.
So let's read it.
Rate.
Is the interest rate per period for the loan, for example,
use 6% divided by 4 for quarterly payments
at 6% annual percentage rate.
So sometimes, it gives you pretty good hints.
Now, we already did our division.
We were smart, right?
So the rate.
Now this is a text box.
That's the collapse button.
Any time you see the collapse button,
it means you can connect the text box to the cells.
Now, I'm going to drag it out of the way here.
With the rate, I'm going to click on monthly, not annual.
The banker would love it if you calculated the monthly rate
with the annual rate, because then it would be huge.
Use monthly rate.
The rule of thumb for a function like this and this
is one of the many finance functions,
is all of the arguments have to be in the same period.
So we're doing month, so I'm going to use the monthly rate.
NPER is the total number of payments for the loan.
Oh OK, so we already calculated that.
Number periods.
That's what that stands for.
So I'm going to click in that text box.
Not 5, it's total number of periods.
Remember, we're doing monthly payment for our loan,
so everything has to be in months.
Now, PV is the present value, the total amount
of a series of future payments is worth now.
Now, that's finance speak.
You got to just think of present value
as hey, if I go borrow $24,799 today,
what's the present value of it?
It just means how much is it worth.
Well, $24,000.
Now so I'm going to click.
Now notice over here you can see the unformatted numbers.
So all of these text boxes have cell references
and it's displaying the unformatted numbers over here.
Down here, it's showing me the result.
There's also a result over here.
Now I click into this next one and this next one,
these ones are not bold.
You can see bold, bold, bold, these ones are not bold.
That means that they're optional.
And for us, we don't need to worry about these.
If you were a finance person, you
would have to worry about those.
All right, so you ready?
Here's the magic, I'm going to click OK.
Oh, that is amazing.
Now that looks like number format.
We talked about that before.
That's either accounting or currency.
Oh no, we know exactly which one it
is, it's currency because the dollar
sign isn't fixed on the outside.
Now that is red and it's got parentheses,
so I'm going to use the keyboard Control 1
to open up the Format Cells.
Go to number, and in currency, I'm
going to select negative numbers.
Click OK.
Now, this one down here doesn't have any formatting,
so it shows the number and all of its decimals.
But why is that negative?
If I click in that cell and hit F2,
why is the PMT spitting out a negative?
Here's the amazing thing.
These finance functions, and this is just one of them,
they understand cash flow.
If you're buying the car, every month you're
paying in the $465.72, you're actually
having this money come out of your wallet.
Now, if you take a finance class,
you'll learn all about cash flows,
but that's another example how the built in functions really
help you.
It totally understands cash flow that's important for finance.
Now one other thing.
Since this shows all the decimals here,
I bet you anything if we increase the decimals
we'll see these here also.
And of course, we do.
The formula down here, math formula, and the PMT function
are calculating the same.
Now I'd like to actually hack off and officially round
this using the round function.
So 718, that would round to 72 pennies,
so I'm going to click in the cell and hit F2.
We click after the equal sign, but before our PMT and type
round.
Now we saw this before, we did a multiplication calculation.
The round, and I'm going to click on the screen tick number
argument, that thing can be anything.
It could be a number from a cell,
multiplication like we did before,
this function or even a big huge formula calculation thing.
Whatever it is come to the N, type comma and NUM digits.
That means what position do you want to round to?
As we studied before, penny is 2.
Closed parentheses Control Enter.
Now I'm going to decrease those decimals.
From our earlier keyboard video, we
know that I set up my keyboard [INAUDIBLE] to do ALT 1.
So I can Alt 1, 1, 1, 1, 1.
Oh that's beautiful.
So now we have $465.72, that's our monthly payment,
and the PMT really made our job easy.
One last thing for this little template, before we test it,
we're going to test all the inputs
and see if everything updates, I'd like a label at the top.
I'd actually like the label to say the monthly payment is,
and have this formula that looks down here
so when I change these inputs, the label at the top
will change also.
A8 hit F2, and then I'm going to put
my cursor at the very beginning.
And if we want text in a formula,
as we learned last video, you have
to put it in double quotes.
Oh, but we want it to be a formula
equals sign tells the cell this is a formula.
Double quotes, the space, the monthly payment
is, and I'm going to come to the end
and double click those question mark.
Type a double quote, and now I want to join this bit of text
to this cell down here.
So I'm going to use the ampersand shift 7, that's
the join symbol, and then click on this cell reference B12.
Now, if you're paying attention and studying hard
in this class, you know well, that's a cell reference,
and if that's number format and this formula sure is not
going to look at that number formatting,
so when I Control Enter, no it's not
looking at the number formatting.
Now there's a few ways we could make this label more appealing.
I'm going to click on the cell and hit F2.
First thing is, I don't want it showing up as a negative
for my label.
Finance is beautiful, it's showing up negative down here.
So I'm just going to in this text formula
here, type a minus sign in front of it.
Double negative.
A minus minus or the opposite of a negative or a double negative
will turn it positive.
So if I Control Enter, it's positive.
Now one last thing, F2, let's add a dollar sign.
But I can't add the dollar sign here.
Since it's going to be text, I have
to add it somewhere in here.
Now, notice we were smart, we put a space there,
so that there's a space between is and the number.
But I don't want the dollar sign to have
a space between it and the number.
So F2, I actually put the dollar sign right there.
Now this gets tricky doing these text formulas
and the way I usually hack my way through it
is I make a mistake, right?
I accidentally type a dollar sign then I enter it,
and I'm like, oh, wait a second, that's not right.
And then I F2, and I go back and forth until I get it right.
So I'm going to backspace, maybe this'll do it.
All right, so that's pretty cool.
And here's the really cool part.
If I change the down payment to $2,000, boom,
instantly everything updates including my label at the top.
That's just beautiful.
If I change the price of the car, oh wait a second,
I only have $24,000.
Instantly the label and our beautiful round PMT function
works down here.
All right, so the bottom line is,
I don't want to do all of that.
I certainly like the PMD.
And then we remember from our earlier discussions
of round and a text formula, pretty cool.
Now we have another example of when you want to probably find
an easier way to do something and hopefully they
have a built in function.
We have a bunch of house prices here.
Now, let's remember back to our keyboard video.
instantly know how tall this is, so I'm
going to use control down arrow.
Whoa.
10,000 numbers.
Control up arrow and then scroll a little bit.
So we have a data dump of like housing prices
from our area of the state.
Over 10,000 numbers, and we want to calculate an average.
Now most of us think of an average,
arithmetic mean, we add them all up and divide by the count.
But there's lots of different kinds of averages.
Now, if you've ever heard a real estate agent
talk about houses, their typical value they
use is not arithmetic mean, they don't add them all up
and divide by the count.
They always say hey, the median price is-- well,
averages just means a typical value.
If you're advertising or a real estate agent,
you need one number to represent all the data points
to say something about that neighborhood.
Hey, the average price is blank.
So an average is one number that represents all the numbers.
Well the problem with arithmetic mean when it adds them all up
and divides by the count, is it uses all of them.
If there's $1 million house and I'm going to click in this
and control down arrow-- oh, so it's not sorted.
But you can see if there's a few really expensive houses
and the rest are not very expensive, I clicked and then
control up arrow, that would make your average
or mean calculation bigger than it should be.
And that's actually why real estate agents use the median.
Let's first search for a functional
that'll calculate average.
Well, we're not going to do that because we already
did that earlier in the class.
We know that it's the average.
So the people who design spreadsheets did not
use the word mean, that is the word from statistics.
But a mean is technically the word
that describes adding them all up and dividing by the count.
But the function in Excel is average.
So all right, I'm going to click and remember
my keyboard shortcut Control Shift down arrow, I love it.
I don't have to drag my cursor down 10,000 rows.
Now I'm going to hit Enter.
If I hit Enter, it goes down, right?
And jumps the screen back up but not quite high enough.
So if the real estate agent wanted to use this number,
they'd say the average house is $460,000 approximately.
Now median.
Let's just see how we'd have to do this by hand.
This is terrible.
You actually have to sort the list of numbers
and find the one in the middle.
So for this huge data set, I don't even know how to do that.
I'd have to do it-- it'd be terrible.
First I'd have to count, then I'd have to divide by 2,
we'd have to look up that one and then
look up the one after it.
And then we'd have to take those two because there
is an even number and average those to get the median.
I never want to do that.
So luckily, because that's the median right there.
Luckily, there's a built in function.
So the idea is they put functions
in that make our life easy as Excel users.
So I'm going to click in this cell
and click on the f of x and type-- well,
I know the name of this one.
Median.
And I hit Enter instead of clicking go.
And right at the top, it's called median,
it's a very common calculation.
Returns the median or the number in the middle of a set
given numbers.
Oh, that is so cool.
So I click OK.
It's trying to guess-- we talked about this earlier
when we did like the sum function,
but sometimes it does that in the function argument dialog
box also.
So click in the top cell.
Control Shift down arrow, and then I'm going to click OK.
That is amazing and a lot easier than this calculation
we did over here.
And we can see the median, in terms of the real estate agent,
this number's a lot smaller than the arithmetic mean.
The arithmetic mean had those million dollar houses included,
so it made this number bigger.
Perhaps this number represents the neighborhood better
than the average calculation.
All right, so median, and we saw PMT,
sometimes searching for built in functions
can make our calculating job a lot easier.
All right, we'll see you next video.