WEBVTT Kind: captions Language: en 00:00:11.070 --> 00:00:16.430 We've already talked about how to create simple formulas that only have one type of operation. 00:00:16.430 --> 00:00:18.580 But sometimes you'll have to create a more complex 00:00:18.580 --> 00:00:25.200 formula that has more than one type of operation, for example addition and multiplication. 00:00:25.200 --> 00:00:27.890 To do this, you'll need to understand how Excel is 00:00:27.890 --> 00:00:30.269 calculating the answer. 00:00:30.269 --> 00:00:32.550 Excel uses a set of rules called the Order of 00:00:32.550 --> 00:00:34.940 Operations, which you might remember from math 00:00:34.940 --> 00:00:36.280 class. 00:00:36.280 --> 00:00:38.600 The Order of Operations determines which parts of 00:00:38.600 --> 00:00:40.710 the formula to calculate first. 00:00:40.710 --> 00:00:43.870 The very first thing that you calculate is items 00:00:43.870 --> 00:00:45.460 in parentheses. 00:00:45.460 --> 00:00:50.120 Then, if you have any exponents you'll calculate those. 00:00:50.120 --> 00:00:57.270 Next is multiplication and division, and lastly addition and subtraction. 00:00:57.270 --> 00:01:01.960 Excel knows to use the Order of Operations whenever it calculates a complex formula, 00:01:01.960 --> 00:01:03.310 and it's important that you remember it 00:01:03.310 --> 00:01:07.380 so that you can create formulas that will give you the correct answer. 00:01:07.380 --> 00:01:13.039 To remember the order, you can just use the acronym "PEMDAS," or, to make it even easier, 00:01:13.039 --> 00:01:14.039 just 00:01:14.039 --> 00:01:16.180 remember the phrase, "Please Excuse My Dear Aunt 00:01:16.180 --> 00:01:18.500 Sally." 00:01:18.500 --> 00:01:22.530 Let's go over each of these in a little more detail. 00:01:22.530 --> 00:01:26.140 In this formula, 5+2 is in parentheses, so we'll 00:01:26.140 --> 00:01:29.840 calculate that first to get 7, and then we can 00:01:29.840 --> 00:01:37.439 just multiply 3 times 7 to get 21. 00:01:37.439 --> 00:01:40.340 This formula contains an exponent - 3 to the power 00:01:40.340 --> 00:01:49.780 of 2 - and we'll need to calculate that first before doing the addition. 00:01:49.780 --> 00:01:51.940 If you have multiplication or division, that will 00:01:51.940 --> 00:01:53.070 come next. 00:01:53.070 --> 00:02:02.250 Here, we multiply 2*4 to get 8, and 9-8 equals 1. 00:02:02.250 --> 00:02:04.200 Now multiplication and division are considered to 00:02:04.200 --> 00:02:06.380 be at the same level. 00:02:06.380 --> 00:02:11.790 That means if you have both of them in your formula, they're calculated from left to right. 00:02:11.790 --> 00:02:14.700 In this example, we'll do the division first and 00:02:14.700 --> 00:02:17.900 then the multiplication. 00:02:17.900 --> 00:02:20.010 If we wanted the multiplication to happen first, 00:02:20.010 --> 00:02:22.290 we could just put it in parentheses, or we could 00:02:22.290 --> 00:02:24.670 rearrange the formula so the multiplication was on 00:02:24.670 --> 00:02:26.400 the left. 00:02:26.400 --> 00:02:30.980 Finally, we have addition and subtraction. 00:02:30.980 --> 00:02:33.190 Just like multiplication and division, these are 00:02:33.190 --> 00:02:36.109 on the same level, so we calculate them from left 00:02:36.109 --> 00:02:38.720 to right. 00:02:38.720 --> 00:02:44.230 OK, now let's put them all together. 00:02:44.230 --> 00:02:46.981 This formula may look really complicated, but 00:02:46.981 --> 00:02:49.169 we're just going to go step-by-step through the 00:02:49.169 --> 00:02:54.019 Order of Operations, and you'll see it's actually not that bad. 00:02:54.019 --> 00:02:55.950 We'll start with the parentheses. 00:02:55.950 --> 00:03:00.480 We'll calculate each of these to get 2 and 3, and 00:03:00.480 --> 00:03:02.360 you can see that the formula's already starting to 00:03:02.360 --> 00:03:03.840 look a little bit simpler. 00:03:03.840 --> 00:03:08.660 Next, we'll look to see if there are any exponents in this formula. 00:03:08.660 --> 00:03:11.940 There is one - 2 to the second power - and when we 00:03:11.940 --> 00:03:14.650 calculate it it becomes 4. 00:03:14.650 --> 00:03:22.980 Next, we'll do the multiplication and division going from left to right. 00:03:22.980 --> 00:03:28.950 And now all that's left is addition, and that gives us our answer, which is 8.75, 00:03:28.950 --> 00:03:30.790 and that's the same answer that Excel would give 00:03:30.790 --> 00:03:34.350 you if you entered in that formula. 00:03:34.350 --> 00:03:38.260 It's important to keep these rules in mind when 00:03:38.260 --> 00:03:40.070 you're creating a formula. 00:03:40.070 --> 00:03:45.870 In this example, I'd like to add these three values, and find out how much the tax will 00:03:45.870 --> 00:03:46.870 be. 00:03:46.870 --> 00:03:51.850 And let's say the tax rate is 5.5%. 00:03:51.850 --> 00:03:54.430 I'll start by typing the equals sign, which is how 00:03:54.430 --> 00:03:56.820 you always start a formula. 00:03:56.820 --> 00:04:08.500 I'll add D4 plus D5 plus D6, and then I'll multiply it by .055, which is 5.5%. 00:04:08.500 --> 00:04:12.570 Now, is this formula going to give us the correct 00:04:12.570 --> 00:04:13.700 result? 00:04:13.700 --> 00:04:17.100 No, it's not. 00:04:17.100 --> 00:04:24.780 Multiplication comes before addition, so Excel will first multiply D6*.055, 00:04:24.780 --> 00:04:29.980 and then it will add D4 and D5, and that's actually not what we want. 00:04:29.980 --> 00:04:32.810 We want these three values to be added first, 00:04:32.810 --> 00:04:36.310 and we can do that by just putting them in parentheses. 00:04:36.310 --> 00:04:40.850 Parentheses are often the best way of controlling which parts of the formula are calculated 00:04:40.850 --> 00:04:43.510 first. 00:04:43.510 --> 00:04:46.090 And when this formula is calculated, the answer is 00:04:46.090 --> 00:04:50.320 4 dollars and 65 cents. 00:04:50.320 --> 00:04:52.540 So whenever you're doing a complex formula, you'll 00:04:52.540 --> 00:04:54.630 need to think about the Order of Operations to 00:04:54.630 --> 00:04:57.120 make sure that your formulas give you the correct 00:04:57.120 --> 00:05:19.320 answer.