Welcome to Excel 2010 Business Math video number 7.
And if you on to download this workbook, 135 Chapter 00,
click on the link below the video.
Hey.
We're on the sheet Functions.
We want to look at a couple of different built-in functions
that will make our life easier.
Now I want to scoot over and I see some sheets
over this direction.
So I'm going to use my arrow to expose more sheets.
All right.
So we're on the sheet Functions.
Well, adding, we've already seen we can use the sum function.
And we know the keyboard shortcut.
It's also the only function that has a keyboard shortcut.
So Alt-equals, and that will give us the sum function.
Now notice, equal sign is still--
the definition of a formula is that we have the equal sign
as the first character in the cell.
We just happen to be using one of the built-in functions
called sum.
It's looking at a range.
We saw how cool range functions were a couple videos ago.
So if it's guessing right, we hit Enter.
Now average.
Now, in statistics, it's called mean.
In regular language, it's called average.
You add all of these up and divide by the count.
And that gives you one value you can use and say
the average sales are so-and-so.
All right.
So how would you do that?
Well, what if you knew that the function was called average.
Well, you could just type it out like this.
So average.
Oh.
Look at this.
So this is a way you can actually guess, right?
There are built-- and this is in 2007 and '10.
There's a little list.
And it tells you over here a description.
And once you get your list, you can arrow down
through each one of these.
There's a bunch of different types of averages.
But this one says--
an it's going off the screen, so you can't see it.
But returns the average, arithmetic
mean of its arguments, which can be
numbers, et cetera, et cetera.
So the arithmetic mean means that you add them all up
and divide by the count.
So you can either double-click, or once it's blue,
you can hit Tab.
Now, this is called a screen tip.
It says number 1, number 2, number 3, separated by commas.
Now, we only have a single column.
But if you had many columns all over the place,
you could just load them all into the average
and it would calculate the average.
I mean, that's pretty profound, because adding all these up,
writing the number down, and then counting them
and then doing that division is pretty time-consuming.
Now here's another cool thing about functions.
If it's a simple function with just one argument--
this is called an argument, that number 1 is called an argument.
If it's a simple function, you actually
don't have to type the close parentheses.
If you hit Enter or Control-Enter or Tab,
it will put it in for you.
I'm going to Control-Enter.
I'm immediately going to look up here and I can see, oh, yeah.
It did put it in.
If I hit F2, I can see sure enough it put it in for me.
Those are two examples of functions.
Now here's another cool trick.
If you just are doing something on the fly-- and in this class,
we're always going to have to actually make the calculation.
But if you're on the phone with someone
and you know you've done sum and they say, yeah,
but what's the average, there's something down here.
This is called the status bar.
And it shows the average, the count, and the sum
is that totally cool?
And watch this.
If they say, what are the first three in our list?
Oh, it totally updates.
All right.
And actually, you can right-click and choose
other functions that you might want, like numerical count,
min, max, stuff like that.
Now let's look at this situation right here.
We have a car loan.
And we'll do this exact calculation
in I think Chapter 10.
The math behind it is pretty cool.
It's very complicated though.
But anyway, so we have $16,000.
We have a monthly rate of 1/2 a percent, 0.5%.
And in Chapters 3 and 4 we'll see
how to make the conversion between knowing
it's 1/2 a percent and 0.5%.
And how many months until it's due?
60.
And you're like, man, I wish I could
calculate the monthly payment.
So you're smart.
You've been using Excel.
You're saying, I bet you there's a function.
So you come up here.
This is called the formula bar.
We saw the name box in our first video.
We've seen the formula bar right up
here that shows us what's in the cell, either
the actual typed-in data or the formula.
And right here is Insert Function.
If you click on this, notice our cell is selected.
We click on this and it brings up the Insert Function dialog
box.
Now we can type a description up here.
I can already see the function actually is in this list,
but that's because I used it recently.
Let's just assume you've never used the function before.
So we can search and then click Go.
So I'm going to type "monthly payment on loan."
And I'm going to type the word "loan" because this is a loan.
And then I'm going to click Go.
Now what it gives you is it returns.
And you can click on each one of these and read down here.
Now all of these functions that it returns
are related to finance.
And actually, we'll see a number of these functions
when we get to Chapters 9, 10, and 11, I think.
But the trick when you're searching
is to read the description here.
It just so happens that the first one is the one we want,
but that isn't always the case.
It says "calculates the payment for a loan based
on constant payments and constant interest rate,"
which most car loans and home loans are exactly that.
So once you find the function you want, you click OK.
Now this is called the Functions Argument dialog box.
And it's kind of convenient, especially
if you've never used the function before.
So what it does is as you click in each one of these
or hit Tab to move forward, there's
a description down here.
And it tells you what you should be putting in them.
Now, in this class--
yeah.
When we get later we'll talk about all these arguments.
But for us right here for calculating a loan payment,
we just need the ones in bold.
So rate, it says down here "rate is the interest rate
per period for the loan.
For example, use 6% divide by 4 for quarterly payments."
Well, we already have-- and you can drag the dialog box out
of the way.
We already have-- and we're very careful
about this-- monthly rate and total number of months.
That unit has to be in agreement.
Again, in Chapters 9, 10, 11, we'll talk more about this.
OK.
So we have our rate.
It's over there in the spreadsheet.
Well, how do we get it?
Well, we can drag this to the side.
And if our cursor's flashing, we simply
come to the spreadsheet and click.
Notice down here it starts to emerge.
Up here it starts to emerge.
But there it is.
It shows you a preview, 0.005 there.
Hit Tab.
Number is the total number of payments for the loan.
Oh, OK.
We already have that too.
So with the cursor flashing, you click right here.
The point here, even if you don't understand the finance
part of it, which we'll study later,
is how awesome it is that they have
a list of these items and a description down here.
That really helps us.
Now, this one, we'll learn the term "present
value" in Chapters 10 and 11.
This is a finance term.
And if you read all of this, it may not make much sense.
But think about it.
What is-- if we go out and borrow a car--
borrow some money to buy a car, on the day we get the loan,
what's the present value of that loan?
Well, it's $16,000, so I'm going to click there.
Now, later in the chapters we'll see how
to use these other ones here.
I'm going to click OK.
And there it calculates.
Now, if you calculated this for the first time,
you didn't know about number formatting, you'd be like,
what?
But we're smart enough.
We know it's number formatting.
We Control-1 to open up the Format Cells dialog box.
And if you don't like currency, how many decimals
or the symbol or anything, you can change it.
I'm going to change it to that.
Now, the question is, why is it negative?
Well, the awesome thing about Excel
is they put a lot of hard work into making functions that
do all the hard work for you.
This function knows that when you make your payment
each month, it's painfully coming out of your wallet,
so it knows that it's a negative from your point of view.
In Chapters 9, 10, and 11, we'll talk
all about the financial functions and the point of view
and when it's negative and when it's positive.
All right.
So what we saw is that, man, that was cool.
We could search for a function and we
can have the Functions Argument dialog box help us fill it out.
Now, in this class, as we go through the videos,
we'll show you all the new functions that we encounter.
But when you're out there in the working world,
you really do need to have the skill of being
able to find something.
Just out of curiosity, let's do this.
Let's say you're in an algebra class or something like that.
And you're like, man, I need to find the slope.
I bet you anything if you type in "slope," sure enough
there's a slope function.
And it does exactly what you do in algebra.
Or what if you're an accountant, you're
like, oh, depreciation, how do I calculate depreciation?
So you type "straight line," because that's
the name of one type of depreciation, and click Go.
And you'd have to read through these.
No, it's not that one.
It's definitely not trend.
Variable?
No.
It's not that one.
And you get down here, "returns the straight line"--
oh.
That is so cool.
Not only that, but in accounting,
double-declining balance is hard to calculate.
And there's a built-in function for it.
So the point is this rules, being
able to search for a function.
Sometimes years ago when I was learning Excel,
I'd literally just go through this list
and read them and see all the different ones.
All right.
I'm going to click Cancel.
All right.
That's a little bit about functions.
In our next video coming up, we'll talk about cell
references, our last kind of topic in our Week 1,
Chapter 00.
See you next video.