1-2-3 NOTES All Versions September 20, 1984 Lotus Development Corporation The @IF Function NOTE 103 Introduction The @IF function is one of 1-2-3's logical functions. You use it to determine if a formula comparing two values is true or false. For example, @IF can compare the values in two cells; indicate if a formula produces a specified value; or determine if the value in a cell is greater than, less than, equal to, or not equal to another value. When you enter an @IF function in a worksheet cell, 1-2-3 evaluates the formula and then displays a value in that cell, indicating if the formula is true or false. Here is a simple example of an @IF function: @IF(A1>B1,1,0) The formula compares the values in cells A1 and B1. If the value in cell A1 is greater than the value in cell B1, the formula is true and 1 will appear in the cell where you entered the @IF function. If the formula is false, that is, the value in cell A1 is not greater than the value in cell B1, 0 will appear in the cell. Format The @IF function consists of the function name and three arguments: the condition being tested, the value if true, and the value if false. The arguments must be separated by commas with no intervening spaces, and all three must be enclosed in parentheses. The following illustrates the four parts of the @IF function: @IF(A10=50,1,0) 1.@IF 2.Condition being tested 3.Value if true 4.Value if false 1. @IF is the function name. 2. The condition being tested is a formula comparing two values that is either true or false. In this example, A10=50 is a true condition if the value in cell A10 equals 50. 3. The value if true is the value displayed in the cell containing the @IF function when the condition being tested is true. In this example, 1 is displayed if the value in A10 equals 50. 4. The value if false is the value displayed in the cell containing the @IF function when the condition being tested is false. In this example, 0 is displayed if the value in A10 does not equal 50. There are six ways to express the relationship between two values: = equal to <> not equal to > greater than < less than >= greater than or equal to <= less than or equal to These are called logical operators. You can enter values for the condition-being-tested, value-if-true, and value-if-false arguments in four formats: * actual numbers * cell addresses containing values * range names that refer to single cells containing values * valid formulas or functions that produce values Although the values 1 and 0 are frequently used as the value-if-true and value-if-false arguments,any values will do. When evaluating many values on a large worksheet, you should use values that stand out when you look at the worksheet (for example, 999 or -1). Note: You cannot use text or labels in an @IF function. A label always has a value of 0. Examples The following examples show how values, cell references, and range names are used as the arguments in an @IF function. They also show how the result of an @IF function changes when the values in the condition-being-tested argument are changed. Example 1 @IF(A1>=500,1,0) means that if the value in cell A1 is greater than or equal to 500, the condition being tested is true and 1 will be displayed. If the value in cell A1 is not greater than or equal to 500, the condition being tested is false and 0 will be displayed. Move: to A1 Type: 500 [RETURN] Move: to C1 Type: @IF(A1>=500,1,0) [RETURN] Cell C1, where you wrote the @IF function, should contain 1 because the condition being tested (A1>=500) is true. Now change the value in cell A1 to 100. Since the condition being tested is now false, cell C1 should contain a 0. Example 2 @IF(B3>A3,B3,0) means that if the value in cell B3 is greater than the value in cell A3, the condition being tested is true and the value in cell B3 will be displayed. If the value in cell B3 is not greater than the value in cell A3, the condition being tested is false and 0 will be displayed. Move: to A3 Type: 100 [RETURN] Move: to B3 Type: 400 [RETURN] Move: to C3 Type: @IF(B3>A3,B3,0) [RETURN] Since the condition being tested is true, you will see 400 (the value in cell B3) displayed in cell C3. Now change the value in cell A3 to 900. Since the condition being tested (B3>A3) is now false, 0 will replace 400 in cell C3. Example 3 @IF(A5<=100,A5,999) means that if the value in cell A5 is less than or equal to 100, the condition being tested is true and the value in cell A5 will be displayed. If the value in cell A5 is not less than or equal to 100, the condition being tested is false and 999 will be displayed. Move: to A5 Type: 75 [RETURN] Move: to C5 Type: @IF(A5<=100,A5,999) [RETURN] Since the condition being tested (A5<=100) is true, you will see 75 in cell C5. Now change the value in cell A5 to 200. Since the condition being tested is now false, 999 will replace 75 in cell C5. Example 4 This example uses range names to refer to cells containing values. Note: Type range names in capital letters when you use them in formulas to avoid confusing them with macro commands. @IF(REVENUE>EXPENSES,1,-1) means that if the value in the cell named REVENUE is greater than the value in the cell named EXPENSES, 1 will be displayed. If not, -1 will be displayed. Move: to A7 Type: 400 [RETURN] Move: to B7 Type: 100 [RETURN] Using the /Range Name Create command, name cell A7 REVENUE and cell B7 EXPENSES. Move: to A7 Select:/rnc Type: REVENUE [RETURN] (two times) Move: to B7 Select:/rnc Type: EXPENSES [RETURN] (two times) Now type the @IF function. Move: to C7 Type: @IF(REVENUE>EXPENSES,1,-1) [RETURN] Since the value in cell A7, REVENUE, is greater than the value in cell B7, EXPENSES, the condition being tested is true and 1 will be displayed in cell C7. Now change the EXPENSES value to 500. Since the condition being tested is now false, -1 will be displayed in cell C7. Example 5 Assume you are the manager of a sales department and you want to give a 5 percent commission to every salesperson who recorded sales over $100. Start with an empty worksheet and enter the following data, beginning in cell A1: A B C 1 NAME CURR. SALE 5% COMM. 2 Jim 3000 3 Mike 500 4 Susan 2000 5 Kate 84 This example involves two new elements: using a formula as one of the @IF arguments and copying the @IF function into other cells in the column so that it can evaluate several entries. First, write an @IF function that specifies that the commission will be 0 for sales of $100 or less and 5 percent for any amount over $100. The function should look like this: @IF(B2<=100,0,(B2-100)*0.05) Enter this function in cell C2 and copy it into cells C3, C4, and C5. When you copy the function down the column, all the cell references are relative. This means the function uses the appropriate values for each row. This @IF function means that if the current sales are $100 or less, 0 (no commission) will be displayed. If the current sales are over $100, the value for 5 percent of the current sales over $100 will be displayed. In this example, Jim, Mike, and Susan should get a 5 percent commission. The worksheet should look like this: A B C 1 NAME CURR. SALE 5% COMM. 2 Jim 3000 145 3 Mike 500 20 4 Susan 2000 95 5 Kate 84 0 Example 6 You can also use one @IF function nested inside another. In this example, you will use a nested @IF function to change the commission structure set up in Example 5. Assume that in addition to the 5 percent commission for sales over $100, you want to give your salespeople an extra 5 percent commission for sales over $500. To do this, you nest an @IF function in the value-if-false argument of the original formula. Put the label for column D in cell D1. Move: to D1 Type: 5% COMM + 5% Enter the following formula in cell D2 and copy it into cells D3, D4, and D5: @IF(B2<=100,0,@IF(B2<=500,(B2-100)*0.05,(B2-100)*0.05+(B2-500)*0.05)) 1. 2. 3. 4a. 4b. 4c. 4d. 1. @IF is the function name. 2. B2<=100 is the condition being tested. 3. 0 is the value if true. 4. This nested @IF function is the value if false: a. @IF is the function name. b. B2<=500 is the condition being tested. c. (B2-100)*0.05 is the value if the nested condition is true. If sales are less than or equal to $500 (B2<=500), the commission will be 5 percent of sales over $100. d. (B2-100)*0.05+(B2-500)*0.05) is the value if the nested condition is false. If sales are over $500, the commission will be 5 percent of sales over $100 plus 5 percent of sales over $500. In this example, Jim and Susan should get an extra 5 percent commission for their sales over $500. The worksheet should look like this: A B C D 1 NAME CURR. SALE 5% COMM. 5% COMM + 5% 2 Jim 3000 145 270 3 Mike 500 20 20 4 Susan 2000 95 170 5 Kate 84 0 0 Compound Conditions You can use compound conditions in @IF functions to evaluate two or more conditions at the same time. Use the following logical operators to build compound conditions: #AND# Both conditions must be true for the result to be true. #OR# If either condition is true, the result is true. #NOT# This logical operator uses only one condition. If the condition is true, the result will be false. If the condition is false, the result will be true. Examples Using Compound Conditions Assume you own a small business and want to evaluate different accounts to identify those that have an outstanding balance and are overdue. Start with an empty worksheet and enter the following information, beginning in cell A1: A B C D E F 1 ACCOUNT BALANCE DAYS OVERD #AND# #OR# #NOT# 2 Thomas $300 30 3 Johnson $400 80 4 Carter $600 90 5 Bitler $700 20 6 James $300 50 Example 7: #AND# You want to identify records in which the balance is greater than $500 and the payment is more than 60 days overdue. Use #AND# in an @IF function to test for both of these conditions. Enter the function in column D. Move: to D2 Type: @IF(B2>500#AND#C2>60,999,0) [RETURN] Now copy the function into cells D3, D4, D5, and D6. This @IF function means that if the value in column B (BALANCE) is greater than $500 and the value in column C (DAYS OVERD) is greater than 60, the number 999 will be displayed in column D. Otherwise, 0 will be displayed. Example 8: #OR# You can use the same information to select all of the records in which the balance is greater than $500 or the payment is more than 60 days overdue. Enter the function in column E. Move: to E2 Type: @IF(B2>500#OR#C2>60,-1,0) [RETURN] Now copy the function into cells E3, E4, E5, and E6. This @IF function means that if either condition is true, -1 will be displayed in column E. If both conditions are false, 0 will be displayed. Example 9: #NOT# #NOT# tests for only one condition. You can use it to identify those records in which the balance is not less than or equal to 30 days overdue. Enter the function in column F. Move: to F2 Type: @IF(#NOT#C2<=30,1,0) [RETURN] Now copy the function into cells F3, F4, F5, and F6. This @IF function means that if this condition is true, that is, if the value in column C is not less than or equal to 30, 1 will be displayed. If the condition is false, 0 will be displayed. Now that you have completed examples 7, 8, and 9, your worksheet should look like this: A B C D E F 1 ACCOUNT BALANCE DAYS OVERD #AND# #OR# #NOT# 2 Thomas $300 30 0 0 0 3 Johnson $400 80 0 -1 1 4 Carter $600 90 999 -1 1 5 Bitler $700 20 0 -1 0 6 James $300 50 0 0 1 Combining Compound Conditions Example 10 You can combine compound conditions in one @IF function. For this example, continue using the worksheet from Example 9. You now want to identify the accounts that are both over $500 and 30 days overdue or that are more than 60 days overdue. Enter the function in column G. Move: to G2 Type: @IF((B2>500#AND#C2>30)#OR#(B2<500#AND#C2>60),1,0) [RETURN] Now copy the function into cells G3, G4, G5, and G6. If either of the conditions is true, 1 will be displayed in column G. If neither condition is true, 0 will be displayed. Your worksheet should have 1 in cells G3 and G4 since both the Johnson and Carter accounts meet one of the conditions. The Carter account is more than $500 and more than 30 days overdue, and the Johnson account is less than $500 and more than 60 days overdue.