A lot of businesses fail to keep track of simple data. This lack of records can result in landing the company in hot water. The IF functions used in spreadsheet programs help identify whether they’re meeting their goals, either for legal reasons or to improve business practices.
The IF functions in Google Sheets and Microsoft Excel can be simple when returning results for simple data. Or, this function can also be used as part of more complex formulas in conjunction with other functions to minimize effort for users in the long term.
Pretty much any business can benefit from using IF functions. Professional institutions can use them for a vast array of purposes, including but not limited to:
- Calculating commissions
- Pass or fail functions
- True or False operations
- Yes or no results
- AND/OR calculations
- SUMIF, COUNTIF, MAXIF, etc
As it is one of the most interactive and adaptable formula types in Excel and Sheets, it’s worth learning how to use the IF function effectively.
1. Simple Functions Such as Pass or Fail
As this is one of the simple uses for the IF function, we have the opportunity to explain it here.
Let’s say the employees at your company have just engaged in an Occupational Health and Safety Assessment, and they needed to score at least 80% to pass. To keep things simple, let’s make the score out of 10, so a score of 8 is the passing mark.
For the example below, we used the following IF function syntax to give a Pass or Fail result:
That equates to a score greater than 7 (meaning 8 and up) will return the result Pass, while any other score (7 and below) will return Fail.
After you input the original formula, you can click and drag it to include the scores of each employee.
As you can see, it’s now easy to identify which employees received a Pass or a Fail. It would have been evident with such a small data set anyway, but you can see how this would be useful if there were pages of employees with more complex score requirements.
You could then sort A-Z to group together those that passed and those that failed quickly.
This same process could apply to true/false, yes/no, or any other result you’d like. You would just have to change the text in the quotation marks to the intended result. For example, change “Pass” and “Fail” to “True” and “False”.
You could also use a similar system to identify employees that have missed any internal training they’re required to do by using the ISBLANK syntax as part of the IF function.
Using the same spreadsheet as before but pretending some employees hadn’t completed their training yet, we could use this formula to figure out who is overdue:
It’s not hard to see the time-saving implications of this function for companies that have regular training as part of their safety and efficiency requirements.
2. Calculating Commissions
Work in sales? The IF function can be a godsend for you too.
You can use the IF function to only calculate commissions for yourself or your employees if their sales for any particular period are above a certain threshold.
You can use this in a simple way, such as – IF the source cell is over a certain threshold then calculate commission percentage in a new cell.
But, the calculations can get more and more complex depending on commission scales too. The IF function can easily figure out percentages for each commission above multiple thresholds and give accurate final results.
3. Integrated IF Functions
Companies can use the IF function alongside the other essential functions in spreadsheets.
Sometimes you will only want to make calculations if the data input matches specific criteria. One example of this could be using SUMIF functions to determine the sum of particular groups in a data set.
In the examples we used above, the SUMIF function could figure out the sum only for those that have completed the test.
We have only scratched the surface of what IF functions can do for your company. They’re time-saving when used correctly and provide accurate, relevant, and specified data at a glance. Once you and your employees master the use of IF functions, it will be easy to see the benefits they have created for your business.