Objectives by the end you should be able to use the average mode and median functions use Excel for area and volume calculations, use the round down round up and round function use the mid and I nt functions use the RAND and rank between functions. Excel can be very effectively used to perform a number of mathematical calculations. Let us look at some of the functions in Excel that are capable of doing so. Let's consider an example. In this table, I have written a number of companies and their respective turnover. We will try to find out the average turnover of these companies.
Average or mean can be calculated by adding all the values in the observation and dividing by the number Have observations in our case, we would be adding all the values, I am just using an AutoSum function and dividing this obtained value by the number of observations, that is 13 This is the average of the turnover. We could have also done this using an inbuilt formula in Excel, which is average, let us write over here equal to average. Now, these numbers could have been selected one by one, or a better way to do it is by selecting all these cells together, closing the bracket and hit on Enter. Now, we see that this value and this value are the same. Let us try to find out the mode of these observations. A mode is nothing but a value that has occurred maximum number of times in this data, Excel can directly find out that particular value for us, let us write mode and select all the cells just as we did before close bracket and enter.
From the observation also, we can see that 22,000 has appeared twice in this data and none of the other values have appeared twice and hence, the output is 22,000. median is nothing but the central value of the data. That is, if we organize this data in ascending order, we have done from smallest to largest, then the mid value of the observation is a median. In our case, we have 13 observations. So the median value could be seven Observation hands 1234567 This will be our median. Let us try to find that directly from the formula equal to median and select all the numbers just as we did before close bracket and enter. So, we see that Excel has very well calculated average mode and median.
Now, let us see how we can use Excel to calculate area, surface area, circumference and volume. And for that, we need to know the formulas for each of these for the object that we want to calculate. Let us take an example. Suppose, we want to find area of a square, we know that the formula for it is the side square let us consider that we go The site values as 40. So in order to find the area of this we would write equal to, we'll select the cell, which contains the side. And then race to that is by holding on the shift key.
And we'll press numerical key six. After that, we'll write two. So this shows that we are raising this side to the power of two. And when we hit on Enter, we get the value of area. Now similarly, we could have calculated the area of a rectangle by multiplying the length and the breadth, there is a length is 25. And the breath is 13.
So we'll write the formula equal to selecting the cell that contains the length into the cell that contains the breath and hit on Enter. So This is how we can find the area of rectangle. Similarly, we could find out value of perimeter volume circumference of the object that we want to find. While performing mathematical calculations, we know that we may not get an output like this or we may not get an output that is an integer always. We may have values in decimal, just like this 45.67249 I am just taking a random number. Now if we get such a value, and we would like to use only a number of decimal places, then Excel has certain functions for that.
If we want to round this digit to the lower value, then we could use the round down and select the number mentioned the number of digits that we would like to round down, and hit on Enter. So as we can see, it has round down this number to one numeric digit. We could also round down the same number to two digits, and we get this value. What if we want to round up the number that can also be done using the roundup function, select the number mentioned the number of digits that we would like to round up and hit on Enter. We can also use a round function and we can mention the number of digits that we would like to round. Now what this round function does is it takes the number of digits that we want from the selected cell and checks the digits in the decimal place, the next digit if it is more than five, it will convert the previous digit that is for in our case to the next digit.
Hence, it has returned 6725 if this was four, it could have mentioned 6724 itself. So, from five onwards, it converts the number to the next value. Now, let us look at some very interesting functions, mod and end. Let me explain you by giving an example. Suppose, we have time and seconds, let's say 523 seconds, and we want to convert this into minutes and seconds. Now, how do we go about it?
One way is by dividing the time and seconds by 60 you can get the value of minutes but this We'll give some fractional value also. How do we find out seconds from this, we would have to write minutes minus the part, which is before decimal point, and we get the seconds, but this is all infractions. We can display this into an integer by using these buttons over here, that decrease in the decimal point. But as we can see, it is not an accurate value. Excel has two very interesting functions called integer and model list. Let us see how to use them.
What in teacher does is it will convert this divide by 60 and it will take only the integer part and give us the result. That's what we wanted. Four seconds we would have to find a way to remove The minutes apart from the total time and display the seconds, one way to go about it is to take the time and subtract the time taken from the minutes and get an answer as 43 seconds. There is a better way to go about this by using the mod function, let us write mod a number that is time and we want to divide it by 60. Now, what mod will do is it will divide 523 by 60 and whatever is the remainder that will be the output. So, when we will hit on enter it directly gives us 43 seconds.
Hence, these functions can be very useful for certain occasions. Now, let us see one interesting formula in Excel called as random what this formula does is it generates random numbers. If we use the formula, r A and D, which is a short form for random, it generates numbers between zero and one that is all in decimal randomly. We can then specify the number of digits that we want to show after the decimal point by selecting these buttons over here, every time a cell is written in this sheet, Excel will recalculate the random numbers that have generated. Now if we want to generate numbers between specific number of from and to, then that can also be done. Let me explain you by giving an example.
I have some employees over here. Now these employees need to go for a particular test. Now which order Should I send them If I want Excel to generate a random number for each of these employees, that can be easily done using the RAND between function, let us write this function over here equal to Rand between the bottom value and the top value. Now here my observations are only for six values. So I would write one as the bottom and six as the top. When I hit on Enter, it is generating random values for a.
If I drag this formula down, I can see that it has generated random values for each of these employees. I can use this function at places where I do not know which order I would like to take or I want to shuffle the data that I have. This can be easily done using this ran between function summary, you have learned average mode and median function use Excel for area and volume calculation, round down, round up and round function. m od and I nt functions ran and ran between function