"IF" The Excel funcion
=IF
The “IF” function is the most powerful of all functions – not just in Excel, but in any programming language. Commonly referred to as “Conditional Programming”, it is the IF function that enables us to introduce logical thinking into any program. This function is also referred to as the “If‐Then‐Else” command, “conditional expressions”, or “Propositional Logic”
Simple IF with Calculation – Presented below is an example that is a little more complex:
Simple IF – Larger Example Presented below is yet another IF example on a little larger scale – this example shows how one might apply the IF function to evaluate budget versus actual comparisons.
Simple IF with Drop Down ‐ In the following example, the IF function is checking to see if they have signed up for insurance. If they have, the deduction amount is entered.
Nested IF Functions ‐ In this sample, there are four possibilities for bonuses.
IF Function with Logical OR Argument ‐ Teams A and C meet on Tuesday, Teams B and D meet on Thursday. We want to list the meeting days in column D.
More Complex IF Function Example ‐ The following IF example shows a more complex application in which the user selects a taxpayer status from a drop down list, which then retrieves the correct tax base, threshold, and incremental tax rates to be used in calculating tax.
This example illustrates how a CPA might prepare an income statement template that calculates the appropriate amount of taxes as net income and the taxpayer status changes. Essentially the template calculates the correct tax given all four possible taxpayer statuses, and the IF statements are used to select the correct answers based on the taxpayer status selected.
Keep in mind that despite the many accolades mentioned above, the IF Function is not always the best solution. For example, the VLOOKUP would be a better and easier function to use to extract data from a list as shown in the nested IF Function a few examples above. Many Excel Functions also provide built‐in “IF‐Then‐Else” functionality.
Key Pointers for Using the IF Function:
1. Nesting – You can embed up to 8 nested IF functions in a single formula in Excel 2003, and up to 64 IF nested functions in Excel 2007.
2. AND, OR – You can use the AND and/or OR operator to add more conditions to an IF Function.
3. Variations of IF ‐ Excel offers several variations of the IF function as follows: COUNTIF, COUNTIFS, SUMIF, SUMIFS.
4. Evaluating an IF Error – Since the IF statement provides only a true or false result, there is no way to evaluate an IF Function to ERROR. If you receive an error, you wrote the formula wrong.
5. The Null Set ‐ The Double Quotes is the Null Set, or absence of a value. For example, when testing for a Zero balance or testing for a blank cell, the following IF functions would apply: a. =IF(A1=0,”ZERO”,””) b. =IF(A1=””,”Blank”,””) (Excel also provides an ISBLANK Function that would also work.)
The “IF” function is the most powerful of all functions – not just in Excel, but in any programming language. Commonly referred to as “Conditional Programming”, it is the IF function that enables us to introduce logical thinking into any program. This function is also referred to as the “If‐Then‐Else” command, “conditional expressions”, or “Propositional Logic”
The clever CPA can use the IF Function to build elaborate Excel templates and financial models containing an almost unlimited amount of sophisticated programming. Presented below are several examples to help you better understand the application of this powerful tool. Simple IF ‐ The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. Presented below is a simple example:
Simple IF with Calculation – Presented below is an example that is a little more complex:
Simple IF – Larger Example Presented below is yet another IF example on a little larger scale – this example shows how one might apply the IF function to evaluate budget versus actual comparisons.
Simple IF with Drop Down ‐ In the following example, the IF function is checking to see if they have signed up for insurance. If they have, the deduction amount is entered.
Simple IF with Calculation ‐ The next IF function example is determining each employees earned vacation days. If they have worked for more than a year, they have earned 5 vacation days plus one day for each additional full year.
Nested IF Functions ‐ In this sample, there are four possibilities for bonuses.
IF Function with Logical OR Argument ‐ Teams A and C meet on Tuesday, Teams B and D meet on Thursday. We want to list the meeting days in column D.
More Complex IF Function Example ‐ The following IF example shows a more complex application in which the user selects a taxpayer status from a drop down list, which then retrieves the correct tax base, threshold, and incremental tax rates to be used in calculating tax.
This example illustrates how a CPA might prepare an income statement template that calculates the appropriate amount of taxes as net income and the taxpayer status changes. Essentially the template calculates the correct tax given all four possible taxpayer statuses, and the IF statements are used to select the correct answers based on the taxpayer status selected.
Keep in mind that despite the many accolades mentioned above, the IF Function is not always the best solution. For example, the VLOOKUP would be a better and easier function to use to extract data from a list as shown in the nested IF Function a few examples above. Many Excel Functions also provide built‐in “IF‐Then‐Else” functionality.
Key Pointers for Using the IF Function:
1. Nesting – You can embed up to 8 nested IF functions in a single formula in Excel 2003, and up to 64 IF nested functions in Excel 2007.
2. AND, OR – You can use the AND and/or OR operator to add more conditions to an IF Function.
3. Variations of IF ‐ Excel offers several variations of the IF function as follows: COUNTIF, COUNTIFS, SUMIF, SUMIFS.
4. Evaluating an IF Error – Since the IF statement provides only a true or false result, there is no way to evaluate an IF Function to ERROR. If you receive an error, you wrote the formula wrong.
5. The Null Set ‐ The Double Quotes is the Null Set, or absence of a value. For example, when testing for a Zero balance or testing for a blank cell, the following IF functions would apply: a. =IF(A1=0,”ZERO”,””) b. =IF(A1=””,”Blank”,””) (Excel also provides an ISBLANK Function that would also work.)
Comments
Post a Comment