Excel Tip - IF() Function

The If() function in Excel is one of my favorite functions. The reason is that it allows Excel to find the errors in my workbooks. Read the whole article for the details.

IF function

Purpose

The purpose of the IF function is to provide the user the ability to make comparisons of cell values and return values based on the results of the comparisons.

Useful When

The IF function is useful for error checking and flagging you when certain conditions exist in the data. It is also useful when analyzing a large spreadsheet for certain relationships.

Detail

The IF function is a formula that requires three arguments:
* The condition to test
* What to do if condition is true
* What to do if condition is false

For example, you could create an IF statement that contains the logic: “If assets = liabilities then show ‘ok’ otherwise show ‘ERROR’”. This way, your eye would quickly detect the error in your spreadsheet. Before you printed and distributed it, you could correct the error. As you can imagine, there are many uses for this type of error checking in every day life.

Microsoft description

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false) WHERE Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator. Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula. Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Remarks
* Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.
* When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.

Exercise

Error flag exercise

This exercise will demonstrate how to set up an IF function to determine if assets = liabilities, and flag the user.

Notice the formula is “=IF(C11=C18,”ok”,”ERROR”)”, where C11 is total assets and C18 is total liabilities and equity. If the cells are equal, then return the word “ok”; otherwise return the word “ERROR”. You will note that in Figure 28, assets equal liabilities, so the word “ok” is displayed in cell B4.

Figure 28

However, in Figure 29, assets do not equal liabilities, so the word “ERROR” is displayed.

Figure 29

However, the balance sheet in Figure 29 would not be suitable for printing and giving to a client since it has “In balance?” text in A4. Thus, it may be better to disguise the error-checking cell in a more clever way. For example, a nice technique is to place the error checking IF function in cell A1. In that case, the formula would read “=IF(C11=C18,”Balance Sheet”,”ERROR”)”, as demonstrated below.

Figure 30

As shown in Figure 30, if the balance sheet is in balance, then everything looks fine. However, if the balance sheet is out of balance, the user will be notified, as shown in Figure 31.

Figure 31

Conditional computations example As used above, the IF function is terrific for flagging errors to the user. However, the IF function can also be used to perform advanced computations based on the conditions that exist within workbooks. For example, you could instruct Excel to compute a commission on sales in excess of a budget amount.

The logic would be “if sales exceed budget, then compute a commission otherwise return 0”. Note in Figure 32 Sales are in excess of budget, and a commission amount is computed.

Figure 32

However, in Figure 33, Sales are under budget, and thus, no commission is payable.

Figure 33