Using Logical Functions

1. IF AND OR NOT Using Logical Functions

2. Objectives • Using IF to evaluate a single condition • Using the AND function for multiple conditions • Using the OR function for multiple conditions • Using the NOT function to take the inverse result of an AND or OR function • Nested IF Functions to be used to calculate 3 or more different outcomes • Consider using a Lookup table if the number of conditions to be tested is large.

3. A little more Excel humor: A pilot is flying a small, single-engine charter plane with a couple of really important execs on board into a Seattle airport. There is fog so thick that visibility is only 40 feet, and his instruments are out. He circles, looking for a landmark. After an hour, he is low on fuel and his passengers are very nervous. At last, through a small opening in the fog, he sees a tall building with one guy working alone on the fifth floor. Circling, the pilot banks and shouts through his open window, "Hi, where am I?" The solitary office worker replies, "You're in an airplane." The pilot executes a swift 275 degree turn and makes a perfect blind landing on the airport's runway five miles away. Just as the plane stops, the engines cough and die from lack of fuel. The stunned passengers are relieved, and ask the pilot how he did it. "Simple," replies the pilot, "I asked the guy in that building a simple question. The answer he gave me was 100% correct, but absolutely useless. Therefore, that must have been the Microsoft Excel support office. I know that, from there, the airport is three minutes away on a heading of 87 degrees."

4. Logical Functions (And / Or / Not) • These functions are not computational: • They return a “True” or “False” value based on the conditional test. • They are used to test for conditions to be used for further analysis. • When combined with other functions, they can be used to perform actions on data, based on returned value of the logical function. • They can be used in a Cell or nested within an =IF function.

5. Working with the Logical =IF Function • The IF function is a logical function that returns a TRUE value if the logical condition is true and a FALSE value if the logical conditions are false. • The TRUE and FALSE values can be flags that can be used in additional formulas, actual values or operations, or even other formulas • IF Function syntax • IF(logical_test, value_if_true, [value_if_false])

6. IF Function String values must be in Quotes It is not case sensitive.

7. Game Bracket Copy A2:C4 and paste into A6 to create a 2nd bracket of Games =IF(B6>B8,A6,A8)

8. Game Bracket – Round 2 =IF(B6>B8,A6,A8) Add values in D3 and D7 and create a formula in E5 to show the winner =IF(D3>D7,C3,C7) Can you copy the cells from A2:E8 into A10, and add the logic for ROUND 3? Now copy the cells for the 8 teams and add the logic for ROUND 4 You now have the template for a 16 team Regional Tournament

9. Nested If What happens now if a TIE score is entered in B2 and B4? WHY? =IF(B2=B4,”TIE”,IF(B2>B4,A2,A4)) This example demonstrates a Nested IF. While in “real life” we assume there is only one “winner” and “Loser” what if we allowed for the possibility of a TIE. =IF(B2=B4,”TIE”,IF(B2>B4,A2,A4))

10. Working with =AND Function • The AND function is a logical function that returns a TRUE value if allof the logical conditions are true and a FALSE value if any of the logical conditions are false. • Can test up to 255 logical conditions • =AND(cond1, Cond2, Cond3….)

11. AND Function In E2: =AND(C2=“FT”,D2>=1) In F2: =IF(E2,B2*.03,0)

12. Working with Logical Functions NESTED IF

13. Working with Logical Functions • A nested IF function is when one IF function is placed inside another IF function to test for multiple outcomes • Can allow for three or more outcomes, instead of just two • =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000, IF([Pay Grade]=3, 7500,"Invalid pay grade")))

14. Working with Logical Functions=OR • The OR function is a logical function that returns • TRUEvalue if any of the logical conditions are true • FALSEvalue if all the logical conditions are false • =OR(logical_test_1, logical_test_2,…)

15. Working with Logical Functions=NOT • Takes the Opposite of a returned condition • =NOT(logical_test)

16. Working with Logical Functions If Years is Less than or equal to 1 or Salary > 100,000, there is no Bonus Otherwise, check the Pay Grade code to assign a constant value • =IF(OR([Years Service]<=1,[Annual Salary]>100000),0, IF([Pay Grade]=1,$T$1,IF([Pay Grade]=2,$T$2, IF([Pay Grade]=3,$T$3,"Invalid pay grade"))))

17. Checking Formulas • Check your parentheses • All functions have an opening and closing parentheses • Correct number • Correct position • Excel uses color coding to help you keep track of items in a formula • You don’t use the “=“ inside of Nested functions.

18. Example Electoral College Every four years, the United States has an election to pick the President. A system is used to assign “VOTES” based on the population of the State. And the number of representatives the state has. It is a “Winner Take All” – Whoever wins a majority of the votes in the state gets ALL the Electoral Votes This example uses an =IF function to assign the votes to a Republican 1=(RED) or Democrat 2=(BLUE) Maybe a Data Validation should be assigned to the input Cells for the Data Entry in the E and H columns There is also a condition in Column J to compare the Predicted Result with the Actual Result..

19. Exercise – Payroll Record • Use the IF function to calculate Regular hours worked (<=40) • Use the IF statement to calculate if there are any OverTime Hours • Use the If statement to calculate Overtime Rate If there was Overtime Hours.

20. Exercise – Payroll Record Part2 • Use a Nested IF function to calculate Deductions based on a code. • The code of 1 = 3% • The code of 2 = 6% • The Code of 3 = 8% • You also need to code INVALID data entry codes.