Here are two functions you can use to add power to your If statements, And and Or.
Let's do a different twist on the sales commission calculation example. Suppose we breakout the bonus commission into a separate column. Here is how we can use the And function. Try it or follow along in the And Or file.
In this example, salespeople who have been there more than 1 year can earn a 2% bonus commission if they sell over $200,000.
Notice we use the And function inside the logical test AND(B2>1,C2>200000). In order for this to be true, both parts of the And statement must be true. Mike Jordan has been there more than 1 year, but sold less than $200,000. George Washington sold more than $200,000 but is still in his first year. As a result, neither get the bonus commission.
We could have done this with a nested If statement like we did when showing how a nested If statement works. However, we like using And better in this case. It is a cleaner formula, easier to follow. Another advantage of using And is that you could have more than 2 variables in the And statement if you wanted.
Now let's change the commission formula. Allow first year salespeople a bonus also if they sell over $300,000. Now we have an either or case, so the Or function will work fine.
For our solution, we wrap the Or function around the logical test we had above, which calculated the bonus commission for the salespeople who had been there more than one year. The second part inside the Or function is the test for first year salespeople. Now George Washington gets a bonus commission.
OR(AND(B2>1,C2>200000),C2>300000)
Whether using Or or And, make sure you have the parenthesis in the right spot, or you might get an unintended result.
By usins And or Or functions, you can build more powerful formulas that are also more intuitive and easier to follow.