1 / 11

The IF function

The IF function. Bernard Liengme. Objectives. To know how to: Construct a condition using the comparison operators =, >=, >, <= , < and <>; Construct a formula using the IF function; Nest two IF formulas U se the logical functions AND, OR and NOT;.

duyen
Télécharger la présentation

The IF function

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The IF function Bernard Liengme

  2. Objectives To know how to: • Construct a condition using the comparison operators =, >=, >, <= , < and <>; • Construct a formula using the IF function; • Nest two IF formulas • Use the logical functions AND, OR and NOT;

  3. The IF function is used when you want a formula to return different values depending on the value of a condition or logical test. • As a simple example, suppose A2 contains the percentage increase in sales and you wish to have the word “Good” or “Poor” in B2 depending on whether the increase is greater than or equal to 100. The formula =IF(A2>=100, “Good”, “Poor”) will achieve this.

  4. Syntax for a formula using the IF function

  5. A condition has the form: Expression-1 Comparison-Operator Expression-2 Expression-1 and Expression-2 are any valid Excel expressions composed of cell references, constants and functions. Example of conditions are: A1>10, A1*2 >= 50, and A1 − B1 <> 2*C1. Essentially, an expression is a formula without the equal sign. Thus to test if cell A3 has a value of 5 the condition is A3 =5. A condition is said to be either true or false.

  6. A condition has the form: Expression-1 Comparison-Operator Expression-2 • Expression-1 and Expression-2 are any valid Excel expressions composed of cell references, constants and functions. • Example of conditions are: A1>10 A1*2 >= 50, A1 − B1 <> 2*C1. • Essentially, an expression is a formula without the equal sign. Thus to test if cell A3 has a value of 5 the condition is A3 =5. A condition is said to be either true or false.

  7. The comparison operators are: = equal to > greater than >= greater than or equal to < less than <= less than or equal to <> not equal to.

  8. Examples of IF formulas • =IF(A2<0, “Negative”, “Positive”) Returns the text “Negative” if A2 has a value less than 0, otherwise returns “Positive”. • =IF(A4-B4<=10, 0, 1) Returns 0 if the quantity (A4−B4) is less than or equal to 10, otherwise returns 1. • = IF(A6<>0, (B6−A6)/A6, “”) This returns the ratio (B6−A6)/A6 when A6 is not zero but it returns nothing otherwise. This avoids the #DIV0! error.

  9. The logical functions AND(), OR() and NOT() are used alone to construct compound conditions • =AND(A8>=1, A8<=10) Returns the value TRUE when A8's value lies between 1 and 10, inclusive. Otherwise, it returns the value FALSE. • =OR(A10<1, A10>10) Returns the value TRUE when A10's value is less than 1 or greater than 10. Otherwise, it returns FALSE

  10. =A1=A2 It compares the values in two cells and returns TRUE or FALSE • =NOT(A1=A2) This formula also compares the values in two cells but it returns TRUE when the values are not the same and FALSE when they are equal. • IF(AND(A2>0, A2<11), A2, “Wrong”) The value of A2 is returned if A2 is greater than 0 and less than 11. Otherwise, we get the word Wrong.

  11. Nesting • =IF(A6<10, “Small”, IF(A6>100, “Big”, “Medium”)) • For the moment, ignore the second (the inner) IF. It is clear that when the condition A6 <10 is true then the first IF returns Small. • What happens if the condition is false? The second IF comes into play. When A6 >100, the inner IF returns Big, otherwise it returns Medium

More Related