How can you perform calculations based on criteria? Use the 'if' function!
Let's start with an employee whose name and salary you have entered in an Excel worksheet. Based on his basic salary you pay him a holiday allowance. Now people who get 'x' salary are paid 60% of the salary as holiday allowance and people who get 'y' salary are paid 80% of the salary as holiday allowance. This is a straightforward calculation if you have a small amount of data, let's say, 5 or 6 entries. What happens if you have 100 or thousands of entries. You just can't drag the cell handle and copy the formulas ('autofill') because different values are applicable to different employees. What happens if you wish to use another factor in the calculation like designation of the employee. So, now if somebody is a manager and he draws a 'x' salary, then you want to give him 100% of monthly salary as holiday allowance and if another employee is a manager and he is drawing 'y' salary you want to give him 85% of monthly salary as holiday allowance. How can you solve the problem using the 'if' function in Excel?
The situation is not very different when a teacher wishes to define grades for students based on the total percentage marks that a student gets.
Other situations where you can use 'if' and multiple 'if' would be where you have different 'VAT' (Value Added Tax) values for the products you sell. You could also apply the 'if' function to calculate taxes based on the total salary package and the tax tables provided by the revenue collection department.
Other interesting related functions are 'sumif and 'countif'. If you have used the simple 'conditional formatting' function in Excel, then you know how effective this function is to highlight important data based on your criteria!
To see the use of 'if' and other related functions in action, please follow the links to the free Excel training videos given below.
Till the next article - take care!
What is Excel and how do I use it?
A complete guide to how Microsoft Excel is used.
Creating your own functions in Excel
Creating custom functions in Microsoft Excel can help automate your work.
Getting Information From Your Data in Excel
Once you have data in an Excel worksheet, the next step is to extract information out of the data. For example, a teacher may need to know which students got grade A in Maths, an employer may need to know about all the employees who were absent for more than 3 days in a month or a doctor may need to know how the patient responds to a certain medicine.
