If you’ve used Excel for some time, you may want to learn more about the different logical functions such as IF, AND and OR. This post describes how these functions can be combined to perform very useful composite logical operations.
IF Function
The IF function will carry out one of two actions.
- One action is carried out if a specified test is true.
- The other action is carried out if the specified test is false.
If the function is typed into a cell, the function starts with an equals symbol and then the letters IF, then an open bracket, followed by the three elements test, true action and false action separated by commas and followed by a close bracket.
The function looks something like =IF(TEST,TRUE,FALSE).
For example, suppose the TEST part is A1>100 where cell A1 contains a number of sales.
The TRUE part is “WELL DONE”, and the FALSE part is “Try Harder”. So the complete function looks like =IF(A1>100,”Well Done”,”Try Harder”).
The functions displays either “Well Done” or “Try Harder” in the cell, depending on whether the number in A1 is more than 100 or not. If you type the IF function into, for example, cell A2, you can then try different numbers in cell A1, above or below 100, and you’ll see either of the messages displayed in cell A2.
AND Function
The AND function will display either TRUE or FALSE in the cell containing the AND function.
The function starts with the usual equals sign and then AND followed by brackets which contain one or more tests separated by commas. If all the tests are true, then the cell shows the value TRUE. If any of the tests are not true, then the cell shows the value FALSE.
Suppose in our above example cell B1 contains number of customers. Suppose you type in this formula to cell B2 =AND(A1>100,B1>5) and press the Enter key and look at the result. Provided the number in cell A1 is more than 100, and the number in cell B1 is more than 5, then the function shows TRUE. Try different values in A1 and B1 to confirm that cell B2 only shows TRUE if both tests are true, and FALSE if either or both tests are not true.
OR Function
The OR function will also display either TRUE or FALSE in the cell containing the OR function.
The function starts with the equals and then OR followed by brackets which contain one or more tests separated by commas. This time if any one test is true, then the cell shows TRUE. If all tests are not true, then the cell shows FALSE.
So suppose you type this formula in cell C1 =OR(A1>100,B1>5) and press the Enter key. If you try different values in cells A1 and B1 you’ll find that cell C1 which contains the OF function shows TRUE if either or both cells contain numbers over the set values.
Combining IF with AND function
Suppose we modify our IF function. We now want to show “Well Done” only if sales in A1 are more than 100 and customers in B1 are more than 5. In other words our IF function will need to have two tests true to show “Well Done”.
We can do this by combining an IF with an AND function. So in cell D1 type in the formula =IF(AND(A1>100,B1>5),”Well Done”,”Try Harder”) and press the Enter key. You can see that the AND function is now the test for the IF function, and for the test to be true both numbers in cells A1 and B1 need to be more than the set amounts.
Combining IF with OR Function
Suppose we again modify our IF function. This time we want to show “Well Done” if sales in A1 are more than 100 or customers in B1 are more than 5. In other words our IF function will now need to have only one test true to show “Well Done”.
We can do this by combining an IF with an OR function. So in cell E1 type in the formula =IF(OR(A1>100,B1>5),”Well Done”,”Try Harder”) and press the Enter key. Now you can see that the OF function is the test for the IF function, and for the test to be true either the number in cell A1 or the number in cell B1 needs to be more than the set amount.
In conclusion the IF function is one of the most important logical functions in Excel but you can combine it with AND and OR functions in different ways to create even more powerful logical tests.
A really effective way to learn more about advanced Excel and it many very useful features is to attend a training course. Then you can really boost your skills in using Excel. https://www.stl-training.co.uk/excel-2010-advanced.php
