Objectives by the end you should be able to use cell function to get information about cell use other information functions like is blank, his formula and his number, use error finding function is error is E rr, and if error, use the OFFSET function, use the counter function, use the indirect function construct dynamic named ranges construct dynamic formulas. Today, I will share with you some functions in Excel that if use efficiently can give you an added advantage. Let us see them. Let us go to formulas, more functions information. Now, here is the list of functions that help us get some of the other information about Some cells or some functions in Excel, let us state the first one that is cell. The cell function helps us find a number of things about certain cells in Excel.

This shows a list of things that we can find, we can find an address or a cell or a column number of cell, color, contents and so on and so forth. Let us see how to find address of a cell. For that, we would write equal to sell, select address from this list, comma. Let's take this cell for now. close bracket, enter. So, as we can see, it returned the value as before.

We could also use the cell formula to find a column number of a cell. For that, we will select column, comma, let's take this cell for now f4, close bracket, and enter. So as this cell belongs to the E column, which is the fifth number, it returned five as value. Similarly, we can use this to find row number of a cell for that field, right cell row. And let's select the cell for now, see 12. So as we know that since it is a cell belonging in a 12 row, the output will be 12.

We can also use cell to find out a file name of the file that we are working or some other file we want to refer to. For that filled right cell. Select File name, comma and give reference to any cell in the file. For now, we will just give reference to a version of the same file close bracket and enter. So, as we can see, it gives us the file name with the sheet name from where we are referring to. Let us see some other formulas, there is a category of functions under more functions in the information tab that start with the word is.

Now, these functions give us an output as true or false depending whether the condition is met or not. For example, the first one over here is, is blank. Now is blank function tells us if a particular value of a cell that we have referring to is blank or not. For example, let us just double click this and select this cell. When we click on OK, it says that it is false That means this particular cell, which we referred to is not empty. So, it is not blank.

Similarly, there are other functions which give us the information, whether the cell is having any error or is having an even number or an odd number or the cell is a text, non text or a number it also gives us an information if the cell that we are referring to has formula or not. So, these functions can be used to give us certain information about the cells that we want to refer to and you can use them in formulas to perform certain calculations based on the outcome of these functions. Now, let us look at three error finding functions. Let me first write a V lookup function. Let me find out the value that is there in Region two, for me month, using this V lookup function for that I will write equal to V lookup in quotes May, then I will select the cells, comma, column index for region two would be three, because the first column is the month, second is Region One, third column is region two, hence, I have written three comma false because I want a perfect match, close bracket and hit on Enter.

So it tells me that for the month of me and region to the value is 135. Now we have got an answer over here. What if there was some error in this function or the outcome, let me purposely add an error over here. Instead of me, let me write me with an error and extra y and hit on Enter. So it gives me an error hash na that is not available. If I want to know that whether this formula gives an error or not, I can use this function is error and reference to this cell, hit on Enter.

So it tells me that a particular calculation has gone wrong and there's some error to it. Similarly, we can also use ease er our function where the value of the error is not of the type hash na. It can be any other type like hash value, hash reference, whether reference is not found divided by zero error or some other errors, which are mentioned over here. The third interesting error formula, which help us to give an output, which is logical. Or in other words, if there is an error in any function, and we don't want a sheet to show that error, we can put some value instead of an error using that function. Let me show you how that function is.

If error, let me select this, let me write a function again. We look up me with wrong spelling, comma same table array, same column number and false as we had selected earlier. Comma. Now if there is an error in this particular calculation should return one, close bracket, enter. So as we know that there was an error in this function, hence it has returned one. If I correct this formula and write the correct value of me and hit on Enter, it gives me the output as 135.

So whenever there is an error, it will give me the value that I want to specify. I can also write some text over here. If I don't want a numerical value for that, this is a very useful formula. Having the knowledge of functions that are placed over here under the information tab, let us move ahead and look at some other functions which are marked under lookup and reference. Let us see offset and indirect OFFSET function. returns a cell or a range of cells, that is specified by us to be a certain number of rows, or columns away from the mentioned cells, let us understand it with an example.

Suppose if I want to find out the region for sales in the month of March, for that, I would write equal to offset. Let's take this reference as a three cell, comma, how many rows down. Now, since we want to find out for the month of March, I would write three, three rows down, or three months, comma, columns. Now if I want to find out the region four, I would write first, second, third, fourth, so four columns wave from this particular cell, a three, and height and width. These two are optional over here. If we want only one cell to be returned, we would not write anything.

But if we want to be specific, we can write one cell height and one cell width, close bracket, enter. So it perfectly gives us 105. As an answer. We can use this same formula to find out any other cells from this particular table, or this particular sheet. For example, now if I want to find out for the month of December, for region three, I would simply change these two values. Hit on Enter.

That's it. I'll get my output. OFFSET function can also be used to calculate the sum of All these values, let us see how let us write some, because we want to find out the sum offset, let us select the same reference as before a three cell comma one row one drew down comma one column I had the height would be 12 because the want to find out for all the 12 months and the width would be one because it is extending only for one column, close bracket, close the outer bracket hit on Enter. This value is nothing but sum of all these values. Let us verify it by using the AutoSum function. Hence, we can use offset and sum together to find some of any regions over here.

We only have to change this particular value, which of the column number and we can find out some of any of this regions very easily. OFFSET function can also be used to create a dynamic named range. Let us see how let us first write some random values here, I have written some values, let me assign a name to this range of cells, I have given it name as value. Now, if I want to find out some of this I would write some value close bracket enter. So, I have got this sum of these values What if I want to add a value over here, but as you can see, once I have written this as sum of name range value, it does not expand automatically if I have To expand this particular formula to extend anytime I add any values over here, then I can use the OFFSET function in the name range.

Let me show you how. Let us go to the Name Manager, the value name range that we have created. Let us edit it, it refers to, we would write equal to offset, open bracket. Let us select the first cell over here, comma, zero rows down because he want to started from row one itself, comma zero columns ahead because we want to calculate in a column only comma for the height we want to have some dynamism over there. Hence, the height will not be specified by numbers. Instead, we would write a formula that can It's the number of cells that have certain numeric value.

And the formula for the same is count a. So we would write count a, select this column that is a column, close bracket. And for the width, we would write it as one only, because only one column we want to calculate close bracket. Okay, we'll close this. And now, as you can see, the value in column C has changed. Let us try to add some more values and some more.

So automatically this name range changes as per the number of cells that I added. Now, since this is a dynamic named range, if we select the cells over here, it will not show the name range in this particular box. Hence, we need to be a little careful when we use dice nickname ranges Let us now have a look at a very interesting function called as indirect. This indirect function returns the reference specified by a text string. Let me give you an example. Let me write before over here and let me write indirect.

And let me give reference to the cell Jeevan where I have written before close bracket and enter. So, what we see, we see that indirect function has gone to the reference given taken the value that was written over there before searched in the sheet for the cell B and for and give us the output as one zero to change this value to say d 11. So, my indirect function will go to G one, take this value as d 11 search over here and return me D column 11th row that is 141 value. Interesting, isn't it? So, we can find out any value from the cell by using indirect function and changing just one cell. Let me show you all how to use indirect function to find out some of the some values in this table.

Based on my conditions, I want to find out the sum in Region two, but not for all the values I want to specify a certain range over here. For an example, let me take fifth row till 10th row. So I want to find out the values from this particular cell till this cell only And I want to change this anytime I want for that, let me show you how to use indirect and some together Let us write some indirect. Now, we want to give a dynamic reference over here, we would use now since it is column C, we would write C in quotes and this is used to concatenate let me give the reference of this cell h4 and we want colon C and quotes and reference to the cell h5. Let me explain you what I have done over here. We want to find out the values between C five and C 10.

Correct. So, now, we want c five to be written over here is two that his colon see 10 to be written over here, but we are using This end, so that it concatenates the value for these cells. When we hit on Enter, it gives us the sum of all the values between C five and C, then we can verify it from here 710. Now, with a change of only these cells, I can find out any values that I want, between a specified range dynamically. So that's the beauty of using indirect summary you have learned using the cell function to get information about the cell information function like is blank, his formula and his number, error finding functions his error is eerr. And if error, OFFSET function, counter function, indirect function constructing dynamic named ranges construction Dynamic formulas