Hello and welcome to our new video in this video we are
going to learn about indirect formula, how indirect formula
works and how to use two formulas at the same time.
Let me give you an example, suppose you have a female
friend and you like her, so you can't get information about
her directly.
You can get information about her through your friend, you
can't directly talk to your friend's friend, but you can
get information about her indirectly.
So this is what we are going to see in excel, how we can
get information about things indirectly.
So let's try to understand this thing, how it works
indirectly.
Here we have given some data, in the data we have given
some subjects, here we have given some students' names and
here we have given their marks.
So if I want to ask directly, for example, this cell is K7,
so I want to ask directly from K7 that how many marks are
there in this cell, so it will tell you that there are 84
marks in this cell.
But if I want to find out indirectly, then I have to put
the name of this cell in this cell and I have to find out
what value is there in this cell, so this is what we are
going to try to find out indirectly.
So first of all, how do you use the indirect formula, so
first of all you have to put equals to and then you have to
put indirect here.
So here we have written 08 and we have closed the bracket
and entered it.
Now you will see that the hashtag reference is coming here,
that is, there is no data coming here because we have not
put it yet, we have not asked 08 about any cell.
So here I have to find out what is the value in this cell,
so I will ask 08 that tell me what is the value in I7, so I
will put I7 here, enter it, it came 98, I will increase its
size a little so that it is visible.
So here you can see that I put I7, it came 98, now I have
to ask 08 that tell me what is the value in L7, so I will
put L7 and enter it from here and I will know that 51.
So what are we doing, we are indirectly finding the values,
we are not directly finding the values, we are not directly
finding the values from this cell, we are finding out
everything through 08, now let's go ahead.
Now one more thing to tell in this is that we have seen
that when we click on any cell, we see its name in the name
box, like if I click on this cell, I know that it is K9, if
I click on this cell, I know that it is K7.
Can we do this that I have Rahul's marks 98, 80, 92, 78,
87, can I give the same name to these 5 cells, is it
possible, so let's do it, it is actually possible, so I
selected it from here and then what I did, let's select it
again from here,
I selected it from here and then I changed the name, I put
the name Rahul here, after that, I am going to do it with
everyone in the same way, I put the name Deepak here, so
what I can do, we can give a name to all 4 cells, now I
will tell you the use of it, what is the use of it, in the
same way I did it here, in the same way I did it here,
after that, here I have selected the same thing, I have
selected the cells, I have put Pankaj here and after that I
have selected the same cells here and here I have put
Himanshu in the name box, now you will see here, so you
will see Himanshu, Pankaj, Rahul, Yash, you can see in this
way,
so now what we will do, now we will put the value here, if
we put Rahul here, then what value comes, what should come,
we are trying the same here, so as we put Rahul here and
entered it, it came 92, now what came 92, because it picked
up the same value, now what we have to do, we want to put
Rahul here and what comes, whatever marks are written in
the cells, their total comes, I want this, so what will
have to do for that, as I told you in the starting, we will
see how to use two formulas
so what we do here, as it is written indirect weight, so
what we have to do, we have to take out the sum first,
after taking out the sum, we put here indirect, see one
formula is used for the sum and the other is for indirect,
and what we have put in the indirect, we have put this
weight and we have closed it, now one bracket has become
this one, it has become indirect, to close the second
bracket, which one will have to be put, the sum one, so we
have put one more bracket and entered it here,
you will see here, how much came total, 435 came, from here
if I change, I put Deepak, I put Yash, so how much should
come, 376 should come, 441 should come, so let's do it,
here I put instead of Rahul, suppose I put Yash, I put Yash
and entered it, so how many marks did I get here, I got
441, if I put Himanshu here instead of Yash, then what will
I get, I put Himanshu and I should get, the spelling was
wrong,
so let me correct the spelling here, I put Himanshu and
entered it and it came here 354, so now we have seen this
thing that we are indirectly finding things, we are finding
the value, second we have seen that we are using two
formulas together, now we are using the formula of sum
here, you can use any formula other than sum, like if you
want to use two formulas, like I can use average with sum,
sorry I said it wrong, I can use average with indirect,
I was using indirect with sum, I can use indirect with
maximum, with minimum, so we see that thing here, here I
make a slight change, what do I write, I write max instead
of sum, so it came here, so what will it do, it will do
that all the marks in Himanshu's cells, from here maximum
marks will be there, like it is 95,
So what should be the answer?
It should be 95. Let's see.
I entered. 95 marks came.
Is it clear? Now let's see that if Himanshu wants to show
me minimum marks then what I will do? I will go to min and
double click here.
So now Himanshu's minimum marks are 48 I think.
So what should be the answer?
It should be 48. I entered. 48 came.
So we have seen how we have used two formulas.
We have seen the exact with sum, maximum and minimum.
Let's see it with average too.
So I will go here and remove min.
Here we do average.
And what we do from here?
We enter. So it came 70.8.
So I think it will be clear to you.
Apart from this, count is also left.
Let's do count too.
I know the count is 5.
But still let's do it once.
So here I write count.
Here is the count.
I double clicked.
And what I have to do from here?
I have to enter.
And I got to know how many subject marks are there.
So I counted and told me.
1, 2, 3, 4, 5.
So what is the answer here? 5.
I hope it will be clear to you how to use indirect formula
and how to use two formulas at the same time.
So I will meet you in the next video.
Till then what you have to do?
You have to practice.
And if there is any doubt, then you have to watch this
video again.