1 00:00:04,440 --> 00:00:08,280 In this section we're going to take a look at some essential Excel functions. 2 00:00:08,760 --> 00:00:12,150 But before I go over the topics we cover in this section 3 00:00:12,150 --> 00:00:15,570 let me introduce you to Excel's function library. 4 00:00:15,570 --> 00:00:19,670 This library has a list of all the functions that exist in Excel. 5 00:00:19,880 --> 00:00:25,710 So if you're looking for something specific whether Excel has this function or not you're likely going 6 00:00:25,710 --> 00:00:30,130 to be googling it. But you can also search the function library for it. 7 00:00:30,300 --> 00:00:34,860 Just click on the formulas tab and you see it right here. 8 00:00:34,860 --> 00:00:37,950 The functions are organized in these categories. 9 00:00:37,950 --> 00:00:41,700 Let's take a look at what we have inside the math functions here. 10 00:00:41,700 --> 00:00:45,420 There is a lot of functions and I don't use all of these 11 00:00:45,450 --> 00:00:47,230 obviously, I use some. 12 00:00:47,250 --> 00:00:53,480 For example, the aggregate function. Down here we're going to have matrix multiplication, the mod function. 13 00:00:53,480 --> 00:01:00,690 So depending on the analysis I'm doing I might need this, round, round down, and round up as well. 14 00:01:00,690 --> 00:01:05,730 Now let's say you see Round and you're interested to find out more about this function. 15 00:01:05,760 --> 00:01:10,130 If you just hover your mouse over it you get some additional information. 16 00:01:10,140 --> 00:01:14,310 This function rounds a number to a specified number of digits. 17 00:01:14,310 --> 00:01:16,160 Now let's just click on it. 18 00:01:16,200 --> 00:01:18,810 We get the insert function wizard. 19 00:01:18,840 --> 00:01:20,670 Notice what else happened. 20 00:01:20,730 --> 00:01:26,330 We see the round function in the formula bar it's helping us write our formula. 21 00:01:26,520 --> 00:01:28,770 What we need is a number. 22 00:01:28,770 --> 00:01:30,660 Usually this would be a cell reference. 23 00:01:30,660 --> 00:01:32,740 In this example let's just type it in. 24 00:01:32,850 --> 00:01:35,220 Then we need the number of digits. 25 00:01:35,310 --> 00:01:37,440 So let's say you don't know what this means. 26 00:01:37,440 --> 00:01:38,880 You want more information. 27 00:01:39,030 --> 00:01:40,600 You get it down here. 28 00:01:40,620 --> 00:01:46,230 This is the number of digits to which you want to round, negative rounds to the left of the decimal point, 29 00:01:46,610 --> 00:01:48,690 zero to the nearest integer. 30 00:01:48,690 --> 00:01:51,900 In this case let's say I want to round to the nearest integer. 31 00:01:51,900 --> 00:01:53,370 I'm going to put in zero. 32 00:01:53,370 --> 00:01:54,810 That's my answer. 33 00:01:54,810 --> 00:01:55,700 That's my formula 34 00:01:55,710 --> 00:01:58,560 getting completed for me, click on okay 35 00:01:58,710 --> 00:02:00,800 and I have my formula. 36 00:02:00,810 --> 00:02:04,230 Now let's say I had the number written in this cell. 37 00:02:04,230 --> 00:02:07,690 I know that the function is Round so I start typing it in. 38 00:02:07,710 --> 00:02:08,930 I select the number 39 00:02:09,449 --> 00:02:16,500 and now for the number of digits and like what was that? I can click on the FX right here. 40 00:02:16,580 --> 00:02:17,970 Notice what happens. 41 00:02:17,970 --> 00:02:23,590 It takes me back to the insert function wizard and it's already filled up the parts I've already done. 42 00:02:23,910 --> 00:02:28,640 I can find out more about the number of digits here and complete my formula in this view. 43 00:02:28,800 --> 00:02:33,450 Another way you can use this is to search for a function. 44 00:02:33,840 --> 00:02:37,060 So let's say you want to see the different count functions Excel has available. 45 00:02:37,080 --> 00:02:43,310 I can click on this or go to formulas and click on insert function. 46 00:02:43,380 --> 00:02:46,100 You basically come to the same view. 47 00:02:46,290 --> 00:02:50,760 Now we can search for a function, so let's just type in count and press enter. 48 00:02:50,760 --> 00:02:54,210 These are the different Count formulas that we have. 49 00:02:54,210 --> 00:02:57,740 What if I wanted something else? Count by criteria, press enter. 50 00:02:57,760 --> 00:03:01,410 I have the CountIfs function. 51 00:03:01,460 --> 00:03:06,510 Now I'm just going to click on okay and I can take a look at the different arguments this function needs 52 00:03:06,510 --> 00:03:13,810 If you're just getting started with formulas and you prefer the wizard interface 53 00:03:14,050 --> 00:03:14,980 use it. 54 00:03:15,190 --> 00:03:21,210 If you haven't used a formula in a long time and you want to get a bit more information about the arguments 55 00:03:21,210 --> 00:03:23,650 in that formula use the wizard. 56 00:03:23,650 --> 00:03:30,460 Otherwise get used to writing formulas directly in the formula bar. It's faster and it's also easier 57 00:03:30,490 --> 00:03:37,270 to nest formulas. In this section we're going to be covering the functions I showed you, the count function, 58 00:03:37,750 --> 00:03:40,940 counting cells if they match a criteria. 59 00:03:41,050 --> 00:03:44,630 Different methods to round, Round up, round down. 60 00:03:44,630 --> 00:03:53,610 You'll also learn how to sum values by criteria, so you only sum the cells that match a certain condition. 61 00:03:53,620 --> 00:04:03,010 Also how to get the max and min values in a data set and also how to get the max and min based on conditions. 62 00:04:03,010 --> 00:04:07,090 Now why did we cover functions that account for conditions. 63 00:04:07,090 --> 00:04:13,690 The reason is a lot of the analysis that you're going to be doing will not be simple. 64 00:04:13,720 --> 00:04:19,899 Sometimes you just need to sum the cells or get the maximum value of a list of numbers but sometimes 65 00:04:19,990 --> 00:04:21,940 you need to get the max value 66 00:04:21,940 --> 00:04:28,960 If, let's say company is A and Product is B. That's when you need these special functions. 67 00:04:29,170 --> 00:04:35,350 We'll then take a look at how you can work with dates and time in Excel and how you can correctly make 68 00:04:35,350 --> 00:04:42,910 calculations for sensitive topics like total time worked in a week. After this we are going to take a look at 69 00:04:42,970 --> 00:04:50,340 how you can handle errors in your reports and then you Master two very important Excel functions. 70 00:04:50,500 --> 00:04:56,560 "IF" and "VLOOKUP". So you see Excel has a lot of functions but in your work you're probably going to end up 71 00:04:56,560 --> 00:05:01,970 using around 10 functions a lot and maybe another 10 from time to time. 72 00:05:01,990 --> 00:05:04,570 Let's get started with our first set of functions.