In this lesson, we're going to explore functions. Functions are predefined formulas that perform
calculations using specific values in a particular order.
While you may think of formulas as being a short mathematical equation, like F2 * C2,
they can be really lengthy. Excel even gives extra space here in the formula
bar to accommodate long formulas. Functions can actually save you the time of
writing lengthy formulas because the computation the function will perform is predefined so
that you don't have to enter in all the mathematical operators.
For example, let's say that we want to find the total cost of our inventory.
Basically, we want to add each item in Column G together.
Instead of writing a formula that adds G2 plus G3 plus G4 and so on, we can use a function.
Let's scroll down to A42 and type Total Inventory Cost.
And we're going to use a function to add each item in column G and place the total here
in G42. To access our functions, select the Formulas
tab. You'll see a variety of commands in the Function
Library group. From here we can access all of the available
functions. In G42, I want to insert a function that adds
or provides the sum of all the cells in Column G.
So, let's select the drop-down arrow next to the AutoSum command and select Sum.
You'll see a formula has been defined in G42. This is called a function.
I know this looks a little different than the formulas you are used to, but don't worry,
I'll explain more about the function in a minute.
You can see that the cells in the column are selected.
The AutoSum command automatically selects the cells G2 through G41.
This is called a range of cells. Excel essentially predicted those were the
cells I wanted based on where I was inserting the function.
If I press the Enter key or the Enter button next to the formula bar the total appears.
We can see that our inventory is worth $4,371.06. Now, what if you didn't want all those cells
selected? Well, you can edit the function. Let's just select the cell, and go up to our
formula bar. We can edit the range.
Right now, the function adds cells G2 through G41.
If we don't want to include the cups in our inventory total, we can change this to G39
and then click the Enter icon. We can see that the total has changed to reflect
our new range. I actually do want all my inventory selected,
so let's just click Undo on the Quick Access toolbar to go back to our original function.
Now, let's take a look at the parts of the function in closer detail.
Each function has a specific order, called syntax, which must be strictly followed for
the function to work correctly. All functions begin with the equal sign.
After the = sign, you have to define the function name.
In this case, it's SUM. Remember, a function is a predefined formula,
so this function tells Excel that it will add a group of cells.
The next part of the function is the argument. The information enclosed in the parentheses
is the argument. The argument in this function is G2:G41 which
represents a range of cells. You use the colon to represent a range of
cells. The range basically says that all the cells
from G2 to G41 are included. I know the language can be really confusing,
but it is important to know. You won't need to know these terms to use
the functions, but you will need to know them if you use the Excel Help tool.
You can also have a function that has more than one argument.
For example, this function calculates the sum of two ranges.
Let's take a look at how you can insert this function into your spreadsheet.
We want to calculate the total dollar amount we have of cones and cups.
Instead of using the AutoSum command, I can insert the function another way.
First, I need to select the cell where I want the function to appear.
Then, click the Insert Function command on the Formulas tab.
A dialog box appears. By default, the SUM function is selected,
but if I wanted to perform another mathematical operation I could use this search tool to
find a function that would perform the calculation that I need.
All you have to do is type a question in the Search for a function box and click GO.
Or, you can scroll through the list of functions in the Select a function field.
As you select different functions, a description of the function will display below describing
what calculation the function will perform. If you would prefer to narrow this list, you
can select a category and only view these types of functions.
We'll just select Sum, and click OK and the Function Arguments dialog box appears so that
I can enter cells that I want to include in the function.
We'll make sure our cursor is in the Number 1 field, and then find the area of our spreadsheet
where the Cones are listed. Now, left-click and drag your cursor to select
cells G21 through 26, and then release the mouse button.
We can see now that the argument appears in the Number 1 field.
Since we want to add another argument, we'll just place our cursor in the Number 2 field,
and in the spreadsheet select cells G40 and 41. And the argument appears in the field.
If we look at the spredsheet, we can see that the function with two arguments appears in the cell.
Now, click OK in the dialog box and the function
adds the amount of cups and cones we have together and displays it in G44.
There are so many ways you can write functions and so many types of functions.
Another simple function that I can show you using the Inventory spreadsheet is the Average
function. This function computes the average of a range
of cells. Let's say we want to calculate the average
cost we pay for a 2.5 gallon tub of ice cream. The price varies a bit depending on the flavor,
but we want to find the average cost. In A46, I'll type Average Flavor, and then
select C46 where I want the function to appear. Now, if I click the drop-down arrow next to
the AutoSum command and select Average, we can see that Excel automatically selects all
the cells in column C. But, wait.
I only want to find the average cost that I pay for a 2.5 gallon tub of ice cream.
Excel automatically chose all of these cells, but it's not quite right.
So, all I need to do is left click and drag to select the cells I want to average.
As I'm dragging my mouse and selecting the cells, I can see in the formula bar how the
formula is changing. Once the correct cells are selected, I can
click Enter icon, and see that the amount listed is the average cost for a 2.5 gallon
tub of ice cream. Ok, this is something I need to
point out really quickly.
If you look closely, a small green triangle and an exclamation mark appeared in the left
corner of the cell where I was calculating the average.
This usually indicates some type of mistake, so let's take a look.
If I scroll over the exclamation point I can select the drop down arrow which will give
me a list of options. I don't want to include the adjacent cells
so I'll just ignore this error. One of the many great features in Excel is
that it will catch your mistakes for you and make suggestions on how to fix those mistakes!
SUM and AVG are just 2 of the most commonly used functions.
You can use functions to accomplish a variety of mathematical calculations.
From the Function Library you can see some of the categories of functions.
There are financial functions that will help you work with interest rates, calculate loan
payments, and determine depreciation amounts; there are Date and Time functions that will
help you do things like calculate the number of days between two dates, Math &
Trig functions, and so much more. Excel literally has hundreds of different
functions to assist you with your calculations. Building formulas can be difficult and time-consuming.
Excel's functions can save you a lot of time and headaches.