WEBVTT 1 00:00:01.589 --> 00:00:07.620 William Rosener: hello, this is Dr roadster this instructional video you'll learn about the function using Microsoft excel. 2 00:00:08.099 --> 00:00:15.389 William Rosener: The function is one of the most popular functions in excel and allows you to make a logical comparison between a value in what you expect. 3 00:00:15.780 --> 00:00:22.980 William Rosener: In fact, the function excel checks weather condition is met, he returns one value of true and another value of faults. 4 00:00:23.460 --> 00:00:27.420 William Rosener: let's start by taking a look at what you learned how to create in this video and then we'll walk through. 5 00:00:28.080 --> 00:00:35.400 William Rosener: Some going to kind of break this down into four little chunks of the first one here is going to be taking a look at it we've got a budgeted amount and the actual expenditure. 6 00:00:35.790 --> 00:00:58.470 William Rosener: We won't be using formulas to compute either over budget or within budget and then it's over budget how much over so in this case, if I was budgeted $800 and I spent 750 I would be within budget, however, price spent 850 and i'd be over budget by $50. 7 00:00:59.700 --> 00:01:13.110 William Rosener: The next one, is a we're going to be computing based on the quantity a price and stuff it's from one to 10 and the price per unit 37 to 2028 and 20 So if I put into here. 8 00:01:14.280 --> 00:01:18.690 William Rosener: Two times 30 would be 60 whereas if I put in 30. 9 00:01:20.490 --> 00:01:32.430 William Rosener: And 30 times 22 would be 616 next one, is a great maybe we'll start down here if greatest 72% soon begin to see. 10 00:01:33.510 --> 00:01:41.790 William Rosener: It was 82% be you know I tell you I could even do say a 62% that would be there. 11 00:01:42.930 --> 00:01:46.740 William Rosener: Then we're going to go ahead and compute in a Commission and here's. 12 00:01:47.220 --> 00:01:55.830 William Rosener: The formulas for those sort of them kind of the guidelines here if sales or less than $500 per month and the salesperson gets 7% Commission. 13 00:01:56.220 --> 00:02:10.440 William Rosener: For example, shelley and bread or both under $500 per month, if the sales are between 500 and 1000, for example, amy here she would be getting a 10% Commission and if sales are over. 14 00:02:11.790 --> 00:02:19.830 William Rosener: $1,000 and the salesperson would get 16% Commission nonetheless you see if you guys can figure that out, but i'm gonna get you the code will walk through these first three here. 15 00:02:21.330 --> 00:02:26.340 William Rosener: So, in the same area as you found a link to this video, you should also find. 16 00:02:27.600 --> 00:02:36.540 William Rosener: The tax kind of bug so you take a little time here but ominous still have you kind of copy and paste and then format this adding some color and some borders, etc there. 17 00:02:37.230 --> 00:02:45.180 William Rosener: But let's start off with its kind of get your day to enter their let's start off with computing some we got a budget amount and actual budget and actual. 18 00:02:45.630 --> 00:02:56.520 William Rosener: Expenditure announced compute the status and I don't want to do is take a look, is this value is a greater than that so i'm going to display over budget summer take a look. 19 00:02:57.090 --> 00:03:09.420 William Rosener: Take a look i'm gonna take a look at the information and be or is it greater than information in a form, and if so i'm going to be over budget. 20 00:03:10.590 --> 00:03:26.010 William Rosener: You can actually see if I can take a look right down here where's my test now working on that what happens if it's true i'm going to just like over budget notice those double quotes and what happens if it's false i'm just going to simply say within budget. 21 00:03:29.010 --> 00:03:30.300 William Rosener: And i'm going to close it now. 22 00:03:31.590 --> 00:03:39.390 William Rosener: So the budget $800 extra 720 once or over budget I tell you what, let me just go ahead and maybe grab that. 23 00:03:40.650 --> 00:03:54.870 William Rosener: And pasted here, just so you can clearly remember this font size, a little bit larger so here is my condition that evaluates to true i'm going to display the string that's why the double quotes otherwise i'm going to display this string right there. 24 00:03:56.310 --> 00:03:58.050 William Rosener: Okay oops. 25 00:04:06.450 --> 00:04:09.150 William Rosener: Okay, I got back to where it was. 26 00:04:10.530 --> 00:04:24.570 William Rosener: Now to compute the amount over reduce take a look and is this value greater than this little subtraction otherwise i'll display zero so right here i'm going to go ahead and take a look and i'm going to go if. 27 00:04:27.150 --> 00:04:28.290 William Rosener: it's my before. 28 00:04:30.360 --> 00:04:32.160 William Rosener: That greater than a form. 29 00:04:33.240 --> 00:04:39.210 William Rosener: And if so, i'm going to do subtraction the take before minus a four. 30 00:04:41.940 --> 00:04:44.730 William Rosener: Otherwise, I would simply display zero. 31 00:04:46.560 --> 00:04:58.830 William Rosener: Okay once i've got that I can grab these two and rag on down and maybe i'm the one that's even fix this come right up here looks like a need to add a bottom border. 32 00:05:01.500 --> 00:05:04.770 William Rosener: And let me just grab one of these disbursement harem copy. 33 00:05:09.060 --> 00:05:23.730 William Rosener: And come over here, maybe this font size, a little bit easier to read, here is my condition is deform greater than a for it so i'm values to true i'm going to do subtraction otherwise i'm display zero. 34 00:05:25.350 --> 00:05:29.310 William Rosener: Okay, the next one over here, so this will get both of these kind of have two conditions. 35 00:05:29.850 --> 00:05:36.780 William Rosener: If statement that I simply had either I want it over budget or within budget that's been gets a little bit more complicated when have three. 36 00:05:37.110 --> 00:05:56.910 William Rosener: kind of conditions on the statement and over here with the great I shouldn't have five a B, C D and F let's go ahead and start here with the total come right up here, I want to go eat was going to take information that's going to be G seven remember put this out to here. 37 00:06:02.700 --> 00:06:04.740 William Rosener: Okay, someone take a look. 38 00:06:05.760 --> 00:06:27.120 William Rosener: It was with the G seven so in this case initially what's going to take two and i'm going to take that two times, one of these three values and i'm going to take more pipeline that's the shift of the eight that little Star and i'm gonna take a look, if she seven is greater than. 39 00:06:28.650 --> 00:06:29.580 William Rosener: or equal to. 40 00:06:31.200 --> 00:06:37.740 William Rosener: Already, because i've got 30 and above or equal to 30 then i'm going to simply put in 22 here. 41 00:06:39.570 --> 00:06:46.050 William Rosener: And that's going to multiply by 22 and I could also put in G six right there i'll just go ahead and put the 22. 42 00:06:47.250 --> 00:06:56.520 William Rosener: Then we'll take a look is G seven is it greater than or equal to 11. 43 00:06:57.720 --> 00:07:02.850 William Rosener: Then we multiply it by the true condition, would be a 28. 44 00:07:03.900 --> 00:07:07.020 William Rosener: Otherwise, and multiply it by 30. 45 00:07:08.340 --> 00:07:12.450 William Rosener: And now i'm going to go ahead and close my parentheses. 46 00:07:13.980 --> 00:07:28.350 William Rosener: And sure enough if it's two were translated into $60 and we're just going to put in a 20 and it's working and i'll put it in a 30 and that she gave me 660 there, and let me just go ahead and grab this formula here. 47 00:07:34.260 --> 00:07:35.640 William Rosener: And come right over here. 48 00:07:37.380 --> 00:07:38.010 William Rosener: and paste. 49 00:07:39.540 --> 00:07:59.160 William Rosener: Some it take what's in G seven, and I want to multiply by one or three values i'm going to moto G seven is greater than 30 and multiply by 22 creatures G seven else would take a look is G seven greater than 11 I multiply by 28 and, finally, none of those conditions I multiply by 30. 50 00:08:00.420 --> 00:08:01.890 William Rosener: Okay last one i'm gonna give you is. 51 00:08:11.460 --> 00:08:17.370 William Rosener: yeah let me grab this kid right back thing a copy to copy that and come right back here. 52 00:08:18.840 --> 00:08:20.160 William Rosener: and paste. 53 00:08:22.110 --> 00:08:27.810 William Rosener: Okay, so the last one I want to work on is the grade and here we're going to go ahead and we're going to have five nations. 54 00:08:28.440 --> 00:08:41.970 William Rosener: And on the average we do this by a B, C D or out there some position my cursor in be 10 and i'll be looking at information in a tent so to start off doing, he was. 55 00:08:42.840 --> 00:08:55.830 William Rosener: willing to take what this statement it's a 10 is that greater than or equal to 90% and hope, I reminded this is a percent not evaluate so. 56 00:08:56.370 --> 00:09:14.370 William Rosener: Once I get this formula type i'll go ahead and show you make sure how to get that as a percent so is eaten greater than 90% of so I wanted to display and so notice i'm gonna put that within quotes if that's not the case, I can just simply it well, I could put B, C D or after. 57 00:09:15.420 --> 00:09:33.600 William Rosener: That I could close that but then I want to actually break it down so that they can that it would display either be seen not just bcb up and either one of those four values them so i'm going to take a look, is a 10 rather than equal to 80%. 58 00:09:34.860 --> 00:09:36.330 William Rosener: If so, i'm going to play a bit. 59 00:09:38.520 --> 00:09:40.530 William Rosener: That i'm gonna go ahead and take a look, yes. 60 00:09:42.360 --> 00:09:49.410 William Rosener: A 10 is greater than or equal to 70% whoops make sure I get my hip percent. 61 00:09:50.610 --> 00:09:52.470 William Rosener: Then i'm going to slay a seed. 62 00:09:54.480 --> 00:09:55.050 William Rosener: Finally. 63 00:09:56.520 --> 00:09:57.510 William Rosener: If a 10. 64 00:10:00.120 --> 00:10:01.560 William Rosener: is greater than equal to. 65 00:10:03.330 --> 00:10:06.090 William Rosener: 60% i'm going to display ad. 66 00:10:07.890 --> 00:10:09.360 William Rosener: Otherwise i'm going to split in half. 67 00:10:12.570 --> 00:10:17.010 William Rosener: And i'll go ahead and close my formulas down. 68 00:10:18.180 --> 00:10:30.780 William Rosener: So if I got 70 to two before I do that, let me just come right up here so make sure that this is not general make sure hey 10 has been formatted as percentage there. 69 00:10:32.310 --> 00:10:50.550 William Rosener: Because for take a look at this great i'm taking a look as a 10 not as aggressive than equal to the number 90 is greater than equal to 90% so in this case, if I come right over here and I change this will start down at the bottom and that's good for a 42% certain would be getting enough. 70 00:10:52.050 --> 00:10:55.230 William Rosener: It was say 62. 71 00:10:56.250 --> 00:10:57.390 William Rosener: would be to a Dean. 72 00:10:58.770 --> 00:11:02.460 William Rosener: And it was a 75. 73 00:11:04.530 --> 00:11:05.280 William Rosener: seem. 74 00:11:06.690 --> 00:11:10.500 William Rosener: Like if I been being in a 95. 75 00:11:11.520 --> 00:11:13.050 William Rosener: would give you and. 76 00:11:14.250 --> 00:11:18.210 William Rosener: me and i'm going to go ahead and have come to the code here for the first kind of three chunks there. 77 00:11:18.600 --> 00:11:26.190 William Rosener: And once you work on the Commission and it's going to be kind of very similar to this one over here, in fact, because we had kind of like three. 78 00:11:26.670 --> 00:11:30.750 William Rosener: three possibilities, so these had two possibilities on the effort either. 79 00:11:31.230 --> 00:11:44.640 William Rosener: over budget or within then can again down here when we take a look at the sales and I think haven't looked at these three you should be able to handle this with her, but hopefully in this video you'll learn more about the if statement and now catch the next video thanks time.