The final logical function to discuss in this article is the NOT function, and we have left the simplest for last. The formula below is entered into column E and shows the XOR function with IF to display “Yes” or “No” only if either condition is met. The XOR function is perfect for this logic. If they don’t sell £3,000 or more in both then nothing. This is achieved with an AND function with IF like earlier in the article.īut if they sell £3,000 or more in either half then we want to assign them Silver status. If a salesperson sells £3,000 or more in both halves then they are assigned Gold standard. In this example, sales are split over two halves of the year. Let’s look at a simple example of the XOR function.
This function gets a little more confusing when more conditions are added. This differs from the OR function because that would return TRUE if both conditions were TRUE. FALSE if both conditions are TRUE, or neither condition is TRUE.TRUE if either condition evaluates to TRUE.When evaluating just two conditions the XOR function returns: The structure of the XOR function is the same as the OR function. This function can take some effort to understand, so a practical example is shown. The XOR function was introduced with the Excel 2013 version. In addition to the OR function, there is also an exclusive OR function. If the AND function returns TRUE then 10% is discounted from the amount in column B otherwise, no discount is given and the value in column B is repeated in column D. In the formula below, the AND function is nested inside the IF function’s logical test. The IF function alone can only handle one condition, or comparison. The AND and OR functions are used when you want to perform more than one comparison in your formula. One very useful alternative is the SWITCH function in Excel. There are alternative formulas that can be cleaner than this nested IF approach. Notice the two closing brackets on the end of the formula-one for each IF function. And you can nest more IF functions if necessary. We have now extended our formula to beyond what just one IF function can do. Take this example where we want to display the word “Excellent” if the value in cell B2 is greater than or equal to 90, display “Good” if the value is greater than or equal to 75, and display “Poor” if anything else.
But if we embed (or nest) another IF function in the value_if_false section, then we can perform another action. One IF function is capable of performing two actions (the value_if_true and value_if_false ). We may want to do this if we have more than two actions to perform. This means that we can write an IF function within another IF function. You may have heard of the term nested IFs before. We check if the due date in cell B2 is less than today’s date (The TODAY function returns today’s date from the computer’s clock). But if the date is in the future, calculate the number of days until the due date. We want to display the word “Overdue” if the date in column B is in the past. In this third example, we use the IF function to track a list of due dates. And running different formulas dependent upon the values of other cells is a very powerful skill to have. What’s important is that you can use any formula in the value_if_true or value_if_false sections. The B2*90% part of the formula is a way that you can subtract 10% from the value in cell B2. In this example, we want to give a 10% discount if the customer spends a certain amount of money.
0 kommentar(er)
