1 / 32

Decisions

Learn various conditions and examples for decision-making in Excel, from simple to complex, with logical operators and functions. Explore practical practices and rules for automating calculations.

johncbrown
Download Presentation

Decisions

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. Decisions Action based on condition

  2. Examples • Simple condition: • If total sales exceeds $300 then applies 5% discount; otherwise, no discount. • More than one condition: • Taxable Income < =3000 no tax • 3000 < taxable income <= 10000 5% tax • 10000 < taxable income <= 50000 10% tax • Taxable income > 50000 15% tax • Complex condition: • If an applicant’s GPA > 3.0 and SAT > 1200: admitted

  3. Comparison • Less than: < • Less than or equal: <= • Greater than: > • Greater than or equal: >= • Equal: = • Not equal: <> • At least: >= • At most: <= • No more than: <= • No less than: >= • A comparison returns True/False. • Example:

  4. IF Function • =IF(condition, ValueIfTrue,ValueIfFalse) • Example: • Tuition: If total units <= 12, then tuition = 1200 • Otherwise, tuition = 1200 + 200 per additional unit

  5. Example: Compute weekly wage. Overtime hours are paid 50% more than the regular pay.

  6. Example: Tax rate is based on married status: • Single: 15% • Married: 10%

  7. Example: A restaurant charges service fee based on this rule: 15% of the check amount with a minimum of $2.

  8. Practices • 1. How to determine an integer entered in a cell is an even number or an odd number? • 2. The average of two exams is calculated by this rule: 60% * higher score + 40% * lower score. • 3. An Internet service provider offers a service plan that charges customer based on this rule: • The first 20 hours: $10 • Each additional hour: $1.5 • 4. Automate the calculation of AmountOfDepDuringYr of the double declining depreciation table of assignment 2 for any value of life.

  9. Calculating Conditional Counts and Sums • COUNTIF(range, criteria) • Range of cells • Criteria: expression that define which cells are to be counted. • SUMIF(range, criteria,[sum_range]) • Sum_range: The cells you want to sum. If omit, Excel will sum the values in the range argument.

  10. Example • How many employees earn more than 5000? • COUNTIF(B2:b4,”>5000”) 2. Compute the total salary of employees earning more than 5000. SUMIF(B2:b4,”>5000”) 3. How many employees’ name begin with “P”? COUNTIF(A2:A4,”P*”)

  11. Complex Condition • Examples: • A theater charges admission fee based on customer’s age: • 12 <= Age <= 65: Fee = $5 • Otherwise: Fee = $3 • X University admission rules: • If GPA > 3.5 or SAT > 1500: Admitted • Y University admission rules: • If GPA > 3.0 and SAT > 1200: Admitted

  12. Logical Operators: AND, OR, NOT • AND • Cond1 Cond2 Cond1 AND Cond2 T T T F F T F F • OR • Cond1 Cond2 Cond1 OR Cond2 T T T F F T F F • NOT • Cond NOT Cond T F

  13. Examples • Write a complex condition for: 12 <= Age <= 65 • Use a complex condition to describe age not between 12 and 65. • X <= 15 is equivalent to: X<15 AND X =15? (T/F) • This complex condition is always false: • X < 5 AND X > 10 • This complex condition is always true: • X >= 5 OR X <= 10

  14. AND Function/OR Function • =AND (cond1, cond2, …, cond n) • Up to 30 conditions • True if all conditions are true • False if any condition is false • =OR (cond1, cond2, …, cond n) • True if any condition is true • False if all conditions are false

  15. The rules to compute employee bonus are: • If JobCode = 1 and Salary < 50000, then bonus = 30% * salary • Otherwise, bonus = 20% * salary Note: Change the rules to OR

  16. Nested IF and Complex Condition

  17. Nested IF • Example: • Rules to determine bonus: • JobCode = 1, Bonus=500 • JobCode = 2, Bonus = 700 • JobCode = 3, Bonus = 1000 • In C2: If(B2=1, 500, If(B2=2, 700, 1000))

  18. 1 JobCode= 1 Or <> =1 2 JobCode=2 Or <> 2 3

  19. Example • Electric Company charges customers based on KiloWatt-Hour used. The rules are: • First 100 KH, 20 cents per KH • Each of the next 200 KH • (up to 300 KH), 15 cents per KH • All KH over 300, 10 cents per KH

  20. What if we have more than 3 conditions • Example: Bonus • JobCode = 1, Bonus = 500 • JobCode = 2, Bonus = 600 • JobCode = 3, Bonus = 700 • JobCode = 4, Bonus = 800 • JobCode = 5, Bonus = 1000 • … • Other functions: • Table lookup

  21. Example • State University calculates students tuition based on the following rules: • State residents: • Total units taken <=12, tuition = 1200 • Total units taken > 12, tuition = 1200 + 200 per additional unit. • Non residents: • Total units taken <= 9, tuition = 3000 • Total units taken > 9, tuition = 3000 + 500 per additional unit.

  22. Units <= 12 or Not Resident or Not Units <= 9 or Not

  23. Complex Condition • University admission rules: Applicants will be admitted if meet one of the following rules: • 1. Income >= 100,000 • 2. GPA > 2.5 AND SAT > 900 • An applicant’s Income is 150,000, GPA is 2.9 and SAT is 800. Admitted? • Income >= 100,000 OR GPA > 2.5 AND SAT >900 • How to evaluate this complex condition?

  24. Scholarship: Business students with GPA at least 3.2 and major in Accounting or CIS qualified to apply: • 1. GPA >= 3.2 • 2. Major in Accounting OR CIS • Is a CIS student with GPA = 2.0 qualified? • GPA >= 3.2 AND Major = “Acct” OR Major = “CIS” • Is this complex condition correct?

  25. NOT Set 1: Young: Age < 30 Set 2: Rich: Income >= 100,000 Young Rich

  26. Condition with Not • University admission rules: Applicants will be admitted if meet all the rules: • 1. SAT > 900 OR Income >= 50,000 • 2. Not GPA < 2.5 • Condition: • SAT > 900 OR Income >= 50,000 AND Not GPA < 2.5 • Correct?

  27. Order of Evaluation • 1. () • 2. Not • 3. AND • 4. OR

  28. Examples • SAT = 800, Income 60,000, GPA 3.0, admitted? • (SAT > 900 OR Income >= 50,000) AND Not GPA < 2.5 • A=2, B=3 • (A=3 OR NOT (B < A)) AND B=A+1

  29. Rules to calculate employee bonus are: • If JobCode = 1 AND Salary < 5000 OR Sex = “F” Bonus = 10% * Salary • Otherwise: Bonus = 8% * Salary

  30. Practices • 1. Change the rules for bonus to: • If Sex = “F” AND (JobCode = 1 OR JobCode = 2) then bonus = 10% * Salary • Otherwise, bonus = 8% * Salary • 2. Change the rules to: • If JobCode = 1 AND Salary < 5000 OR Sex = “F” Bonus = 10% * Salary • Otherwise: Bonus = 8% * Salary • Use a complex condition to describe numbers: 20 50 100

  31. Set 1: Young: Age < 30 Set 2: Rich: Income >= 100,000 Set 3: Smart: GPA > 3.5 Smart Young Rich

More Related