Hello everybody, welcome to the second dashboard. In this video we will see how to get data for each division in yearly experience slabs. So what is the salary paid out, the first thing will keep the cursor on j five now put in the formula equals average if over here as we have multiple parameters, that is one, this is the division and the other one is experience. So we'll go with average ifs. But the problem we might have is that when we put in the formula, it will overlap the reference or the cells which will be difficult to select. So the best option is to first select the range where the formula has to be put in.
Rather than writing the formula into the cell directly just write the formula into the formula bar which helps us with overlapping cells. Something like these equals two average ifs, I'll press the Tab key Complete the formula. The first range that is what is the average range in actual and will that is converted salary from the data selected by the user. Next criteria range, the criteria range is also designation from the data. Now, these are all something that I have created, the same method is being used as we did for the countries that converted salaries and so on TAB key that will complete the formula. Now, from the designation which is selected by the user, I'll be looking out for the admin as of now, where the I column will be constant because I'm trying to cross where the I column has to be constant.
That's for the first criteria and the criteria range. Now comes the second criteria range. second criteria range is experience so experienced group from the data, the same as I've done for converted salary designation from the data. So the same logic is applicable for experience group from a data, just keep it simple and say it is from data common experience from the top 00 to one zero. Here the fourth row will be constant because all the experiences mentioned in the fourth row. Once the selection is done, close the bracket.
Now completely fill up this particular formula in the selected area, the shortcut we can use is Ctrl. Enter Ctrl Enter will directly fill the data in the selected reference. To change it into a number format we'll be using Ctrl Shift exclamation mark and that will convert it to a number format with two decimals. Now comes the fun part. So as we can see, there are some errors because there is no data provided in the actual data for example, the IT field under the year experience of 31 and 40. There are no employees yet assigned.
So to remove these errors, we will be using it Oops, sorry for that, again, we will go into the formula bar will say if error and then at the extreme end we'll put in a comma that is value for error. We cannot put zero because zero stands that average itself is zero, and we cannot give the wrong information. So we'll keep it as blank. To do so just put in a double quotation, and a double quotation once again without putting any spaces or characters into the blanks. Close the bracket again Ctrl, enter and there it is, we have the average results to the 31st. In the next video, we'll see the number of total salaries and the number of employees using the same concept.
Till then keep practicing. See you in the next video. Thank you