Hello, everyone, and this lecture, we will cover some of the basic mathematical functions in Excel,
but before that, let us understand the small difference between formulas and functions in Excel.
Most people used interchangeably, but there is a small difference between the two.
A formula is an expression which calculates the value of a cell.
It can be any expression.
What we wrote in the last class, what a mathematical formulas so does is equal to a one plus B when
a function is a predefined formula, functions allow you to use descriptive names to automatically apply
formulas for you.
For example, some average Intertrade functions, we will see how to use them.
We use functions because they increase user productivity when working with Excel.
So I hope the difference is clear.
Anything that is calculated in a cell is a formula, but functions are predefined formulas of excel
with descriptive names.
Now let's see some of the mathematical functions we use in Excel.
Let's first look at the data we have in this data we have scored of five students in three subjects
math, science and English.
We want to find out the total marks scored by these students.
For this.
We can simply write formula as earlier using the plus symbols.
Or we can use the sum function.
Let's use the sum function the same.
We go to the set and start typing is equal to assume start the bracket.
Within the bracket, a function takes parameters on which it operate.
For example, for this function, we need to give all the numbers which we want to add.
There are two ways to enter these numbers.
One is to enter each cell individually separated by a comma.
Let's do it this way for this role reversal, comma, second cell, comma, the third to close the record,
enter.
And this is the sum of these three numbers.
If you click to tail again and the formula, but you can see the function applied instead of using cell
reference, you could write the value of the cell also like some ninety five.
Eighty three.
Eighty one.
That would give you the same result.
But as discussed in the last lecture, using cell reference has some benefit for the student too.
We will use the second method instead of entering each individual cell.
I'm going to select the whole series.
This time we start again with is equal to some.
Within bracket, we use the mouse to select the entire range of cells that we want to add close to bracket
and enter.
Let's compare the difference between these two formulas and the formula bar and the first formula.
You can see that the three cells are separated by commas.
In the second formula, only the endpoints of the cities are written and between them is a colon.
So this represents that everything between M6 and 006 is to be added.
Can you see the power of some function over the normal addition formula using plus symbols?
If I have to add one hundred numbers, I do not need to write the cell reference of hundred cells.
Just select the entire cities within the same function.
For the third student.
I want you to notice another feature of Excel.
When in a cell with type is equal to excel, expect that we are going to enter a function and it automatically
starts recommending functions as we take the first few alphabet of the function name.
If you do not want to function, do not bother, just continue typing.
But if you want this adjusted function, press the tab key.
It will auto complete function as well as it will start the bracket for you.
Lastly, to calculate for other students, we will simply track the formula by going to the bottom right
corner and extending it to other students,
using any function is similar to this.
Just take the name of the function, give the parameters in the bracket and enter that it.
Let's cover other mathematical functions quickly now.
Next, we look at the main function, which returns the minimum value from the input data.
So to get the minimum score in maths, we go to this and start typing is equal to men that when we hit
done this function will be selected in the parameters we need to select the range of cells to define
what the score of student in maths.
We closed the record and how it ended.
You can see that forty five is the minimum score scored by any student in maths.
We can extend this formula for science and English also.
So 53 is the minimum score inside and 55 is the minimum score in English.
Next, we look at the max function.
Max function returns the maximum value from the input data.
So to get maximum value of school in maths, we go to this cell and rate is equal to Max.
If you want to select the second formula here, you can use the energy to go to second and then presto,
no, we need to give the cities from which we have to select the maximum score.
You can see that ninety nine is the maximum marks in that we can extend it for science and English also.
Next is the average function, this function returns the average of the mean value of the input numbers.
If you remember in the last lecture, we calculated average using usual mathematical operators.
This time we use the function so to find average marks of each student, go to the cell.
Type is equal to average when excel to just average function.
You can hit the tab key.
After this selected range of cells for which you want to calculate the average press, enter.
Try to get the average for other student.
Once we have calculated the average marks, we may want to assign ranks to students also as to which
student scored the highest marks and who scored the least to rank these students versus their average
score, go to the next level type is equal to rank average.
The first barometer is the number whose rank you want to find, this will be the school of the student.
The second parameter and this function is these cards from which rank is to be calculated.
So this will be the scores of all these student.
The last parameter is how should the ranking be done, whether highest should be ranked one or lowest
should be ranked one, if you want to be ranked one such as the case here, the third parameter will
be zero.
For the other scenario, it will be one.
So we enter zero.
And press enter.
You can see that the average score of student one makes the student one second in class.
Important thing to note here is if we drag this formula down, the reference range will change.
So so the differential range changes from B five to nine to P6 to Putin to Putin is not the correct
reference range.
In the last lecture, we discussed how to maintain the reference range to Loti reference range.
We need to use dollar signs.
To do this, go to the different values and symbols.
There is a shortcut for adding dollars and bells.
Also just click on DL on which you want to add dollars, symbols and press efford.
Dollars and bills will be added to both row and column name.
This will restrain reference values to change while dragging, so now let's extend it for other student.
Now you can see that the differential remains from five to nine and student four is now at rank one
and student five is the student with at least average bonks.
Next, we move on to some product formula in this formula, we import two or more series of numbers
and it returns the sum of product of individual elements of these two series of numbers.
For example, if we want to assign weight age to the marks of math, science and English of these students
before calculating their ranks, we have written the relativity of each object here.
Now I want to find the total marks as per village.
That is, I want to find the sum of 95 in the two plus 83 into one plus eighty one and two one point
three.
This will be the total marks for student one.
To do this, we go to the cell and tape is equal to some product.
Then we select the first series of numbers to select the second series of numbers and press enter,
we get a value of three seventy eight point three.
This is calculated by adding 95 into two plus eighty three into one plus eighty one and one point three.
This can be dragged down, but before dragging it, remember, to use dollars and for debate is.
We can track this don't know,
and here we have the total marks of student as divided.
Next, we look at brain function and function is used to generate random value between zero and one.
This is an amazing function.
It takes no input parameter.
It just generates a number between zero and one randomly.
But why do we need random numbers?
Random numbers can be used to simulate Jossi range, like throwing a dice or tossing a coin.
For example, say we want to randomly select a class monitor or a class representative out of these
five students.
I will generate one random number for each student.
Whoever gets the largest number becomes classroom.
So we go to this cell and type is equal to Aadi and the.
And you can see random values generated for student one, we can drag it down for all the student.
We can see that student five has the maximum value and now student five can be selected as the class
representative.
Next, we move to Iran between function.
This will return random value between the range that we have provided.
So this function takes two values as inputs and generates a random number between these two numbers
in our example for students.
Six, we can randomly assigned Moncks format by going to the cell and typing is equal to rank between.
And giving the Rangers 40 and.
The student has scored 56 marks in maths, we can extend this to science and English also, you can
see that every time you change any cell, the value of RAM and then between get refreshed.
So now the student has scored 88 in maths for doing science and 77 in English.
Let's see this one more time.
If I add any value to a new cell and press enter, these values are refreshed.
Basically, doing any operation in the Excel sheet triggers a refreshing of these values to stop this
from happening.
We can use the paste as a value technique.
So the thing is that these cells contain formulas, whereas we are interested in the value already generated
by these formulas, the formulas have already served their purpose.
So what we do is we copied the contents of these cells.
The content of the cell is a formula.
You can see that by pasting it in any other cell
you can see in the formula.
But when I pasted, it still contains the formula.
But we want to keep the value and not the formula.
So we copy these cells and paste them here.
As values, this is the option for posting as values.
This comes only when you have copied a formula, it basically gives the option to copy paste a formula
as a formula or the value calculated by the formula.
Now, as I have pasted the values, I have the randomly generated values with me which want to refresh
every time I perform any operation.
There are several other mathematical formulas, such as the ones in the sheet, we encourage you to
try these on your own.
You can also find a list of all the formulas in the Excel menu, click on the formula and select math
and trigonometry.
Here you can see the list of all the math related functions.
That's all for this lecture.
See you in the next one.