WEBVTT Kind: captions Language: en 00:00:06.859 --> 00:00:12.889 Excel is intended to be used to calculate and analyze numerical information such as 00:00:12.889 --> 00:00:27.889 household budgets, company finances, inventory, and more. 00:00:29.710 --> 00:00:36.680 To do this, you need to understand formulas. In lesson 5, we cover simple formulas that 00:00:36.680 --> 00:00:43.680 have one mathematical operation, such as 2+2. In this lesson, we'll discuss complex formulas 00:00:46.560 --> 00:00:52.360 that use multiple mathematical operators, and that use absolute and relative references. 00:00:54.920 --> 00:00:59.829 This lesson may take you back to high school math because in Excel, there are certain math 00:00:59.829 --> 00:01:06.680 rules that you have to follow for formulas to work. 00:01:06.680 --> 00:01:11.549 The order of mathematical operations is very important. 00:01:11.549 --> 00:01:18.460 If you enter a formula that contains several operations like addition, subtraction, multiplication 00:01:18.460 --> 00:01:25.320 and division, Excel knows to work these operations in a specific order. 00:01:25.320 --> 00:01:30.590 First in that order is parentheses. Operations that are enclosed in parentheses 00:01:30.590 --> 00:01:37.530 are completed first. Next we have exponents. 00:01:37.530 --> 00:01:44.390 Calculations that involve exponents, such as 2 to the second power, are completed next. 00:01:44.390 --> 00:01:50.020 I would guess that most of the spreadsheets you work with will not use exponents, but 00:01:50.020 --> 00:01:55.570 this is part of the basic mathematical order, so it's worth mentioning. 00:01:55.570 --> 00:02:02.570 The next steps in the order are multiplication and division. 00:02:05.070 --> 00:02:09.500 These two operations are considered to be equal, or at the same level. 00:02:09.500 --> 00:02:14.810 This means that if you have a formula with both a multiplication and a division sign, 00:02:14.810 --> 00:02:21.810 Excel will read the formula from left to right and perform whichever operation comes first. 00:02:23.270 --> 00:02:30.270 And finally, we have addition and subtraction. These two operations are considered equal 00:02:30.890 --> 00:02:36.690 like multiplication and division, so Excel will read the formula from left to right and 00:02:36.690 --> 00:02:46.560 perform whichever operation comes first. A mnemonic that can help you remember this 00:02:46.560 --> 00:02:53.560 is Please Excuse My Dear Aunt Sally. I actually remember this from my ninth grade 00:02:53.730 --> 00:02:59.750 math class. It stands for Parentheses, Exponents, Multiplication, 00:02:59.750 --> 00:03:06.750 Division, Addition, and Subtraction. Like I said before, you probably won't have 00:03:06.940 --> 00:03:11.480 to deal with exponents, but it will help you remember the order of operations. 00:03:11.480 --> 00:03:18.480 Now, let's take a look at how the order of operations will affect your Excel formulas. 00:03:20.019 --> 00:03:26.510 Let's write a formula with 2 operations. As always, we start our formulas with an equal 00:03:26.510 --> 00:03:33.510 sign. Now, Excel will follow the order of operations 00:03:34.879 --> 00:03:39.159 and will multiply the two numbers and then add. 00:03:39.159 --> 00:03:46.159 So, 3 times 2 equals 6, and 3 plus 6 equals 9. 00:03:48.450 --> 00:03:55.450 So, I'll just click enter and see, yes the answer is nine. 00:03:57.060 --> 00:04:04.060 Now, what if I want Excel to add 3 plus 3 together before multiplying? Let's retype 00:04:05.459 --> 00:04:12.459 the formula. And add parentheses around 3 plus 3. 00:04:12.700 --> 00:04:18.909 Following the order of operations, Excel will add the numbers in parentheses first, so we 00:04:18.909 --> 00:04:25.909 have 3 plus 3 equals six, and then multiply that number times 2 to equal 12. 00:04:26.550 --> 00:04:31.699 Let's check. And we can see that Excel followed the order 00:04:31.699 --> 00:04:37.729 of operations. Before moving on, let's take a look 00:04:37.729 --> 00:04:43.339 at some formulas to make sure you really understand the order of operations, which Excel uses 00:04:43.339 --> 00:04:47.990 to calculate answers. You really do have to understand this so that 00:04:47.990 --> 00:04:54.990 you can write formulas correctly. We will multiply 4 times 2 before performing 00:04:55.249 --> 00:05:02.249 the division operation because the multiplication sign comes before the division sign. 00:05:04.789 --> 00:05:11.789 The answer is 2. What about this one? We'll divide 4 by 2 before 00:05:12.629 --> 00:05:18.279 performing the multiplication operation because the division sign comes before the multiplication 00:05:18.279 --> 00:05:24.809 sign. The answer is 8. 00:05:24.809 --> 00:05:29.139 Now let's add parentheses and see how that changes the problem. 00:05:29.139 --> 00:05:36.139 We'll need to perform the operation in parentheses (2*4) first and divide 4 by this result. 00:05:39.119 --> 00:05:46.119 The answer is 0.5. What about this one? Multiply 2 times 4 before 00:05:47.689 --> 00:05:54.119 performing the subtraction operation because the multiplication sign is of a higher order 00:05:54.119 --> 00:06:01.119 than the subtraction sign. The answer is negative 4. 00:06:03.059 --> 00:06:07.689 Now that we've covered the order of operations and written a few formulas, let's see how 00:06:07.689 --> 00:06:13.439 it works in a spreadsheet using cell references instead of numbers. 00:06:13.439 --> 00:06:20.439 A cell reference, or cell address, is the name of the cell based on its location. 00:06:22.169 --> 00:06:29.169 This cell is where column G and row 6 intersect, so the cell reference, or cell address, is 00:06:29.919 --> 00:06:35.689 G6. If you're ever unsure about the cell address, 00:06:35.689 --> 00:06:42.689 you can find it in the Name Box, which is to the left of the formula bar. 00:06:43.939 --> 00:06:48.860 Let's say we want to calculate how much I'm spending per bowl. 00:06:48.860 --> 00:06:55.860 Let's write a formula here in H6 and we'll just delete it when we're done. 00:06:56.580 --> 00:07:03.580 We'll insert an equal sign to get started. Now, what do we need to do to calculate this? 00:07:03.669 --> 00:07:10.469 Since we only have one case each of the small and regular bowls, we just need to add the 00:07:10.469 --> 00:07:16.770 two bowl amounts together to calculate how much we've spent on bowls. 00:07:16.770 --> 00:07:23.770 To do this using cell references, we'll select G6, type an addition sign, and then select 00:07:26.610 --> 00:07:31.839 G7. To figure out how much we paid per bowl, we'll 00:07:31.839 --> 00:07:38.839 need to divide by the total amount of bowls that we have. 00:07:39.610 --> 00:07:46.610 To calculate how many bowls we have, we'll need to add cell D6 and D7. 00:07:49.899 --> 00:07:56.899 Now, can you see a problem with this formula? We want to divide the total dollar amount 00:07:59.689 --> 00:08:06.429 we spent by the total number of bowls so that we can calculate how much we're spending per 00:08:06.429 --> 00:08:12.009 bowl. Does this formula do that for us? No, it doesn't. 00:08:12.009 --> 00:08:19.009 You see, with the way the formula is currently written, Excel will divide G7 by D6 since 00:08:21.029 --> 00:08:28.029 division is completed before addition. This will give us some big number that clearly 00:08:28.689 --> 00:08:33.870 doesn't indicate how much we're paying per bowl. 00:08:33.870 --> 00:08:37.930 Let's add some parentheses to the formula so that Excel knows to perform the addition 00:08:37.930 --> 00:08:44.930 operations before it divides. And now we can see that we're paying about 00:08:46.440 --> 00:08:51.779 13 cents per bowl. If you want to show fewer decimal places, 00:08:51.779 --> 00:08:58.069 you can just click the Decrease Decimal Place command on the Home tab. 00:08:58.069 --> 00:09:03.199 I think we've covered complex formulas using the order of operations pretty well, so let's 00:09:03.199 --> 00:09:10.199 talk about two types of cell references - relative and absolute references. 00:09:10.430 --> 00:09:22.720 The formula in this cell takes the number in F2 and multiplies it by the number in C2. 00:09:22.720 --> 00:09:27.379 But what the Excel program sees when it reads this formula is that it should multiply the 00:09:27.379 --> 00:09:34.379 value that appears in one cell to the left by the value that appears in four cells to 00:09:34.509 --> 00:09:41.509 the left from where the formula appears. This is called a relative cell reference. 00:09:43.160 --> 00:09:48.970 When you copy a formula containing relative references to another cell, the relative references 00:09:48.970 --> 00:09:55.120 automatically change. So, when I drag this formula down and fill 00:09:55.120 --> 00:10:02.120 the other cells with it you won't see F2 times C2,you'll see the new relative references. 00:10:06.300 --> 00:10:12.230 So here in G3, the formula reads, the value in one cell to the left times the value that 00:10:12.230 --> 00:10:17.550 appears in four cells to the left or F3 times C3. 00:10:17.550 --> 00:10:24.550 And you can see the same thing in these other cells.The other type of reference is called 00:10:24.720 --> 00:10:30.930 an absolute cell reference. If a formula is copied to a different location, 00:10:30.930 --> 00:10:37.930 the absolute reference remains the same. An absolute reference is designated in the 00:10:39.350 --> 00:10:46.350 formula by the addition of a dollar sign ($). It can precede the column reference or the 00:10:46.500 --> 00:10:53.500 row reference, or both. In this example, the column and the row do 00:10:54.709 --> 00:11:01.230 not change when copied. In this example, the row does not change when 00:11:01.230 --> 00:11:05.660 copied. In this example, The column does not change 00:11:05.660 --> 00:11:12.050 when copied. Let's see how this might work in a spreadsheet. 00:11:12.050 --> 00:11:17.750 Over here in H2, let's write a formula that multiplies the Inventory number by the value 00:11:17.750 --> 00:11:21.449 in C2. If you want to create an absolute reference, 00:11:21.449 --> 00:11:27.439 we'll just add our dollar signs before the C and before the 2. 00:11:27.439 --> 00:11:31.819 And press Enter. And we can see that the amounts in G2 and 00:11:31.819 --> 00:11:36.519 H2 are the same even though H2 uses an absolute reference. 00:11:36.519 --> 00:11:43.519 So, when I fill this formula in to 3 additional cells, I can see that the numbers don't match 00:11:43.839 --> 00:11:53.930 up with what appears in G3, 4, and 5.This formula takes the number in F3 and multiplies 00:11:53.930 --> 00:12:00.819 it by the number in C2. The C2 will stay constant because of the dollar 00:12:00.819 --> 00:12:07.819 signs which make it an absolute reference. This spreadsheet doesn't need these formulas, 00:12:09.360 --> 00:12:14.689 so let's just delete them. As you can see the order of operations and 00:12:14.689 --> 00:12:20.060 relative and absolute references are important to understand if you plan on using formulas 00:12:20.060 --> 00:12:24.769 in your spreadsheet. Spend some time writing formulas and see how 00:12:24.769 --> 00:12:25.269 they work.