1 00:00:01,200 --> 00:00:07,520 Hello, everyone, and this lecture, we will cover some of the basic mathematical functions in Excel, 2 00:00:09,270 --> 00:00:15,250 but before that, let us understand the small difference between formulas and functions in Excel. 3 00:00:16,650 --> 00:00:22,190 Most people used interchangeably, but there is a small difference between the two. 4 00:00:23,540 --> 00:00:28,520 A formula is an expression which calculates the value of a cell. 5 00:00:29,550 --> 00:00:31,010 It can be any expression. 6 00:00:32,040 --> 00:00:38,370 What we wrote in the last class, what a mathematical formulas so does is equal to a one plus B when 7 00:00:40,500 --> 00:00:48,090 a function is a predefined formula, functions allow you to use descriptive names to automatically apply 8 00:00:48,090 --> 00:00:48,940 formulas for you. 9 00:00:50,130 --> 00:00:56,750 For example, some average Intertrade functions, we will see how to use them. 10 00:00:58,080 --> 00:01:03,280 We use functions because they increase user productivity when working with Excel. 11 00:01:05,010 --> 00:01:06,480 So I hope the difference is clear. 12 00:01:06,730 --> 00:01:13,170 Anything that is calculated in a cell is a formula, but functions are predefined formulas of excel 13 00:01:13,410 --> 00:01:14,880 with descriptive names. 14 00:01:17,040 --> 00:01:20,620 Now let's see some of the mathematical functions we use in Excel. 15 00:01:22,920 --> 00:01:30,750 Let's first look at the data we have in this data we have scored of five students in three subjects 16 00:01:31,080 --> 00:01:33,060 math, science and English. 17 00:01:35,760 --> 00:01:40,110 We want to find out the total marks scored by these students. 18 00:01:41,550 --> 00:01:42,150 For this. 19 00:01:42,150 --> 00:01:45,810 We can simply write formula as earlier using the plus symbols. 20 00:01:46,380 --> 00:01:48,620 Or we can use the sum function. 21 00:01:50,370 --> 00:01:52,500 Let's use the sum function the same. 22 00:01:54,630 --> 00:02:03,540 We go to the set and start typing is equal to assume start the bracket. 23 00:02:07,300 --> 00:02:11,690 Within the bracket, a function takes parameters on which it operate. 24 00:02:12,550 --> 00:02:18,250 For example, for this function, we need to give all the numbers which we want to add. 25 00:02:19,720 --> 00:02:21,670 There are two ways to enter these numbers. 26 00:02:22,240 --> 00:02:26,140 One is to enter each cell individually separated by a comma. 27 00:02:27,040 --> 00:02:39,570 Let's do it this way for this role reversal, comma, second cell, comma, the third to close the record, 28 00:02:39,940 --> 00:02:40,430 enter. 29 00:02:41,950 --> 00:02:44,640 And this is the sum of these three numbers. 30 00:02:47,050 --> 00:02:55,060 If you click to tail again and the formula, but you can see the function applied instead of using cell 31 00:02:55,060 --> 00:03:01,210 reference, you could write the value of the cell also like some ninety five. 32 00:03:01,240 --> 00:03:01,900 Eighty three. 33 00:03:01,900 --> 00:03:02,440 Eighty one. 34 00:03:03,010 --> 00:03:04,500 That would give you the same result. 35 00:03:04,930 --> 00:03:12,570 But as discussed in the last lecture, using cell reference has some benefit for the student too. 36 00:03:12,790 --> 00:03:17,760 We will use the second method instead of entering each individual cell. 37 00:03:17,950 --> 00:03:20,250 I'm going to select the whole series. 38 00:03:20,260 --> 00:03:26,050 This time we start again with is equal to some. 39 00:03:29,940 --> 00:03:40,080 Within bracket, we use the mouse to select the entire range of cells that we want to add close to bracket 40 00:03:40,830 --> 00:03:41,740 and enter. 41 00:03:43,470 --> 00:03:49,980 Let's compare the difference between these two formulas and the formula bar and the first formula. 42 00:03:50,140 --> 00:03:53,730 You can see that the three cells are separated by commas. 43 00:03:55,350 --> 00:04:04,040 In the second formula, only the endpoints of the cities are written and between them is a colon. 44 00:04:06,060 --> 00:04:11,860 So this represents that everything between M6 and 006 is to be added. 45 00:04:14,190 --> 00:04:18,830 Can you see the power of some function over the normal addition formula using plus symbols? 46 00:04:20,400 --> 00:04:25,920 If I have to add one hundred numbers, I do not need to write the cell reference of hundred cells. 47 00:04:26,760 --> 00:04:29,900 Just select the entire cities within the same function. 48 00:04:32,660 --> 00:04:33,770 For the third student. 49 00:04:34,230 --> 00:04:36,680 I want you to notice another feature of Excel. 50 00:04:38,370 --> 00:04:46,170 When in a cell with type is equal to excel, expect that we are going to enter a function and it automatically 51 00:04:46,170 --> 00:04:51,650 starts recommending functions as we take the first few alphabet of the function name. 52 00:04:54,420 --> 00:04:58,620 If you do not want to function, do not bother, just continue typing. 53 00:04:59,160 --> 00:05:02,910 But if you want this adjusted function, press the tab key. 54 00:05:07,350 --> 00:05:12,030 It will auto complete function as well as it will start the bracket for you. 55 00:05:17,730 --> 00:05:23,610 Lastly, to calculate for other students, we will simply track the formula by going to the bottom right 56 00:05:23,610 --> 00:05:26,550 corner and extending it to other students, 57 00:05:29,490 --> 00:05:31,640 using any function is similar to this. 58 00:05:32,250 --> 00:05:38,210 Just take the name of the function, give the parameters in the bracket and enter that it. 59 00:05:39,420 --> 00:05:42,120 Let's cover other mathematical functions quickly now. 60 00:05:43,620 --> 00:05:49,900 Next, we look at the main function, which returns the minimum value from the input data. 61 00:05:51,480 --> 00:06:02,330 So to get the minimum score in maths, we go to this and start typing is equal to men that when we hit 62 00:06:02,330 --> 00:06:12,980 done this function will be selected in the parameters we need to select the range of cells to define 63 00:06:13,380 --> 00:06:15,540 what the score of student in maths. 64 00:06:17,340 --> 00:06:19,350 We closed the record and how it ended. 65 00:06:20,910 --> 00:06:27,510 You can see that forty five is the minimum score scored by any student in maths. 66 00:06:29,490 --> 00:06:33,000 We can extend this formula for science and English also. 67 00:06:36,560 --> 00:06:43,430 So 53 is the minimum score inside and 55 is the minimum score in English. 68 00:06:45,740 --> 00:06:47,720 Next, we look at the max function. 69 00:06:49,990 --> 00:06:53,780 Max function returns the maximum value from the input data. 70 00:06:55,420 --> 00:07:04,090 So to get maximum value of school in maths, we go to this cell and rate is equal to Max. 71 00:07:05,650 --> 00:07:12,490 If you want to select the second formula here, you can use the energy to go to second and then presto, 72 00:07:14,770 --> 00:07:19,420 no, we need to give the cities from which we have to select the maximum score. 73 00:07:22,780 --> 00:07:30,100 You can see that ninety nine is the maximum marks in that we can extend it for science and English also. 74 00:07:33,980 --> 00:07:43,260 Next is the average function, this function returns the average of the mean value of the input numbers. 75 00:07:44,330 --> 00:07:50,880 If you remember in the last lecture, we calculated average using usual mathematical operators. 76 00:07:52,010 --> 00:08:01,550 This time we use the function so to find average marks of each student, go to the cell. 77 00:08:04,150 --> 00:08:12,470 Type is equal to average when excel to just average function. 78 00:08:12,790 --> 00:08:14,620 You can hit the tab key. 79 00:08:17,720 --> 00:08:25,040 After this selected range of cells for which you want to calculate the average press, enter. 80 00:08:29,110 --> 00:08:31,420 Try to get the average for other student. 81 00:08:34,810 --> 00:08:42,190 Once we have calculated the average marks, we may want to assign ranks to students also as to which 82 00:08:42,190 --> 00:08:50,290 student scored the highest marks and who scored the least to rank these students versus their average 83 00:08:50,290 --> 00:08:57,070 score, go to the next level type is equal to rank average. 84 00:09:07,610 --> 00:09:15,190 The first barometer is the number whose rank you want to find, this will be the school of the student. 85 00:09:17,420 --> 00:09:23,450 The second parameter and this function is these cards from which rank is to be calculated. 86 00:09:24,200 --> 00:09:27,050 So this will be the scores of all these student. 87 00:09:31,500 --> 00:09:38,850 The last parameter is how should the ranking be done, whether highest should be ranked one or lowest 88 00:09:38,850 --> 00:09:46,590 should be ranked one, if you want to be ranked one such as the case here, the third parameter will 89 00:09:46,590 --> 00:09:47,130 be zero. 90 00:09:47,970 --> 00:09:49,810 For the other scenario, it will be one. 91 00:09:52,500 --> 00:09:53,430 So we enter zero. 92 00:09:56,080 --> 00:09:57,040 And press enter. 93 00:10:00,290 --> 00:10:08,540 You can see that the average score of student one makes the student one second in class. 94 00:10:12,600 --> 00:10:18,780 Important thing to note here is if we drag this formula down, the reference range will change. 95 00:10:20,380 --> 00:10:33,220 So so the differential range changes from B five to nine to P6 to Putin to Putin is not the correct 96 00:10:33,220 --> 00:10:34,030 reference range. 97 00:10:35,920 --> 00:10:41,910 In the last lecture, we discussed how to maintain the reference range to Loti reference range. 98 00:10:42,040 --> 00:10:43,930 We need to use dollar signs. 99 00:10:47,340 --> 00:10:51,230 To do this, go to the different values and symbols. 100 00:10:52,110 --> 00:10:54,230 There is a shortcut for adding dollars and bells. 101 00:10:54,240 --> 00:11:01,050 Also just click on DL on which you want to add dollars, symbols and press efford. 102 00:11:04,690 --> 00:11:07,990 Dollars and bills will be added to both row and column name. 103 00:11:10,850 --> 00:11:18,410 This will restrain reference values to change while dragging, so now let's extend it for other student. 104 00:11:23,390 --> 00:11:33,200 Now you can see that the differential remains from five to nine and student four is now at rank one 105 00:11:33,770 --> 00:11:38,360 and student five is the student with at least average bonks. 106 00:11:41,010 --> 00:11:50,310 Next, we move on to some product formula in this formula, we import two or more series of numbers 107 00:11:52,890 --> 00:11:58,860 and it returns the sum of product of individual elements of these two series of numbers. 108 00:12:00,600 --> 00:12:08,490 For example, if we want to assign weight age to the marks of math, science and English of these students 109 00:12:08,640 --> 00:12:15,240 before calculating their ranks, we have written the relativity of each object here. 110 00:12:17,490 --> 00:12:21,300 Now I want to find the total marks as per village. 111 00:12:21,630 --> 00:12:30,960 That is, I want to find the sum of 95 in the two plus 83 into one plus eighty one and two one point 112 00:12:30,960 --> 00:12:31,330 three. 113 00:12:32,160 --> 00:12:34,470 This will be the total marks for student one. 114 00:12:37,670 --> 00:12:41,810 To do this, we go to the cell and tape is equal to some product. 115 00:12:50,730 --> 00:13:02,340 Then we select the first series of numbers to select the second series of numbers and press enter, 116 00:13:05,220 --> 00:13:07,780 we get a value of three seventy eight point three. 117 00:13:08,760 --> 00:13:16,260 This is calculated by adding 95 into two plus eighty three into one plus eighty one and one point three. 118 00:13:20,170 --> 00:13:25,660 This can be dragged down, but before dragging it, remember, to use dollars and for debate is. 119 00:13:37,290 --> 00:13:38,570 We can track this don't know, 120 00:13:41,520 --> 00:13:46,320 and here we have the total marks of student as divided. 121 00:13:48,810 --> 00:13:57,540 Next, we look at brain function and function is used to generate random value between zero and one. 122 00:13:59,250 --> 00:14:00,920 This is an amazing function. 123 00:14:01,770 --> 00:14:03,690 It takes no input parameter. 124 00:14:04,260 --> 00:14:08,290 It just generates a number between zero and one randomly. 125 00:14:09,780 --> 00:14:11,490 But why do we need random numbers? 126 00:14:12,430 --> 00:14:19,800 Random numbers can be used to simulate Jossi range, like throwing a dice or tossing a coin. 127 00:14:21,340 --> 00:14:28,920 For example, say we want to randomly select a class monitor or a class representative out of these 128 00:14:28,920 --> 00:14:29,660 five students. 129 00:14:31,410 --> 00:14:34,970 I will generate one random number for each student. 130 00:14:36,180 --> 00:14:39,140 Whoever gets the largest number becomes classroom. 131 00:14:41,100 --> 00:14:46,560 So we go to this cell and type is equal to Aadi and the. 132 00:14:53,560 --> 00:15:01,090 And you can see random values generated for student one, we can drag it down for all the student. 133 00:15:03,810 --> 00:15:13,320 We can see that student five has the maximum value and now student five can be selected as the class 134 00:15:13,320 --> 00:15:14,130 representative. 135 00:15:18,300 --> 00:15:21,100 Next, we move to Iran between function. 136 00:15:23,460 --> 00:15:27,830 This will return random value between the range that we have provided. 137 00:15:29,280 --> 00:15:36,260 So this function takes two values as inputs and generates a random number between these two numbers 138 00:15:38,640 --> 00:15:41,400 in our example for students. 139 00:15:41,430 --> 00:15:51,480 Six, we can randomly assigned Moncks format by going to the cell and typing is equal to rank between. 140 00:15:57,750 --> 00:16:00,830 And giving the Rangers 40 and. 141 00:16:08,350 --> 00:16:18,250 The student has scored 56 marks in maths, we can extend this to science and English also, you can 142 00:16:18,250 --> 00:16:25,430 see that every time you change any cell, the value of RAM and then between get refreshed. 143 00:16:27,250 --> 00:16:34,250 So now the student has scored 88 in maths for doing science and 77 in English. 144 00:16:35,980 --> 00:16:37,240 Let's see this one more time. 145 00:16:37,780 --> 00:16:45,370 If I add any value to a new cell and press enter, these values are refreshed. 146 00:16:50,160 --> 00:16:58,800 Basically, doing any operation in the Excel sheet triggers a refreshing of these values to stop this 147 00:16:58,800 --> 00:16:59,530 from happening. 148 00:17:00,720 --> 00:17:03,290 We can use the paste as a value technique. 149 00:17:04,680 --> 00:17:13,080 So the thing is that these cells contain formulas, whereas we are interested in the value already generated 150 00:17:13,080 --> 00:17:18,070 by these formulas, the formulas have already served their purpose. 151 00:17:19,110 --> 00:17:22,680 So what we do is we copied the contents of these cells. 152 00:17:24,630 --> 00:17:27,420 The content of the cell is a formula. 153 00:17:28,020 --> 00:17:30,840 You can see that by pasting it in any other cell 154 00:17:34,020 --> 00:17:35,270 you can see in the formula. 155 00:17:35,280 --> 00:17:39,560 But when I pasted, it still contains the formula. 156 00:17:42,090 --> 00:17:44,850 But we want to keep the value and not the formula. 157 00:17:45,750 --> 00:17:49,320 So we copy these cells and paste them here. 158 00:17:52,810 --> 00:17:58,420 As values, this is the option for posting as values. 159 00:18:03,100 --> 00:18:10,120 This comes only when you have copied a formula, it basically gives the option to copy paste a formula 160 00:18:10,240 --> 00:18:13,540 as a formula or the value calculated by the formula. 161 00:18:15,880 --> 00:18:23,110 Now, as I have pasted the values, I have the randomly generated values with me which want to refresh 162 00:18:23,110 --> 00:18:25,150 every time I perform any operation. 163 00:18:32,270 --> 00:18:39,920 There are several other mathematical formulas, such as the ones in the sheet, we encourage you to 164 00:18:39,920 --> 00:18:41,220 try these on your own. 165 00:18:43,310 --> 00:18:53,930 You can also find a list of all the formulas in the Excel menu, click on the formula and select math 166 00:18:53,930 --> 00:18:54,710 and trigonometry. 167 00:18:58,010 --> 00:19:01,340 Here you can see the list of all the math related functions. 168 00:19:07,090 --> 00:19:08,350 That's all for this lecture. 169 00:19:09,130 --> 00:19:10,060 See you in the next one.