1 00:00:00,180 --> 00:00:05,130 Now it's time to highlight some math functions, to help with handling errors and creating some fairly 2 00:00:05,130 --> 00:00:06,810 common computational adjustments. 3 00:00:07,470 --> 00:00:09,180 Let's start with the divide function. 4 00:00:09,600 --> 00:00:11,580 The divide function is fairly simple. 5 00:00:11,880 --> 00:00:13,140 It divides two numbers. 6 00:00:13,290 --> 00:00:15,590 But don't we already do that with the forward slash? 7 00:00:15,900 --> 00:00:17,010 So why use a function? 8 00:00:17,520 --> 00:00:22,440 As you probably know, dividing by zero is a problem that almost always requires some type of error 9 00:00:22,440 --> 00:00:22,920 handling. 10 00:00:23,280 --> 00:00:25,040 You're probably familiar with the formula. 11 00:00:25,050 --> 00:00:32,100 If A equals zero zero comma be divided by a C, yes, you could write it that way index every time, 12 00:00:32,220 --> 00:00:36,950 but then you need to deal with context in what is a and what if A is an aggregate and it's a whole thing. 13 00:00:37,500 --> 00:00:42,870 So instead there's the divide function that ties the error handling into a nice little function for 14 00:00:42,870 --> 00:00:43,110 you. 15 00:00:43,440 --> 00:00:46,530 Let's recreate our average monthly wage column on our labor table. 16 00:00:46,530 --> 00:00:52,740 With it I can add a new column, open divide function and see that there are three inputs the numerator, 17 00:00:52,770 --> 00:00:55,530 the denominator and then the value that I should use. 18 00:00:55,770 --> 00:01:02,220 If there is an error, I use employee wages of the numerator employee count as the denominator and zero 19 00:01:02,220 --> 00:01:03,510 for my alternate result. 20 00:01:03,810 --> 00:01:07,800 When I close the function, I suddenly have a recreation of our average wages column. 21 00:01:08,520 --> 00:01:14,220 I recommend using the divide function every time that you use division over using the standard division 22 00:01:14,220 --> 00:01:20,250 sign simply because more data sets will have a divide by zero issue than not to go along with this divide 23 00:01:20,250 --> 00:01:25,370 function are two functions that I consider very important and useful to no manipulation. 24 00:01:25,530 --> 00:01:27,330 The floor and ceiling functions. 25 00:01:28,080 --> 00:01:33,240 These take a number and take them to the nearest multiple that you've given the function based upon 26 00:01:33,240 --> 00:01:34,830 the direction of the function. 27 00:01:35,100 --> 00:01:38,880 So the ceiling function rounds up and the floor function rounds down. 28 00:01:39,330 --> 00:01:44,280 The most common use of these is to simply get the closest integer in any given direction. 29 00:01:44,490 --> 00:01:46,160 However, there are other uses too. 30 00:01:46,680 --> 00:01:51,720 Let's start with the integer scenario with our average wage and say that I want to define the average 31 00:01:51,720 --> 00:01:53,700 wage rounded down to the nearest dollar. 32 00:01:54,060 --> 00:01:56,730 I can add a new column and offer my floor function. 33 00:01:57,270 --> 00:02:01,920 The first input is the actual number that I want to do the rounding on, and the second input is the 34 00:02:01,920 --> 00:02:07,250 level of significance that I want to round to think about the second parameter as being the closest 35 00:02:07,260 --> 00:02:08,970 multiple value to find. 36 00:02:09,360 --> 00:02:15,330 So floor average monthly wage come one all around the average monthly wage down to the nearest dollar. 37 00:02:16,430 --> 00:02:22,130 So five eighty one point zero four becomes five eighty one and six sixty five point seventy six becomes 38 00:02:22,130 --> 00:02:26,180 six sixty five, rounding down by one is nice, but what else can we do? 39 00:02:26,600 --> 00:02:32,210 Let's say I wanted to know how many quarters we needed for each of these after rounding a quarter is 40 00:02:32,210 --> 00:02:38,750 twenty five cents so I can take the floor of the average monthly wage minus this new column to the significance 41 00:02:38,750 --> 00:02:40,220 of zero point twenty five. 42 00:02:40,850 --> 00:02:47,750 This tells us what the closest number of cents is for each dollar rounded to twenty five cents. 43 00:02:48,290 --> 00:02:53,240 So now I'll wrap this with a revised function and divide by zero point twenty five to get an integer 44 00:02:53,240 --> 00:02:55,640 telling me how many quarters we need for each set. 45 00:02:56,340 --> 00:03:01,100 Now, this isn't perfect because I'm not handling exact values well, as you can see by the negative 46 00:03:01,100 --> 00:03:01,580 ones. 47 00:03:02,360 --> 00:03:05,360 So it seems to be working mostly right now. 48 00:03:05,360 --> 00:03:07,580 Obviously, this example is a bit contrived. 49 00:03:07,670 --> 00:03:11,210 So let's go look at our purchase orders table, which has a new tweak to it. 50 00:03:12,230 --> 00:03:16,700 In addition to the amount that we spent on purchase orders, I now have a quantity field. 51 00:03:17,090 --> 00:03:22,010 Let's imagine that we needed to calculate how many pallets of goods were involved with each purchase 52 00:03:22,010 --> 00:03:22,340 order. 53 00:03:22,580 --> 00:03:27,770 And then we know that every pallet can hold up to one hundred and forty four units for this. 54 00:03:27,770 --> 00:03:32,930 I'm going to use the ceiling function to round up since I have one hundred and forty five goods that'll 55 00:03:32,930 --> 00:03:33,980 need two pallets. 56 00:03:34,460 --> 00:03:41,750 So I'll do ceiling quantity comma one for for this texture quantity up to the nearest multiple of one 57 00:03:41,750 --> 00:03:42,890 hundred and forty four. 58 00:03:43,310 --> 00:03:47,690 We can see cases where we have one hundred and forty six and it rounds up to two hundred eighty eight. 59 00:03:49,420 --> 00:03:54,370 I'll add a divide function to this, and again, I'll divide by one hundred and forty four to get my 60 00:03:54,370 --> 00:04:00,310 number of pallets with a few more steps, you could end up constructing measures to help you figure 61 00:04:00,310 --> 00:04:04,690 out how much quantity you need to sell on average to take full advantage of two pallets. 62 00:04:05,770 --> 00:04:10,480 Or you could even find out how much shipping expenses you're wasting when you order one hundred and 63 00:04:10,480 --> 00:04:16,180 forty six units and have two pallets shipped to you, as you can see, these functions can be very useful 64 00:04:16,180 --> 00:04:18,220 for a wide variety of calculations. 65 00:04:18,790 --> 00:04:23,650 While I've demonstrated them using calculate columns, there are many uses for them and measures as 66 00:04:23,650 --> 00:04:24,010 well.