Excel’s IF Function – Examples and Usage
Excel’s IF Function – Examples and Usage. If you need to conditionally set a cell’s value when analyzing data in Excel, the IF function is an excellent tool to use. The syntax is as follows: IF(logical_test, [value_if_true], [value_if_false]), where:
– logical_test is an expression that evaluates to TRUE or FALSE,
– value_if_true is an optional argument that specifies the result when the logical_test is true, and
– value_if_false is an optional argument that specifies the result when the logical_test is false.
Examples of IF Statements
To gain a better understanding of how the IF function operates, let’s explore some examples.
Example 1
Suppose you have a list of students and their exam scores, as shown below:
Excel’s IF Function – Examples and Usage
You want to mark each student as either “passing” or “failing” based on their exam score. You can use an IF function to verify if their score is above or below the passing grade. A passing grade is 60, so a score less than 60 indicates failure, while a score greater than or equal to 60 indicates success.
You can implement this in Excel with IF(B2<60, “failed”, “passed”), as shown below.
Excel’s IF Function – Examples and Usage
You can then fill this formula in all cells in the column to obtain the following result:
Excel’s IF Function – Examples and Usage
Conditional formatting was employed to highlight the distinctions between the two results.
Example 2
For more complex logic, you may also nest IF statements. Refer to this article to learn more about how to accomplish this. Consider this instance in the medical industry. We have a patient’s blood test results and their sex-specific normal range values. Let’s use an IF statement to determine if the blood test results fall inside or outside the normal range:
Excel’s IF Function – Examples and Usage
To check the test result for male and female patients, we need to consider their respective normal ranges. We begin by verifying the patient’s sex by checking if $H$1=”male” (we use the $ symbol to fix this cell when copying and pasting the formula to other cells). Next, we use a nested IF statement to compare the test result against the corresponding normal range.
If the patient is male, we use the formula IF(OR(J2<C2, J2>D2), “ABNORMAL”, “normal”). On the other hand, if the patient is female, we use the formula IF(OR(J2<C3, J2>D3), “ABNORMAL”, “normal”). The OR function evaluates to true if at least one argument is true, and false if all arguments are false. In this context, we use it to check if the test result is outside the normal range (i.e., if it is less than the lower limit or greater than the upper limit). If the test result is outside the range, we return “ABNORMAL”, and if it is within the range, we return “normal”.
To combine these formulas, we use the following expression: =IF(H$1=”male”, IF(OR(J2<C2, J2>D2), “ABNORMAL”, “normal”), IF(OR(J2<C3, J2>D3), “ABNORMAL”, “normal”)).
For the white blood cell count and platelet count, there is no difference in the normal ranges between male and female patients, so the formula is simpler: =IF(OR(J8<C8, J8>D8), “ABNORMAL”, “normal”).
Example 3
In this third example, let’s consider what a group of salespeople where able to sell in a certain period.
In this group, if they where able to sell more then the average they get a bonus. So let’s check each of their gains against the average with this formula:
=IF(B2>B$10, “BONUS!”, “nope”)
It seems that four of them will get a bonus for this period!
Conclusion
If you need to analyse data, then the IF statement in Excel is pretty useful.
We have seen it here in action through three different examples, but what you can do with it is only limited by your creativity. Have fun!
Let’s consider an example of a group of salespeople and their sales in a specific period. If they sell more than the average, they get a bonus. We can use the IF statement to check each person’s sales against the average and determine whether they are eligible for a bonus. The formula is as follows: =IF(B2>B$10, “BONUS!”, “nope”). In this case, it appears that four of them will receive a bonus for this period.
In conclusion, the IF statement in Excel is a powerful tool for data analysis. We have demonstrated its usefulness through three different examples, but the possibilities are endless. So, get creative and have fun exploring what you can do with it!
