WEBVTT Kind: captions Language: en 00:00:09.519 --> 00:00:14.000 You may already have some experience creating simple formulas that have only one type of 00:00:14.000 --> 00:00:15.000 operation… 00:00:15.000 --> 00:00:19.859 But sometimes, you have to create more complex formulas that do have more than one operator; 00:00:19.859 --> 00:00:25.810 for example, addition and multiplication. To do this correctly, you need to understand 00:00:25.810 --> 00:00:30.890 how Excel is going to calculate the answer. 00:00:30.890 --> 00:00:35.690 When solving formulas, Excel uses a set of rules called the Order of Operations, which 00:00:35.690 --> 00:00:40.390 you might remember from math class. The Order of Operations determines the order in which 00:00:40.390 --> 00:00:44.789 the formula will be calculated, so you know exactly how to write it out. 00:00:44.789 --> 00:00:48.260 The first thing that's calculated are any equations in parentheses. 00:00:48.260 --> 00:00:53.789 Then, if you have any exponents, you'll calculate those. 00:00:53.789 --> 00:01:01.170 Next is multiplication and division. And finally, addition and subtraction. 00:01:01.170 --> 00:01:06.350 Excel knows to use the Order of Operations whenever it calculates a more complex formula. 00:01:06.350 --> 00:01:10.290 It's important that you remember it, so you can create formulas that'll give you the correct 00:01:10.290 --> 00:01:11.890 answer. 00:01:11.890 --> 00:01:17.590 To remember the order, you can just use the acronym "PEMDAS." Or, to make it even easier, 00:01:17.590 --> 00:01:22.530 remember the phrase, "Please Excuse My Dear Aunt Sally." 00:01:22.530 --> 00:01:26.970 Let's take a closer look at some examples. 00:01:26.970 --> 00:01:34.009 In this formula, 5+2 is written in parentheses. So we'll calculate that first to get 7… 00:01:34.009 --> 00:01:38.860 and then we can just multiply 3 times 7 to get 21. 00:01:38.860 --> 00:01:47.549 This formula contains an exponent—3 to the power of 2. We'll need to calculate that first… 00:01:47.549 --> 00:01:52.710 and then we can do the addition. 00:01:52.710 --> 00:01:59.009 If you have multiplication or division, that'll come next. Here, we multiply 2 times 4 to 00:01:59.009 --> 00:02:02.630 get 8… and 9 minus 8 equals 1. 00:02:02.630 --> 00:02:09.729 Now, multiplication and division are considered to be on the same level. That means if you 00:02:09.729 --> 00:02:14.080 have both of them in your formula, the order simply goes from left to right. 00:02:14.080 --> 00:02:20.520 In this example, we'll do the division first… and then the multiplication. 00:02:20.520 --> 00:02:25.110 If we wanted the multiplication to happen first, we could just put it in parentheses—or 00:02:25.110 --> 00:02:28.670 we could swap the order so the multiplication was on the left. 00:02:28.670 --> 00:02:36.410 Finally, we have addition and subtraction. Just like multiplication and division, these 00:02:36.410 --> 00:02:39.950 are on the same level, so we'll calculate them from left to right. 00:02:39.950 --> 00:02:49.519 OK, now let's put them all together. This formula may look really complicated, but we're 00:02:49.519 --> 00:02:53.700 just going to go step-by-step through the Order of Operations, and you'll see it's really 00:02:53.700 --> 00:02:54.700 not that bad. 00:02:54.700 --> 00:03:00.879 First we'll start with parentheses. We'll calculate each of these to get 2 and 3… 00:03:00.879 --> 00:03:04.269 and as you can see, the formula's already starting to look a bit simpler. 00:03:04.269 --> 00:03:10.670 Next, we'll look to see if there are any exponents in this formula. There is one—2 to the second 00:03:10.670 --> 00:03:14.170 power. When we calculate that, it becomes 4. 00:03:14.170 --> 00:03:22.150 Next, we'll do multiplication and division, going from left to right. 00:03:22.150 --> 00:03:28.260 And now all that's left is addition—and that gives us our answer, which is 8.75. And 00:03:28.260 --> 00:03:35.129 that's the same answer Excel would give you, if you entered that formula in your worksheet. 00:03:35.129 --> 00:03:39.180 Now I'm going to show you why it's so important that you follow the Order of Operations every 00:03:39.180 --> 00:03:43.709 time. If you don't, your formulas won't give you the correct answer. 00:03:43.709 --> 00:03:48.590 In this example, I'd like to add these three values, and find out how much the sales tax 00:03:48.590 --> 00:03:50.190 will be. 00:03:50.190 --> 00:03:54.830 I'll start by typing the equal sign, which is how you always start a formula. Then I'll 00:03:54.830 --> 00:04:03.170 calculate D3+D4+D5. 00:04:03.170 --> 00:04:09.040 Next I'll multiply by .075, which is the tax rate at 7.5%. 00:04:09.040 --> 00:04:14.470 Now, this may look like it's going to give us the correct answer, but it's not. That's 00:04:14.470 --> 00:04:17.169 because we didn't follow the Order of Operations. 00:04:17.169 --> 00:04:26.810 Remember, multiplication comes before addition—so Excel is going to multiply D5 by .075 first… 00:04:26.810 --> 00:04:31.720 then it will add D3 and D4, which isn't what we want. 00:04:31.720 --> 00:04:37.060 We want these three values to be added first. So to do that, we just need to put them in 00:04:37.060 --> 00:04:39.410 parentheses. 00:04:39.410 --> 00:04:43.790 Parentheses are often the best way of controlling which parts of the formula are calculated 00:04:43.790 --> 00:04:53.150 first. And when this formula is calculated… we can see that the answer is $20.56. 00:04:53.150 --> 00:04:57.930 Writing complex formulas really isn't that difficult once you know how they work. Just 00:04:57.930 --> 00:05:02.090 remember to use the Order of Operations, and you'll get the correct answer, every time.