The IF Function is a very powerful function which allows users run logical tests and return a value if true, or another value if false. The logical tests are executed with the use of comparative operators such as " >, <, =, <=, >=, <> ". An example of the use of the IF Function is in the context of grading a professional exam. Imagine that you have 5000 scripts to grade, and you need to assign a pass or fail to all the scripts marked. Let's assume the pass mark is 50%, we can use the if function to return a pass or fail (=IF(A1>=50,"Pass","Fail). The IF Function is very versatile, it can be used as a standalone function or combined with many other functions to achieve great results. In this post we would take you through all the tips and trick regarding using the IF Function in excel including how to use it in a real life modeling scenario with other functions such as AND, OR and NOT. Follow me on this journey as we take a pit stop at the Syntax & Argument section.
Syntax & Argument
The Syntax for the IF Function is as follows:
=IF(logical_test, [value_if_true], [value_if_false])
There are three arguments, and all are required.
Logical_test: This represents the condition you need excel to check. It could be a logical expression or a value that can be evaluated as true o false.
Value_if_true: The value to be returned if the logical test turns is true.
Value_if_false: The value to be returned if the logical test is false.
Moving on to usage notes for the IF Function
Usage Notes
Below are some of the usage notes of the IF Function
- The IF Function can be used in testing for any condition that can be evaluated as either true or false. It could test matches for a numerical value, text value or even results of other functions
- In the point above we stated that the IF Function could test for results of other functions, so it shouldnt be surprising that we can combine the IF Function with various other functions such as SUM, AVERAGE, MAX, MIN, AND, OR and so on
- Naturally, the IF Function is built to test only one condition, but when you combine with functions such as AND and OR, you can test multiple conditions.
- In addition to the immediate previous point, you can also test for multiple conditions by using a Nested IF statement to create more complex logical statements. This is a little complicated and we advise using other functions, but nonetheless, we shall touch on it in our example section.
Logical Test Structure
In this section we would be looking at the syntax for some logical tests before we go to the main examples.
Objective | Logical Test |
---|---|
If B5 is greater than 50 | B5>50 |
If B5 is less than 100 | B5<100 |
If B5 equals 75 | B5=75 |
If B5 is greater than or equal to 200 | B5>=200 |
If B5 is less than or equal to 150 | B5<=150 |
If B5 equals Blue | B5="Blue" |
If B5 is not equal to Green | B5<>"Green" |
If B5 is blank | B5="" |
If B5 is not blank | B5<>"" |
If B5 is greater than D2 | B5>D2 |
If B5 is less than the current date and time | B5<NOW() |
As you can see from the above, texts need to be enclosed in double quotes, but numerical values are included as is. You can also directly select another cell in your logical test construct without having to use the ampersand (&) character. Please note that the IF Function does not support wildcards but by combining with other functions such as ISNUMBER and SEARCH functions, we can achieve the wildcard logical tests.
IF Function Examples
Yes or No example
Suppose we want to create a formula to check if the value in cell B5 is greater than a hardcoded number (say 100), we can use the formula below to achieve this and return yes if true or no if false.
In our example above, the logical test is B5>100, with B5 representing the cell we want to check. The argument order after the logical test is value_if_true and value_if_false, so appropriately, we have Yes as the value to return if the logical test is true and No as the value to return if the logical test is false.
Now Imagine that the value (100) we are comparing with cell B5 is instead captured in another cell, let's say cell E3, how do we go about it? Easy, we just replace 100 with the cell number:
Pass or Fail example
At the beginning of this post, we provided an example of a scenario where using the IF function could be very useful. The example centered on grading 5000 scripts and assigning a pass or fail based on a pass mark of 50%. Of course, we won't list out the scores of 5,000 students, but we would show you how to easily assign a pass or fail with a sample data of 20 from the 5000 scripts.
In cell D5 in the image above, we only need to input the formula below to return a pass if the number in the preceding column is greater than or equal to 50.
Now, copy and paste the formula on cell D5 to cells D6:D24
Nested IF Statements
In the usage notes section of this post, we stated that the IF Statement can test whether true or false for one condition only. With the concept of the Nested IF however, we can extend the functionality of the IF statement to test for multiple conditions. How does it work? Let's explain below:
A nested IF function simply put is a formula that contains multiple (two or more) IF statements inside each other. The nested IF can be used to test more conditions and return a specified value if any of those conditions are met. In a nested IF statement, each subsequent IF statement joined to the preceding IF statement usually represents the value_if_false, and the result of the immediately preceding IF statement will determine whether or not the next IF statement would be evaluated.
Let's illustrate by building on the same example in the previous section. Suppose we want to instead assign grades from A to F, we can use the nested IF Function to achieve this.
Now just copy and paste the formula to the other rows below.
IF with AND, OR, NOT
The IF Function can be combined with other functions such as AND, OR and NOT, you can extend the functionalities of the IF Function and create a custom response if:
- All the multiple criteria are met (Combined with the AND Function)
- At least one of the multiple criteria is met (Combined with the OR Function).
- None of the multiple criteria are met (Combined with the NOT Function).
IF Combination with AND
To illustrate, we would be calculating the total amount of taxes to be deducted from employees' salaries depending on their salary band. In our example, there are 3 bands with different tax rates as follows:
- 0% tax rate for salaries between $0 - $10,000
- 20% tax rate for salaries between $10,001 - $30,000
- 40% tax rate for salaries $30,001 and above
Please note that this is a progressive tax system, so each higher tax rate is on incremental salary only and not on the full salary. For example, the 20% would be on a maximum of $20,000 only (difference between the upper band and lower band)
By using Nested IF with a combination of the AND function, we would create a single formula to calculate the tax deductions for all salaries we test. To begin, we would ignore the first band group with 0 percent tax rate, and start with the third band group ($30,001 - infinity(∞))
For the third band group, we need to write a formula that calculates the difference between the salary amount that falls into that band (say $40,000) and the lower band value of $30,001 and then apply the 40% tax rate on that value. For the hypothetical values just stated, it means that we would apply 40% tax rate on $10,000 only (($40,000 - 30,0001)+1)
To achieve the above, we use the formula below:
Breaking the above function down:
Logical Test: Our Logical test in the above function is $C12>$C$7. From the image above, we can see that the salary we are creating this test for is on cell C12 and we need to check if the value is greater than our lower band for the third salary band.
Value_if_true: If the above logical test turns out true, we are instructing excel to sum up the following:
1) The difference between the salary we need to return a tax deduction for, and the lower band amount in the third band multiplied by the tax rate of the third band group. (($35,000 - $30,001)+1)*40%.
2) The difference between the Upper and lower bands of the second band group multiplied by the tax rate for the second band group. (($30,000 - $10,001)+1)*20%.
Value_if_false: For now, just put a comma after the value_if_true and close with a bracket.
We are 50% done now, I am sure you are wondering when exactly the AND function will feature. Don't worry, just a little more patience, we are almost there. The second part of our IF statement is with the AND function, it would serve as the value to return if the first IF Statement is false. The objective of the AND function is to increase the conditions we can test as our logical test, checking that the Gross Pay is greater than or equal to the lower band ($10,001) of the 2nd band group, and also that the Gross Pay is less than or equal to the higher band ($30,000) of the 3rd band group. If it turns out true, then we can simply calculate the difference between the Salary we are checking and the lower band of $10,001 and apply a tax rate of 20%. Please see the formula below:
Breaking the above function down:
Logical Test: The logical tests for this formula are $C12<=$D$6 and $C12>=$C$6. These check whether the salary is greater than or equal to $10,001 (lower band of the 2nd band group) and less than or equal to $30,000.
Value_If_true: If both of the logical tests are true, we are instructing excel to calculate the difference between the salary ($35,000) and lower band of the 2nd band group, then multiply the amount by the tax rate (($35,000 - 10,001)+1)*20%.
Value_if_false: For this standalone, we would also leave the value_if_false as zero.
Formula Combined:
Now we can combine the formula in a Nested IF format:
We have zero (0) at the end of the formula, this serves as the value to return If the salary to check isn't greater than $10,000.
Now copy and paste (Ctrl + C to copy and Ctrl + V to paste) to the other cells and we are done.
IF Combination with OR
For this section, we would assume that we are a company that sells two distinct products, Product A and Product B. Product A is a standard product with a fixed unit price whilst Product B is a bespoke product that depends on the features needed by the client. It is the company's policy to offer a 5% discount on orders of 12,500 units (at $0.8 per unit) or more of product A or order value of $10,000 of product B.
Even when the client orders both products and meets only one of the conditions, we extend the discount for the total value of the client's order. We have a list of orders from various clients, and we need a formula to calculate the final billable amount for each client considering the discounts if one or both of the conditions are met.
In our discount column, we only need to input the formula below to check if the client qualifies for any discount. If yes, 5% of the combined value would be calculated, and if no, there would be no discount for the client.
The final step is to copy the formula and paste to the rows below:
IF Combination with NOT
The word "Not" in English is used as a negative word which implies the opposite of something. This function has the same meaning in excel, it tells excel to search for the opposite of whatever we put in as the logical test. In our example for this section, we have the data of a credit analyst that has a list of customer loan applications, and credit score is one of the factors that affect credit eligibility. So ,we need to create a model that would flag customers whose credit scores are too low for credit approval.
Let's assume that the minimum credit score the credit analyst is to consider is a score of 700. we would input the formula below in column E to return whether or not the customer is eligible to be considered for the loan.
Now we just need to copy and paste to the rows below using Ctrl + C and Ctrl + V