1 / 15

Conditional Logic Using Excel “IF” Statements

Conditional Logic Using Excel “IF” Statements. Some “plain-English” examples of conditional logic statements. My grading scale—(yours might be different). Example 1: A simple conditional logic test

zlhna
Télécharger la présentation

Conditional Logic Using Excel “IF” Statements

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. Conditional Logic Using Excel “IF” Statements

  2. Some “plain-English” examples of conditional logic statements

  3. My grading scale—(yours might be different)

  4. Example 1: A simple conditional logic test Based on the grading scale shown in the previous slide, create a conditional logic statement that determines if each of the values in column K (“Pct of Tot Pts Possible”) represents a passing grade (“Pass”) or a failing grade (“Fail”), and store the results in column M ( “Pass or “Fail”).

  5. The basic form of an “If” statement is: =IF(Logical Test is True,Then,Else)

  6. The conditional logic statement for the first case is: =IF(K2>=59.5,“Pass","Fail")

  7. The conditional logic statements for all cases are: For the value in K2 (i.e., 73), the conditional logic statement would be: =IF(K2>=59.5,”Pass”,”Fail”) For the value in K3 (i.e., 89.6), the conditional logic statement would be: =IF(K3>=59.5,”Pass”,”Fail”) For the value in K4 (i.e., 59.4), the conditional logic statement would be: =IF(K4>=59.5,”Pass”,”Fail”) For the value in K5 (i.e., 79.8), the conditional logic statement would be: =IF(K5>=59.5,”Pass”,”Fail”) For the value in K6 (i.e., 67.2), the conditional logic statement would be: =IF(K6>=59.5,”Pass”,”Fail”)

  8. Example 2: “Nested” conditional logic tests Based on the grading scale shown previously, create a conditional logic statement that will determine the letter grade for each of the values in column K (“Pct of Tot Pts Possible”), and store the results in column L (“Ltr Grade”).

  9. The conditional logic statement for the first case is: =IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F")))) Displayed on the next four slides are its four ”nested” component parts…

  10. =IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F"))))=IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F"))))

  11. =IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F“))))=IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F“))))

  12. =IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F“))))=IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F“))))

  13. =IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F"))))=IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F"))))

  14. The conditional logic statements for all cases are: For the value in K2 (i.e., 73), the conditional logic statement would be: =IF(K2>=89.5,"A",IF(K2>=79.5,"B",IF(K2>=69.5,"C",IF(K2>=59.5,"D","F")))) For the value in K3 (i.e., 89.6), the conditional logic statement would be: =IF(K3>=89.5,"A",IF(K3>=79.5,"B",IF(K3>=69.5,"C",IF(K3>=59.5,"D","F")))) For the value in K4 (i.e., 59.4), the conditional logic statement would be: =IF(K4>=89.5,"A",IF(K4>=79.5,"B",IF(K4>=69.5,"C",IF(K4>=59.5,"D","F")))) For the value in K5 (i.e., 79.8), the conditional logic statement would be: =IF(K5>=89.5,"A",IF(K5>=79.5,"B",IF(K5>=69.5,"C",IF(K5>=59.5,"D","F")))) For the value in K6 (i.e., 67.2), the conditional logic statement would be: =IF(K6>=89.5,"A",IF(K6>=79.5,"B",IF(K6>=69.5,"C",IF(K6>=59.5,"D","F"))))

  15. Some Important Things to Remember About Nested Conditional Logic Statements: • You are limited to a maximum of 7 logical tests when creating nested conditional logic statements. • Separate the 3 parameters for each conditional logic statement with commas. • For each conditional logic statement, you must enclose its parameters inside a separate set of parenthesis. • For each conditional logic statement, if the “then” and “else” parameters are fixed characters (like letters or numbers), enclose them in a pair of double quotes (i.e., “).

More Related