MET 107Logic FunctionsMET 107 Logic Functions Excel has to ability to make decisions as well as perform calculations. The IF() function is used for making decisions. The syntax of the IF function is =IF(Logical_test,Value_if_true,Value_if_false) Where, Logical_test is any value or expression that can be evaluated TRUE of FALSE, for example A1>2? Value_if_true is the value that is returned if Logical_test is TRUE. Value_if_false is the value that is returned if Logical_test is FALSE. Note that three arguments are required and each argument is seperated by a comma. If either of the last two arguments are text strings, the text must be enclosed by quotation marks (“). Cell references, formulas and numbers are used without the quotation marks. The figure below shows the Paste Function Dialog Box for the “IF” function as well as comparison operators for use as logical tests. Comparison Operators & Meanings: on ter A1>B1 r eq A1>=B1 r equal A1<=B1 t equa to A1<>B1 Comparis Operator Meaning Example = Equal to A1=B1 > Grea than < Less than A1<B1 >= Greater than o ual to <= Less than o to <> No l EXAMPLE 1: Cells A1 and A2 contain two numbers. In cell B2, we want to indicate whether the number in cell A2 is less than or equal to the number in cell A1 or if it is greater than A1. Cell B2 should indicate “Less than or equal to A1” or “Greater than A1”. START IS A2 <= A1? CELL B2: “Less than or equal to A1” CELL B2: “Greater than A1” FLOW CHART YES NO AB152 2 Less than or equal to A1AB152 5 Less than or equal to A1AB152 7 Greater than A1CELL FORMULA B2: =IF(A2<=A1,”Less than or equal to A1”,”Greater than A1”) EXAMPLE 2: 107 – LogicFunctions.doc Page 1 of 3 9/12/07Build a worksheet that finds the area of a circle based on the size input by the user. The user will input a number followed by a “D” if the number is a diameter. The number is followed by an “R” for a radius value. 107 – LogicFunctions.doc Page 2 of 3 9/12/07 EXAMPLE 3: Build a worksheet that finds the area of a square or a circle. The user inputs the geometric shape by use of a validation box (to be covered in a few weeks) . The dimension input area changes to accommodate the selected shape. EXAMPLE 4: In the design of mechanical or structural parts, the actual stress in the member must be less than an “allowable stress.” The allowable stress of a material is typically determined by dividing the yield stress of the material by a factor of safety (FS). The yield stress is the level of stress at which the part will permanently deform because of the loading. Create a speadsheet to calculated the allowable stress of a material using a FS = 2 for static loading, FS = 6 for cyclic loading and FS = 8 for impact loading. ABCDEFG1 TITLE: Allowable Stress 23 DESCRIPTION: Calculates the allowable stress of a material based on the 4 specified yield stress (in psi) and the type of loading.56 PARAMETERS: Yield stress of material, Sy = 60000 psi7 Loading Type = 289101112 CALCULATIONS:13 FS = 61415 Allowable Stress = Sy / FS = 10000 psi16Loading Type1 = Static Loading2 = Cyclic Loading3 = Impact LoadingCell Formula: =F6/E13 Cell Formula: =IF(F7=1,2,IF(F7=2,6,8)) ABCDEFGH12 Input Size of Circle: 10d W ork Area:3 Length 3 =LEN(C2)4 Area = 78.54 D or R d =RIGHT(C2,1)5 Size = 10 =LEFT(C2,F3-1)6=IF(F4=”D”,PI()*F5^2/4,PI()*F5^2) ABCDEF1List2 Shape: Square Square3Circle4 Side = 2.005 Area = 4.0067SquareCell Formulas: A4: =IF(B2=”Square”,”Side = “,”Diameter = “) B5: =IF(B2=”Square”,B4^2,PI()*B4^2/4)The example used a nested IF function to decide which factor of safety to use: 2, 6, or 8. The first logical test asks if cell F7 = 1. If F7 contains a 1, cell E13 will contain the value 2. If F7 ≠ 1, another logical test asks if F7 = 2. If F7 = 2, cell E13 will contain the value 6. If F7 ≠ 2, the value in E13 is 8. Figure 2-8 shows a flow chart for the IF function used in this example. What value is in E13 if F7 equals 4? The if function can be nested in any cell formula as well as standing alone as in the previous example. Start 107 – LogicFunctions.doc Page 3 of 3 9/12/07 E13 = 2 YES F7 = 1? NO F7 = 2? E13 = 6 YES NO E13 = 8 Cell Formula in cell E13: =IF(F7=1,2,IF(F7=2,6,8))