This calls for nested/multiple IF functions. Sometimes, you may have 3 or more possibilities, for instance, different calculations based on ranges or levels of values. Not every evaluation has only one condition limited to two different actions. But Wait … There’s More! Nested Functions Note that it doesn’t matter if you add spaces after each comma. Otherwise, the answer is “AUDIT NEEDED.” Notice that text entries are surrounded by quotes (to create a character string) with the formula commas outside the quotes. In this sample function, if the two cell values are the same, the result is “OK”. The results of the IF function can also be a text entry that you may want to filter for checking data, such as =IF(B14=E14, “OK”, “AUDIT NEEDED”). And, as with other formulas, this calculation can be copied down or across to calculate additional values. As with other formulas, the results will update if values are changed. The formula would look like this: =IF(B2>=1000, 100, 0) where B2 is the value being evaluated. For example, if an amount is more than 5% over the budgeted amount, then you might display “OVER”, otherwise, show “OK.”įor instance, what if we want to calculate an analysis where, if the total is greater than or equal to $1000, then a $100 bonus is entered into the formula cell otherwise, no bonus is given. The cell or entry that is evaluated may be a formula, value, or text the displayed result may also be a formula, value, or text answer. =IF(what you are testing/evaluating, what to do if true, what to do if false) Conditions Can Be Formulas, Values, or Text The official logic and structure of an IF function is: Then we add the function name followed by open parentheses, such as =IF(. As with other Excel functions, we start with =(equals). To see a more detailed demonstration of the various ways you can save time working with the IF function, watch my training video:įirst, let’s look at the structure (syntax) of the IF function. This article gives you an overview of the IF function. How often are you sorting or filtering data to manually locate Excel entries that might require additional data entry or auditing? With the IF function, you can automatically evaluate your data based on the condition or conditions that you create. The IF function tests whether a condition is true or false and then performs an action such as a calculation or data entry. Although SUM is the most frequently used function in Excel, the IF function should also be in your Excel bag of workbook tricks.

Excel how to create if then formula