Excel 2007 Tutorial 3.1. Using Functions
Uploaded by djdates on 19.06.2010
Transcript:
In this video, we will discuss using functions in Microsoft Excel 2007.
Opening an empty spreadsheet, we are going to enter some simple data. We will create
one column to show the functions we are using and another column to show our data.
Previously, we talked about the Autosum icon on the Home ribbon.
If we select the empty cell underneath our data, we can click on the AutoSum icon and
AutoSum will create a formula using the sum function. Looking at the formula, we can see
the equal sign that begins every formula, the sum keyword, the parenthesis, and the
range of cells. Our range of cells consists of the first cell, B2, separated from the
last cell, B5, by a colon. Remember, the colon indicates that this is a range of cells and
not just two cells.
Let's copy the formula by itself to another cell. Instead of right clicking and selecting
copy, we can use a keyboard command. We can hold down the Control [Ctrl] key and press
[C] to copy the text, press the Escape [Esc] key to exit editing the cell, and press Control
[Ctrl] + [V] to paste the text into another cell.
We can also select our data cells and click on Autosum, and Autosum will create a formula
in the next empty cell. Instead of just creating another sum formula, we can click on the drop
down arrow next to Autosum and select a different type of function, such as Average. Like sum,
average is taking our range of cells, B2:B5, and averaging the numbers, adding the cells
and dividing by four.
The Count function tells us how many items are in a range of cells.
The Max function tells us the largest number in a range of cells.
The Min function tells us the smallest number in a range of cells.
Some functions can take a range of cells in the parenthesis, some functions can only take
one cell or value, and some functions do not take any cells or values at all.
An example of a function that can only take one cell or value is the absolute value function.
Let's say we don't know the keyword for this function, but we can guess that it might start
with a letter A.
By typing an equal sign, then an A in a cell, Excel shows autocomplete options for functions
starting with A. We can click on one of these functions and use our arrow keys to move through
the list, and then when have found our absolute value function, we can click on it or press
the [Tab] key.
Notice that the tool tip says that the ABS function accepts only a number in the parenthesis.
Let's see what happens if we put in our range of cells.
Notice we get the #VALUE! error message? This means that we are using the function incorrectly.
Let's use it correctly now and click on a single cell, and we can see that the absolute
value function has stripped the minus sign from this value.
We can also pass a value to the absolute value function without that value existing inside
of another cell.
So as we work with functions, it is important to remember that functions that accept values
can accept data directly or through cell references to cells that contain our data.
The NOW function is an example of a function that does not take any values. The NOW function
tells us the current date and time, but even though we are not passing any information
to this function, the parenthesis are still required. Removing the parenthesis will result
in an error.
Let's take a look at a much more complex function.
Let's say that we are interested in calculating the loan payments for a new car we are buying.
We are going to borrow $20,000 at 4% interest over five years.
If we look at the formula ribbon, we can see that there are a number of different categories
for functions. If we are interested in a financial function, such as calculating our loan payment,
there are many options, but we might not easily find the function we are looking for.
As part of the formula bar, there is an Insert Function icon. This allows us to easily search
for a function. We can type in the words "calculate loan payment" and click on Go, and the first
option we are presented with is a payment PMT function that "Calculates the payment
for a loan based on constant payments and a constant interest rate." If we click on
OK, we are presented with a guided dialog box that will help us use this function.
The arguments in bold are mandatory, while the arguments that are not in bold are optional.
Near the bottom of the screen, we can see the definition for our current argument. We
can see that rate is defined as "the interest rate period for the loan. For example, use
6%/4 for quarterly payments at 6% APR."
APR stands for Annual Percentage Rate.
Our hypothetical loan is at 4%, but we will be making payments on this loan on a monthly
basis, so we will need to divide this interest by 12 months, because we are paying 4% interest
per year, not per month.
We can click on our interest cell and add a forward leaning slash / and the number 12
to divide by 12. Now the dialog box shows that our monthly interest rate is .003 repeating.
Let's look at the next argument. "Nper is the total number of payments for the loan."
We will be paying off this loan over five years, but we will be making monthly payments,
so we click on the cell containing the number of years and use the asterisk * to multiply
this value by 12, giving us 60 payments. Finally, let's look at the PV argument. "Pv
is the present value: the total amount that a series of future payments is worth now."
In other words, Pv is our starting principal -- the amount of money we are borrowing,
so we will select that cell.
Once we have selected the mandatory arguments, it shows the result of our function, that
our payment will be $368.
Clicking on OK, we can now see this payment amount on our spreadsheet. The value is in
currency format and the value is shown in parenthesis with red text because the value
is a negative number.
Notice in our function, we have used a combination of cells and numbers, including multiplication
and division operations. It is important to remember that Excel enforces the mathematical
order of operations. Any operations in parenthesis will be calculated first, followed by exponents,
multiplication, division, and then finally addition and subtraction.
We can even include the functions inside the arguments we pass to other functions, such
as copy and pasting our payment formula inside the parenthesis of an absolute value function,
in case we want our loan payment to appear as a positive number.
Excel has a tremendous number of built-in functions and any time you wish to explore
these functions, you can take a look through the different categories found on the Formula
Ribbon.
Opening an empty spreadsheet, we are going to enter some simple data. We will create
one column to show the functions we are using and another column to show our data.
Previously, we talked about the Autosum icon on the Home ribbon.
If we select the empty cell underneath our data, we can click on the AutoSum icon and
AutoSum will create a formula using the sum function. Looking at the formula, we can see
the equal sign that begins every formula, the sum keyword, the parenthesis, and the
range of cells. Our range of cells consists of the first cell, B2, separated from the
last cell, B5, by a colon. Remember, the colon indicates that this is a range of cells and
not just two cells.
Let's copy the formula by itself to another cell. Instead of right clicking and selecting
copy, we can use a keyboard command. We can hold down the Control [Ctrl] key and press
[C] to copy the text, press the Escape [Esc] key to exit editing the cell, and press Control
[Ctrl] + [V] to paste the text into another cell.
We can also select our data cells and click on Autosum, and Autosum will create a formula
in the next empty cell. Instead of just creating another sum formula, we can click on the drop
down arrow next to Autosum and select a different type of function, such as Average. Like sum,
average is taking our range of cells, B2:B5, and averaging the numbers, adding the cells
and dividing by four.
The Count function tells us how many items are in a range of cells.
The Max function tells us the largest number in a range of cells.
The Min function tells us the smallest number in a range of cells.
Some functions can take a range of cells in the parenthesis, some functions can only take
one cell or value, and some functions do not take any cells or values at all.
An example of a function that can only take one cell or value is the absolute value function.
Let's say we don't know the keyword for this function, but we can guess that it might start
with a letter A.
By typing an equal sign, then an A in a cell, Excel shows autocomplete options for functions
starting with A. We can click on one of these functions and use our arrow keys to move through
the list, and then when have found our absolute value function, we can click on it or press
the [Tab] key.
Notice that the tool tip says that the ABS function accepts only a number in the parenthesis.
Let's see what happens if we put in our range of cells.
Notice we get the #VALUE! error message? This means that we are using the function incorrectly.
Let's use it correctly now and click on a single cell, and we can see that the absolute
value function has stripped the minus sign from this value.
We can also pass a value to the absolute value function without that value existing inside
of another cell.
So as we work with functions, it is important to remember that functions that accept values
can accept data directly or through cell references to cells that contain our data.
The NOW function is an example of a function that does not take any values. The NOW function
tells us the current date and time, but even though we are not passing any information
to this function, the parenthesis are still required. Removing the parenthesis will result
in an error.
Let's take a look at a much more complex function.
Let's say that we are interested in calculating the loan payments for a new car we are buying.
We are going to borrow $20,000 at 4% interest over five years.
If we look at the formula ribbon, we can see that there are a number of different categories
for functions. If we are interested in a financial function, such as calculating our loan payment,
there are many options, but we might not easily find the function we are looking for.
As part of the formula bar, there is an Insert Function icon. This allows us to easily search
for a function. We can type in the words "calculate loan payment" and click on Go, and the first
option we are presented with is a payment PMT function that "Calculates the payment
for a loan based on constant payments and a constant interest rate." If we click on
OK, we are presented with a guided dialog box that will help us use this function.
The arguments in bold are mandatory, while the arguments that are not in bold are optional.
Near the bottom of the screen, we can see the definition for our current argument. We
can see that rate is defined as "the interest rate period for the loan. For example, use
6%/4 for quarterly payments at 6% APR."
APR stands for Annual Percentage Rate.
Our hypothetical loan is at 4%, but we will be making payments on this loan on a monthly
basis, so we will need to divide this interest by 12 months, because we are paying 4% interest
per year, not per month.
We can click on our interest cell and add a forward leaning slash / and the number 12
to divide by 12. Now the dialog box shows that our monthly interest rate is .003 repeating.
Let's look at the next argument. "Nper is the total number of payments for the loan."
We will be paying off this loan over five years, but we will be making monthly payments,
so we click on the cell containing the number of years and use the asterisk * to multiply
this value by 12, giving us 60 payments. Finally, let's look at the PV argument. "Pv
is the present value: the total amount that a series of future payments is worth now."
In other words, Pv is our starting principal -- the amount of money we are borrowing,
so we will select that cell.
Once we have selected the mandatory arguments, it shows the result of our function, that
our payment will be $368.
Clicking on OK, we can now see this payment amount on our spreadsheet. The value is in
currency format and the value is shown in parenthesis with red text because the value
is a negative number.
Notice in our function, we have used a combination of cells and numbers, including multiplication
and division operations. It is important to remember that Excel enforces the mathematical
order of operations. Any operations in parenthesis will be calculated first, followed by exponents,
multiplication, division, and then finally addition and subtraction.
We can even include the functions inside the arguments we pass to other functions, such
as copy and pasting our payment formula inside the parenthesis of an absolute value function,
in case we want our loan payment to appear as a positive number.
Excel has a tremendous number of built-in functions and any time you wish to explore
these functions, you can take a look through the different categories found on the Formula
Ribbon.