1 / 23

Introduction to the IF Function

Introduction to the IF Function. Please read the User Guide before beginning. User Guide. Objectives. The IF Function is one of the more difficult functions in Excel. However, it is one of the most powerful. After completing this instructional unit, you will be able to:.

knudsonk
Télécharger la présentation

Introduction to 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. Introduction to the IF Function Please read the User Guide before beginning. User Guide

  2. Objectives The IF Function is one of the more difficult functions in Excel. However, it is one of the most powerful. After completing this instructional unit, you will be able to: 1. Define an IF Function. 2. Identify the Arguments of an IF Function. 3. Use the If Function in a Formula 4. Use Multiple Conditions in an If Function

  3. Objectives (Objectives continued from previous slide) 5. Use text comments in an IF Function. 6. Use a Null string in an IF Function. 7. Nest Formulas/Functions in an IF Function 8. Nest an IF Function in an IF Statement

  4. What is an IF Function? An IF Function is used to create conditional expressions. For example: • If the bill is 30 days late, you will pay a late fee of 2 percent. • If the bill is over $100 and coded A, then you will receive a discount of 5 percent. • If the bill is $100 or coded A, then you will receive a discount of 5 percent. Back Objectives

  5. How do you build an IF Function? An IF Function has 3 arguments: • A condition, or logical test, that typically contains a logical/comparison operator. • A value that is returned if the condition, or logical test, is true. • A value that is returned if the condition, or logical test, is false. For Example: =IF(B4>20000,500,0) Back Objectives

  6. How do you use an IF Function? In this spreadsheet the IF function, =IF(B4>20000,500,0), is calculating the bonus received by the employee. If the amount in cell B4 is greater than 20000, then the employee will receive a bonus of $500, if not then the employee will receive zero. Back Objectives

  7. How do you use multiple conditions in an IF Statement An IF Function satisfies one condition. To satisfy more than one condition, use a logical connector (And or Or). For Example: You want to display B4 if it contains a number strictly between 1 and 100, and you want to display a message if it is not. If B4 contains 104, then: IF(AND(1<B4, B4<100), B4, "The value is out of range.") equals “The value is out of range.“ If B4 contains 50, then: IF(AND(1<B4, B4<100), B4, "The value is out of range.") equals 50 Back Objectives

  8. How do you use text comments inan IF Function? If you want the IF Function to produce text when condition is true or when false, the text must be surrounded by quotes “ “. See example below: Back Objectives

  9. How do you use a Null string inan IF Function? If you want the IF Function to produce no text when condition is true or when false, simply use the quotes ““ with no space in between. See example below: Back Objectives

  10. How do you nest a function in an IF Function? You can use a function within a function. This is called nesting. In the IF function below, there are two nested functions – the average and the sum. It reads: If the average of the range F2:F5 is greater than 50, then calculate the sum of the range G2:G5, if not produce the value zero. =If(Average(F2:F5)>50,Sum(G2:G5),0) Back Objectives

  11. How do you nest an IF Function in an IF Function? An IF function will produce two values – a value when a condition is true and a value when the condition is false. What if you need three values? Then nest an IF function. Example: = IF(A1<100,15%,IF(A1<200,20%,25%)) In this formula we need to charge a 15% discount if under $100, a 20% between 100 and 199, and 25% for anything over $200. Here we have three values needed and three values produced by using two IF functions, one nested in the other. Back Objectives

  12. IF Function Practice Practice Answers Objectives

  13. Function Functions are built in Formulas. Back Objectives

  14. Argument The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numeric values, text values, cell references, ranges of cells, names, labels, and nested functions. Back Objectives

  15. Logical, or Comparison, Operators Operator Meaning Example = (equal sign) Equal to A1=B1 > (greater than sign) Greater than A1>B1 < (less than sign) Less than A1<B1 >= (greater than or equal to sign) Greater than or equal to A1>=B1 <= (less than or equal to sign) Less than or equal to A1<=B1 <> (not equal to sign) Not equal to A1<>B1 Back Objectives

  16. Operators Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference. Back Objectives

  17. Arithmetic Operators Operator Meaning Example + (plus sign) Addition 3+3 – (minus sign) Subtraction/Negation 3–1/–1 (asterisk) Multiplication 3*3 / (forward slash) Division 3/3 % (percent sign) Percent 20% ^ (caret) Exponentiation 3^2 (the same as 3*3) Back Objectives

  18. Text Operator Operator Meaning & (ampersand) Connects, or concatenates, two values to produce one continuous text value Example: ="North" & "wind" produces Northwind Back Objectives

  19. Reference Operator Operator Meaning : (colon) Range operator, which produces one reference to all the cells between two references, including the two references Example: B5:B15 , (comma) Union operator, which combines multiple references into one reference Example: SUM(B5:B15,D5:D15) Back Objectives

  20. Logical Connector - AND And Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments is FALSE. Syntax: AND(logical1,logical2, ...) Logical1, logical2, ...   are 1 to 30 conditions you want to test that can be either TRUE or FALSE. AND(TRUE, TRUE) equals TRUE AND(TRUE, FALSE) equals FALSE AND(2+2=4, 2+3=5) equals TRUE Back Objectives

  21. Logical Connector - OR OR Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE. Syntax OR(logical1,logical2,...) Logical1,logical2,...   are 1 to 30 conditions you want to test that can be either TRUE or FALSE. • Examples OR(TRUE) equals TRUE OR(1+1=1,2+2=5) equals FALSE If A1:A3 contains the values TRUE, FALSE, and TRUE, then: OR(A1:A3) equals TRUE Back Objectives

  22. Nesting A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For example, the AVERAGE function and the SUM function below are both second-level functions because they are arguments of the IF function. A function nested within the AVERAGE function would be a third-level function, and so on. =If(Average(F2:F5)>50,Sum(G2:G5),0) Back Objectives

  23. User Guide • The IF function is an advanced topic. In order to achieve full benefit from this instructional unit, you should already know how to do the following: • Develop a spreadsheet • Build basic functions (Sum, Average, Count, Min, Max, Int, Round) • Understand relative and absolute references Click to move to previous slide. (Slide location – bottom left) Click to move to next slide. (Slide location – bottom right) Click to end Instructional Unit. (Slide location – top right) Back Click to move to last viewed slide. (Slide location – Bottom right) Back Click to return to Objectives. (Slide location – Bottom left) Objectives

More Related