If you are analysing data in Excel and need to set the value of a cell conditionally, you can use an IF statement to do so.
The syntax is IF(logical_test, [value_if_true], [value_if_false]), where:
logical_testis an expression that evaluates toTRUEorFALSE,value_if_trueis an optional argument, and it is what the expression evaluates to in caselogical_testis true, andvalue_if_falseis an optional argument that determines the value in the case thatlogical_testis false.
IF Statement Examples
Let's see how we can use the IF statement in practice so we can better understand how it works.
IF Statement Example 1
Let's say that we have a list of students and the scores they got on an exam, like this:
We want to mark each student as passing or failing on the exam, and we can use an IF statement to check if their score is below or above the passing score. A passing score is 60, so if the students received less than 60 that means they failed the exam, otherwise they passed it.
We can write this in excel as IF(B2<60, "failed", "passed"), as below.
And then we can just fill that info in to all the cells in the column. We will get this result:
I've used a bit of conditional formatting to make it easier to see the difference between the two results.
IF Statement Example 2
You can also nest if statements for more complex logic. I've written about how to do that in this article. Let's see it again here with an example in the medical field.
We have the blood test results of a patient, along with the normal range values (which differs based on the patient's sex). Let's use an IF statement to check if the blood test results are inside or outside the normal range:
The normal range for male and female patients are different, so we need to check the patient's sex before knowing which range to use to check the test result.
We start first by checking $H$1="male" (we use the $ symbol to have this cell fixed when we copy and paste the formula to other cells). Then we use a nested IF statement to compare the value from the blood test against the range.
If the patient is male, we use IF(OR(J2<C2, J2>D2), "ABNORMAL", "normal"). If the patient is female, we use IF(OR(J2<C3, J2>D3), "ABNORMAL", "normal").
The OR function returns true if at least one of the arguments is true, and false if none of the arguments are true. In this case we use it to check if the test result is below the lower value of the range or above the upper value of the range. If it is outside the range, we return ABNORMAL, and if it's inside the range, we return normal.
Put together, the formula looks like this:
=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 based on the patient's sex, so the formula is simpler:
=IF(OR(J8<C8, J8>D8), "ABNORMAL", "normal").
IF Statement 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!