WEBVTT Kind: captions Language: en 00:00:07.370 --> 00:00:13.700 In this lesson, we're going to explore functions. Functions are predefined formulas that perform 00:00:13.700 --> 00:00:18.970 calculations using specific values in a particular order. 00:00:18.970 --> 00:00:25.960 While you may think of formulas as being a short mathematical equation, like F2 * C2, 00:00:25.960 --> 00:00:32.059 they can be really lengthy. Excel even gives extra space here in the formula 00:00:32.059 --> 00:00:39.059 bar to accommodate long formulas. Functions can actually save you the time of 00:00:39.120 --> 00:00:45.230 writing lengthy formulas because the computation the function will perform is predefined so 00:00:45.230 --> 00:00:49.149 that you don't have to enter in all the mathematical operators. 00:00:49.149 --> 00:00:55.899 For example, let's say that we want to find the total cost of our inventory. 00:00:55.899 --> 00:01:00.379 Basically, we want to add each item in Column G together. 00:01:00.379 --> 00:01:11.170 Instead of writing a formula that adds G2 plus G3 plus G4 and so on, we can use a function. 00:01:11.170 --> 00:01:17.620 Let's scroll down to A42 and type Total Inventory Cost. 00:01:17.620 --> 00:01:24.190 And we're going to use a function to add each item in column G and place the total here 00:01:24.190 --> 00:01:31.100 in G42. To access our functions, select the Formulas 00:01:31.100 --> 00:01:35.420 tab. You'll see a variety of commands in the Function 00:01:35.420 --> 00:01:41.090 Library group. From here we can access all of the available 00:01:41.090 --> 00:01:46.500 functions. In G42, I want to insert a function that adds 00:01:46.500 --> 00:01:50.660 or provides the sum of all the cells in Column G. 00:01:50.660 --> 00:02:01.930 So, let's select the drop-down arrow next to the AutoSum command and select Sum. 00:02:01.930 --> 00:02:08.930 You'll see a formula has been defined in G42. This is called a function. 00:02:09.340 --> 00:02:13.410 I know this looks a little different than the formulas you are used to, but don't worry, 00:02:13.410 --> 00:02:18.540 I'll explain more about the function in a minute. 00:02:18.540 --> 00:02:23.409 You can see that the cells in the column are selected. 00:02:23.409 --> 00:02:30.409 The AutoSum command automatically selects the cells G2 through G41. 00:02:30.709 --> 00:02:37.700 This is called a range of cells. Excel essentially predicted those were the 00:02:37.700 --> 00:02:43.650 cells I wanted based on where I was inserting the function. 00:02:43.650 --> 00:02:50.650 If I press the Enter key or the Enter button next to the formula bar the total appears. 00:02:52.060 --> 00:03:02.579 We can see that our inventory is worth $4,371.06. Now, what if you didn't want all those cells 00:03:02.579 --> 00:03:11.889 selected? Well, you can edit the function. Let's just select the cell, and go up to our 00:03:11.889 --> 00:03:15.709 formula bar. We can edit the range. 00:03:15.709 --> 00:03:21.779 Right now, the function adds cells G2 through G41. 00:03:21.779 --> 00:03:28.779 If we don't want to include the cups in our inventory total, we can change this to G39 00:03:32.090 --> 00:03:37.389 and then click the Enter icon. We can see that the total has changed to reflect 00:03:37.389 --> 00:03:43.709 our new range. I actually do want all my inventory selected, 00:03:43.709 --> 00:03:50.260 so let's just click Undo on the Quick Access toolbar to go back to our original function. 00:03:50.260 --> 00:03:57.260 Now, let's take a look at the parts of the function in closer detail. 00:03:57.299 --> 00:04:04.090 Each function has a specific order, called syntax, which must be strictly followed for 00:04:04.090 --> 00:04:11.090 the function to work correctly. All functions begin with the equal sign. 00:04:11.739 --> 00:04:15.209 After the = sign, you have to define the function name. 00:04:15.209 --> 00:04:22.209 In this case, it's SUM. Remember, a function is a predefined formula, 00:04:23.280 --> 00:04:30.030 so this function tells Excel that it will add a group of cells. 00:04:30.030 --> 00:04:36.310 The next part of the function is the argument. The information enclosed in the parentheses 00:04:36.310 --> 00:04:43.310 is the argument. The argument in this function is G2:G41 which 00:04:44.009 --> 00:04:51.009 represents a range of cells. You use the colon to represent a range of 00:04:51.150 --> 00:04:55.419 cells. The range basically says that all the cells 00:04:55.419 --> 00:05:02.419 from G2 to G41 are included. I know the language can be really confusing, 00:05:03.340 --> 00:05:07.370 but it is important to know. You won't need to know these terms to use 00:05:07.370 --> 00:05:13.190 the functions, but you will need to know them if you use the Excel Help tool. 00:05:13.190 --> 00:05:17.680 You can also have a function that has more than one argument. 00:05:17.680 --> 00:05:24.680 For example, this function calculates the sum of two ranges. 00:05:26.900 --> 00:05:32.560 Let's take a look at how you can insert this function into your spreadsheet. 00:05:32.560 --> 00:05:39.560 We want to calculate the total dollar amount we have of cones and cups. 00:05:43.580 --> 00:05:49.530 Instead of using the AutoSum command, I can insert the function another way. 00:05:49.530 --> 00:05:55.770 First, I need to select the cell where I want the function to appear. 00:05:55.770 --> 00:06:02.770 Then, click the Insert Function command on the Formulas tab. 00:06:04.379 --> 00:06:13.389 A dialog box appears. By default, the SUM function is selected, 00:06:13.389 --> 00:06:19.729 but if I wanted to perform another mathematical operation I could use this search tool to 00:06:19.729 --> 00:06:24.569 find a function that would perform the calculation that I need. 00:06:24.569 --> 00:06:31.569 All you have to do is type a question in the Search for a function box and click GO. 00:06:32.330 --> 00:06:39.330 Or, you can scroll through the list of functions in the Select a function field. 00:06:39.689 --> 00:06:45.360 As you select different functions, a description of the function will display below describing 00:06:45.360 --> 00:06:52.360 what calculation the function will perform. If you would prefer to narrow this list, you 00:06:53.530 --> 00:06:59.080 can select a category and only view these types of functions. 00:06:59.080 --> 00:07:06.080 We'll just select Sum, and click OK and the Function Arguments dialog box appears so that 00:07:07.509 --> 00:07:11.849 I can enter cells that I want to include in the function. 00:07:11.849 --> 00:07:18.849 We'll make sure our cursor is in the Number 1 field, and then find the area of our spreadsheet 00:07:19.750 --> 00:07:26.719 where the Cones are listed. Now, left-click and drag your cursor to select 00:07:26.719 --> 00:07:33.719 cells G21 through 26, and then release the mouse button. 00:07:34.289 --> 00:07:40.110 We can see now that the argument appears in the Number 1 field. 00:07:40.110 --> 00:07:47.080 Since we want to add another argument, we'll just place our cursor in the Number 2 field, 00:07:47.080 --> 00:07:56.050 and in the spreadsheet select cells G40 and 41. And the argument appears in the field. 00:07:56.050 --> 00:08:04.050 If we look at the spredsheet, we can see that the function with two arguments appears in the cell. 00:08:04.050 --> 00:08:07.889 Now, click OK in the dialog box and the function 00:08:07.889 --> 00:08:14.889 adds the amount of cups and cones we have together and displays it in G44. 00:08:16.680 --> 00:08:21.789 There are so many ways you can write functions and so many types of functions. 00:08:21.789 --> 00:08:26.999 Another simple function that I can show you using the Inventory spreadsheet is the Average 00:08:26.999 --> 00:08:31.159 function. This function computes the average of a range 00:08:31.159 --> 00:08:34.760 of cells. Let's say we want to calculate the average 00:08:34.760 --> 00:08:43.820 cost we pay for a 2.5 gallon tub of ice cream. The price varies a bit depending on the flavor, 00:08:43.820 --> 00:08:53.920 but we want to find the average cost. In A46, I'll type Average Flavor, and then 00:08:53.920 --> 00:09:03.660 select C46 where I want the function to appear. Now, if I click the drop-down arrow next to 00:09:03.660 --> 00:09:10.660 the AutoSum command and select Average, we can see that Excel automatically selects all 00:09:11.220 --> 00:09:15.570 the cells in column C. But, wait. 00:09:15.570 --> 00:09:22.570 I only want to find the average cost that I pay for a 2.5 gallon tub of ice cream. 00:09:23.040 --> 00:09:28.100 Excel automatically chose all of these cells, but it's not quite right. 00:09:28.100 --> 00:09:35.100 So, all I need to do is left click and drag to select the cells I want to average. 00:09:36.279 --> 00:09:41.779 As I'm dragging my mouse and selecting the cells, I can see in the formula bar how the 00:09:41.779 --> 00:09:47.450 formula is changing. Once the correct cells are selected, I can 00:09:47.450 --> 00:09:54.450 click Enter icon, and see that the amount listed is the average cost for a 2.5 gallon 00:09:55.040 --> 00:09:58.410 tub of ice cream. Ok, this is something I need to 00:09:58.410 --> 00:10:00.060 point out really quickly. 00:10:00.060 --> 00:10:06.250 If you look closely, a small green triangle and an exclamation mark appeared in the left 00:10:06.250 --> 00:10:10.990 corner of the cell where I was calculating the average. 00:10:10.990 --> 00:10:16.600 This usually indicates some type of mistake, so let's take a look. 00:10:16.600 --> 00:10:22.680 If I scroll over the exclamation point I can select the drop down arrow which will give 00:10:22.680 --> 00:10:29.680 me a list of options. I don't want to include the adjacent cells 00:10:30.050 --> 00:10:35.579 so I'll just ignore this error. One of the many great features in Excel is 00:10:35.579 --> 00:10:42.579 that it will catch your mistakes for you and make suggestions on how to fix those mistakes! 00:10:43.269 --> 00:10:48.079 SUM and AVG are just 2 of the most commonly used functions. 00:10:48.079 --> 00:10:53.810 You can use functions to accomplish a variety of mathematical calculations. 00:10:53.810 --> 00:10:59.000 From the Function Library you can see some of the categories of functions. 00:10:59.000 --> 00:11:03.610 There are financial functions that will help you work with interest rates, calculate loan 00:11:03.610 --> 00:11:10.380 payments, and determine depreciation amounts; there are Date and Time functions that will 00:11:10.380 --> 00:11:17.310 help you do things like calculate the number of days between two dates, Math & 00:11:17.310 --> 00:11:23.589 Trig functions, and so much more. Excel literally has hundreds of different 00:11:23.589 --> 00:11:29.170 functions to assist you with your calculations. Building formulas can be difficult and time-consuming. 00:11:29.170 --> 00:11:34.139 Excel's functions can save you a lot of time and headaches.