Hello, everybody. Welcome back to the second dashboard. In this video, we will see the implementation of name range with formulas. Let's see, as we all know, we did have the name range using the calculations by using number of employees get the work done faster. But there is another way as we saw earlier. Now what I've done is I've just calculated for one of the cells over here, that is, what is the result based on the selection that's been provided?
We know to get the average sales, we have to select the reference. That is country from data for the relevant country that is to the left, then it has to average the salary from the data and get the result before doing this conversion. And the formula looks something like this. That's a rather large formula. Now let's see how to get this thing done. We can see All the formulas, I just made it normal.
So now, first let me delete the one that I've created. And there it is, it's done. So now what I'll be doing is first thing, editing the formula using f2 shortcut or simply double click on the cell, which works better. Now, whichever has to be replaced with the name range, just select that set of formulas. In the current example, we'll be taking indirect which actually navigates through the sheets so that we can use this for our future reference. Once the data gets selected, copy it as we select, just right click on the selection, copy.
Once the selection is done, press escape so that the formula does not get this turbed so in the future, if there is a problem, we can do an f2 and the formula is still there. And we can go to the formula Name Manager. Now we already have a lot of name sets available. We'll keep it as it is, we go to new and create a brand new one. So the formula that we are picking up is for the country. So name it as country from data.
So the ground rule for even the name range is formula should start with n equals two. Just put equals two and paste the formula that we just copied. As the reference has already been provided, it follows the exact pattern Once done, click OK. And in the earlier video, we learned that it gets activated only for the current sheet. That means this particular formula will only work for the current sheet. That's totally okay because we were trying to copy paste, but do remember we can use this reference somewhere else and if I select this automatically, the reference gets selected from the edge Then that is the sheet 2016 because the year given over here is 2016. Let's see.
Now to implement it, simply open the formula select the reference. Then now to assign it open the first formula, select the entire formula which has to be replaced and just simply removed. Be careful with the brackets because if you have copied from the bracket to delete the bracket itself deleted, they are sub substituted with the one that is needed is country from data. Now that gets replaced with the actual formula, but still the formula remains the same under the name range. Let's try for the second one as well. So just be careful with the selection because the second bracket is for the entire average.
Just copy this formula Name Manager, new name, I have to give them another name. So this is salary it is converted to salary when I'm done with the second part as well, so I'll just close this and I just replaced the first one by country and the second one by converted salary from data. So now when I press enter, the formula gets updated, but still, you see the answer remains the same. I'll drag it down. The answer is exactly the same. But if I change the year I do get an updated answer.
That means the formula is working exactly as it should work. And I'm keeping my formula safe. So in this future, if you can't protect your farm, you know there's another way apart from hiding the forest. We can actually store the formula in the Name Manager and no one will know what formula we're trying to get. So our data will be saved. The selection also will be saved.
In the next video we'll see how to get minimum salary and maximum salary one after the other. Till then keep practicing. See you in the next video. Thank you