1 00:00:00,780 --> 00:00:07,440 In this video we're gonna be talking about building custom functions in Excel and functions are similar 2 00:00:07,440 --> 00:00:12,960 to the public subroutines and private subroutines that we've been writing in all of our other videos. 3 00:00:12,960 --> 00:00:19,350 The primary difference is that a function will actually return value as the output of the function. 4 00:00:19,350 --> 00:00:25,350 And this means we can call our functions in our actual cells on Excel rather than building a subroutine 5 00:00:25,350 --> 00:00:28,290 that has to be triggered by a button or something like that. 6 00:00:28,290 --> 00:00:38,280 So for example if we wanted to create a column over here that says hourly rate and then maybe we want 7 00:00:38,280 --> 00:00:41,610 to have another column that says hours worked 8 00:00:44,260 --> 00:00:46,740 and a third column that says total pay 9 00:00:49,900 --> 00:00:56,560 and we'll go ahead and we'll just pick some numbers for these and we'll say this person worked only 10 00:00:56,590 --> 00:00:57,480 eight hours. 11 00:00:57,490 --> 00:01:01,370 This person worked 10 hours so they should probably get a little bit of overtime pay. 12 00:01:01,540 --> 00:01:05,590 And this person worked 12 hours so they should definitely be getting overtime pay. 13 00:01:05,650 --> 00:01:11,200 So it would be a little hard for us to right now just use regular functions here and function again 14 00:01:11,200 --> 00:01:14,160 is something like If I typed some here. 15 00:01:14,350 --> 00:01:20,560 This sum is one of excels internal functions that they already have written so we can build our own 16 00:01:20,560 --> 00:01:25,930 function that will serve a purpose like some does but we can call it out with all of the custom code 17 00:01:25,960 --> 00:01:28,890 in it to execute whatever we want it to execute. 18 00:01:28,900 --> 00:01:33,490 So in this case it would be hard for us to determine what this person's total pay is compared to this 19 00:01:33,490 --> 00:01:39,340 person because this person has worked two hours of overtime and maybe they get a little bit extra pay 20 00:01:39,340 --> 00:01:41,590 for working that two hours of overtime. 21 00:01:41,590 --> 00:01:47,110 So this wouldn't be as easy as just saying you know we're gonna take their hourly rate times their hours 22 00:01:47,110 --> 00:01:54,060 worked because if we do this this person isn't getting any extra pay for their extra time that they've 23 00:01:54,060 --> 00:01:54,360 worked. 24 00:01:54,360 --> 00:01:58,080 So instead we're gonna build a custom function that will do this for us. 25 00:01:58,260 --> 00:02:03,860 So we're gonna go over to the developer tab and open up our visual basic window. 26 00:02:03,900 --> 00:02:10,620 Now if you don't have a module in your in your code already you can go to insert module or if you do 27 00:02:10,620 --> 00:02:15,990 have a module you can double click on it or right click you code to open it up and you'll notice I'm 28 00:02:15,990 --> 00:02:22,320 working in an excel file that we built previously when we were building forms and learning about forms. 29 00:02:22,320 --> 00:02:29,040 You can just keep these three columns and create your own excel file if you don't want to have to work 30 00:02:29,040 --> 00:02:33,150 from a pre-existing file on this will still work the same way. 31 00:02:33,150 --> 00:02:39,540 So what we're going to type is function and notice we're using function instead of any sort of subroutine 32 00:02:39,570 --> 00:02:44,320 because again we're interested in creating a custom function not a subroutine. 33 00:02:44,670 --> 00:02:46,650 Then we'll type the name of the function. 34 00:02:46,650 --> 00:02:49,440 So I'm just gonna call this full pay. 35 00:02:49,470 --> 00:02:52,560 And now we're gonna create the inputs for this function. 36 00:02:52,560 --> 00:03:01,800 So first I'm going to create a variable we'll say t as double and this will be how much they get paid 37 00:03:01,800 --> 00:03:02,390 per hour. 38 00:03:02,580 --> 00:03:08,100 And then I'm going to create another variable each as double which will be the number of hours that 39 00:03:08,100 --> 00:03:09,360 they worked. 40 00:03:09,360 --> 00:03:14,640 And now the key to remember about functions is this full pay is actually going to be a variable. 41 00:03:14,640 --> 00:03:17,520 This is going to be the output of your function. 42 00:03:17,520 --> 00:03:20,820 And so we need to tell the system what is for pay. 43 00:03:20,820 --> 00:03:24,150 And in this case we're going to say for pay is also double. 44 00:03:24,150 --> 00:03:27,270 And this just means no we're going to be doing math with this. 45 00:03:27,270 --> 00:03:30,090 So we want to make sure we carry our decimals and all of that. 46 00:03:30,090 --> 00:03:35,490 And that's why I'm using double and you can go back and check previous video where we talked about variable 47 00:03:35,490 --> 00:03:40,210 types if you're not familiar with double versus string or anything like that. 48 00:03:40,230 --> 00:03:45,330 Now the first thing we want to check we want to check if the number of hours that they worked which 49 00:03:45,330 --> 00:03:53,740 will be h is greater than 8 and I'm going to go ahead and put my and if so I don't forget it later so 50 00:03:53,740 --> 00:04:00,660 if they did work more than eight hours then we know they should get overtime pay so the first thing 51 00:04:00,660 --> 00:04:05,580 we're gonna do is we're gonna figure out what their pay should have been for the regular eight hours 52 00:04:05,580 --> 00:04:08,070 that they worked their first eight hours. 53 00:04:08,070 --> 00:04:15,480 So we'll say full pay initially is just gonna be eight times the the hourly rate that they make which 54 00:04:15,480 --> 00:04:16,600 is our variable. 55 00:04:17,400 --> 00:04:21,960 So now I know I've accounted for their first eight hours of pay and I only want them to get overtime 56 00:04:21,960 --> 00:04:24,470 pay for the extra hours that they worked. 57 00:04:24,510 --> 00:04:32,390 So now I'm going to just that full pay value and I'm going to say OK I want you to keep that value that 58 00:04:32,390 --> 00:04:36,620 we just calculated how much they would have paid for their regular eight hours. 59 00:04:36,740 --> 00:04:42,810 And this time I'm going to add in their total number of hours that they worked. 60 00:04:42,810 --> 00:04:45,200 Subtract out the initial eight hours. 61 00:04:45,240 --> 00:04:49,320 So now I know I'm looking at only the extra hours that they worked that day. 62 00:04:49,560 --> 00:04:51,710 I'm going to times that by their hourly pay. 63 00:04:52,080 --> 00:04:55,320 And this time I'm going to give them a little bump and we'll use one point five. 64 00:04:55,320 --> 00:04:56,660 Just as an example. 65 00:04:56,700 --> 00:05:03,340 So they should be giving extra pay half of their hourly rate extra for each hour that they worked overtime. 66 00:05:03,450 --> 00:05:06,140 Close out my parentheses and I'll hit enter. 67 00:05:06,150 --> 00:05:11,370 Now I am going to use an LS here and that's because we also want to account for if they just worked 68 00:05:11,370 --> 00:05:13,770 a regular eight hours so we'll see regular pay. 69 00:05:14,610 --> 00:05:22,140 And this time our full pay would be just the regular pay rate times the number of hours that they worked 70 00:05:25,630 --> 00:05:31,090 so all in all this function should look at how many hours they worked initially if they worked more 71 00:05:31,090 --> 00:05:33,280 than eight hours we're gonna give them overtime pay. 72 00:05:33,460 --> 00:05:38,770 So first we're gonna figure out what they should be paid for the first eight hours they worked then 73 00:05:38,770 --> 00:05:43,600 we're gonna figure out what they should be paid for the extra hours that they worked and add that to 74 00:05:43,600 --> 00:05:45,700 the original eight hours worth of pay. 75 00:05:45,970 --> 00:05:50,980 If they didn't work more than eight hours so if they worked eight hours or less then we're just going 76 00:05:50,980 --> 00:05:55,180 to take their pay rate and multiply it by the number of hours that they worked. 77 00:05:55,210 --> 00:06:01,760 So now I'm going to go ahead and save this and go back to my cell file now in the total pay column here. 78 00:06:01,900 --> 00:06:03,260 I'm going to type equals. 79 00:06:03,850 --> 00:06:09,860 And then the name of our custom function which was full pay and we'll see it actually does pop up in 80 00:06:09,860 --> 00:06:16,130 this function list so we can see Excel does recognize that we have created this custom function. 81 00:06:16,130 --> 00:06:23,460 Now I'm going to go ahead and select the hourly rate and then the hours that they worked in that order 82 00:06:23,490 --> 00:06:30,750 because that's the order that I built into that custom function and hit enter and we can see this person 83 00:06:30,870 --> 00:06:33,720 only worked eight hours so they're not getting any overtime pay. 84 00:06:33,720 --> 00:06:39,690 It's just ten times eight which is 80 but this person did work extra hours they worked two extra hours 85 00:06:39,950 --> 00:06:44,310 so we will look at their hourly rate we can notice they're actually making a little bit more than just 86 00:06:44,370 --> 00:06:45,760 10 times 15. 87 00:06:45,900 --> 00:06:50,670 They're making extra pay to account for those extra hours that they worked and the same with this person. 88 00:06:50,670 --> 00:06:56,250 And if I come back through and I change this to say 10 we can see their pay will adjust automatically. 89 00:06:56,250 --> 00:07:01,710 So we've built this custom function now to go ahead and account for whether or not our employees worked 90 00:07:01,850 --> 00:07:06,350 a regular eight hour day if they worked overtime and automatically calculate that for us. 91 00:07:06,660 --> 00:07:11,640 And we can build these custom functions and take advantage of them to avoid having to write multiple 92 00:07:11,640 --> 00:07:18,600 if statements or nested if statements into our formula box up here and avoid creating huge nasty formulas 93 00:07:18,600 --> 00:07:21,500 that exist in the formula bar that are hard to keep track of. 94 00:07:21,510 --> 00:07:27,000 Instead we can just create those custom functions in VBA and automatically run all those calculations 95 00:07:27,060 --> 00:07:28,130 in the background. 96 00:07:28,140 --> 00:07:31,070 So building custom functions is a super helpful tool. 97 00:07:31,080 --> 00:07:36,120 You should definitely take advantage of custom functions to make sure your formulas aren't huge and 98 00:07:36,120 --> 00:07:41,030 overwhelming and you can build in some error proofing into your custom functions as well. 99 00:07:41,030 --> 00:07:46,020 So that's what we learned about in this video was building custom functions and how helpful they can 100 00:07:46,020 --> 00:07:46,260 be.