Part -13 – IF Function In MS Excel
Download Excel Templates – IF Function in MS Excel
Transcript For The Video – IF Function in MS Excel
video on IF Function in MS Excel
In this set of video tutorials, we are going to learn calculations using IF function in ms excel when there are certain conditions that are being implemented; let’s say when we talk about salaries, a part of the salary actually goes to the government as a part of the taxes. so on one side, we have not actually calculated taxes here because this is the overall salary, but the net salary, which is the take-home salary, is somewhat different from what you see here, so essentially because of the taxes.
So let me give an example; let’s assume that hypothetical case where taxes are defined in such a way let’s say from when the salary is between zero to let’s say fifty thousand dollars the tax rate is zero percent, and when the salary is above fifty thousand dollars the tax rate is twenty-five percent. Now let us take an example; let’s say a person earns seventy thousand US dollars, now, in this case, it will be divided into two parts, one is when the salary is between zero and fifty thousand so the first case is up till fifty thousand the tax would be zero and whatever the amount is above fifty thousand till seventy thousand, in this case, the difference is twenty thousand, and the tax that will be applicable on this additional twenty thousand would be twenty-five percent. so twenty-five percent of twenty thousand, in this case, would come up to be five thousand.
So the total taxes actually is the sum total of these two which is five thousand. so the take-home salary would be seventy thousand, which is the actual salary or the gross salary, and we subtract the government taxes, and the take-home salary would be sixty-five thousand. So these are the certain conditions with respect to taxes and let us try to apply these same tax rates in our case study. So while we apply the tax rates, we can first calculate the taxes and then calculate the gross values, so let me clean this excel sheet first quickly and what I’ll do is I’ll insert two rows in between, and for doing that, I need to just select from the top the whole column, and I do right click and you can press insert what will happen by doing that is a column is inserted between the table and the column which we were talking about. Likewise, I want to insert another cell; I can do the same thing; I just go and right-click and press insert now; what I want to do here is will calculate the taxes in this column and the take-home salary in this set of columns, ok? So that’s what we are going to do.
So in order to calculate the taxes, we are going to use the functions, which are IF function in MS Excel. So now, in order to use the IF function in ms excel what we need to do, we just need to type equal to write IF open bracket ( now what happen here is once we open the bracket, we will find that it provides us with the syntax there are three things which this IF function in ms excel requires the first is a logical test. the logical test is the condition that you want to check whether it is true or false. if it is true, then perform an action that would be valued if _true. If it is false, then perform another action which is value_if_false.
So the first thing that we need to actually do in our case is to check whether the annual salary is greater than fifty thousand or not. so that would be a logical condition here. Because that is the limit on which we as salary holders may have to pay taxes. So, this would be in cell G6 case of Melvin lets type a greater sign is available on the keyboard very close to the question mark. This G6 should be greater than 50000; that is our logical test. Now the next thing that you need to do here is while using the IF function in ms excel is after this condition which is fully complete; you need to type a comma in order to make sure that yes, we have completed the logical and we can move forward and type what needs to be done if the value is true so as you can see how this gets slightly bolder and we can perform the relevant action which we require when the logical test is true, so the action item that needs to be performed is that on the difference between the annual salary and fifty thousand we need to multiply that by twenty-five percent tax.
So, as we know that for the initial fifty thousand there are no taxes and for any amount which is above fifty thousand we need to pay 25% tax. So, first and foremost, we need to calculate what is that additional amount, so the additional amount when we have calculated, we need to use a bracket so as to clearly define what the difference has been calculated here. The annual salary is in G6 minus the minimum amount was fifty thousand. so this bracket term actually calculates the difference between annual salary and fifty thousand, so whatever this amount is, twenty-five percent of this will go as taxes.
So, we use this asterisk sign, and we multiply that by twenty-five percent and typing percent within this set of formulas. so, don’t get intimidated; this really follows a very logical way, so we have kind of completed the value if true. Now the next thing that we need to do is we need to again press the comma so that we can shift from the value if true to or value if false. So What if the salary is less than fifty thousand? There are no taxes that need to be paid; in this case, we will have zero, and last but not least, we need to close the formula in order to make sure that we are done with this IF function in ms excel.
so let’s press enter and see what happens so Melvin is actually paying the salary of, I mean the taxes of fifteen thousand one hundred what would be his take-home salary would be the difference between annual salary and taxes. so this would be calculated here, and this would be annual salary which is G6 minus H6 which is taxes, and we press enter, and we find that the take-home salary of Melvin is ninety-five thousand three hundred dollars. Now that we have calculated the taxes and the take-home salary for Melvin a calculating the same for others should not be difficult because we have learned the formula, which is copy and paste to copy the formula and past the formula is what we are going to do in the case.
So let’s copy this by right-clicking and copy and paste the same formula for Bruce, and we find that Bruce doesn’t pay any taxes because his salary is extremely low in this case, and that turns out to be 0 dollars. Likewise, let’s do it again for the case of Jacob, and we right-click and paste. So Jacob pays 100 dollars as taxes.
Can we do the same thing for taking a home salary, of course, we can because we have done this in the past for maximum function and average function as well? So the take-home salary for Bruce is thirty thousand dollars, and the same formula can be copied in the case of Jacob and what we find is, his take-home salary comes out to be fifty thousand three hundred dollars. So another tip that we can use to copy and paste is we are coping one cell at a time and pasting one cell at a time. We can actually select two cells at a time and paste them across a range. So what do I mean is let’s say I want to copy taxes, I want to copy take-home salary both the formulas at once. So I can do that simultaneously by selecting these two.
Ok, so this is how it is done; you just select these two as a range and copy it. So this has selected both the formulas, can I paste them in this row. Yes, I can do that. Can I paste it across the range it will be a bit clever? Because you know all these formulas will get populated at once. And the next thing once you have selected the full range is to right-click again and paste it. So what we find is that all your formulas get populated at once, and if you want to just a kind of double-check, you can see these formulas are working in a very efficient way, and no structure has been disturbed. So this is how you know you can use the IF function in ms excel.
This has been a guide to the IF function in ms excel. This post is a complete heads-up on how we can use the IF function in ms excels in an efficient manner. These are the following external link related to MS Excel.