IF Function

1. IF Function Decision: Action based on condition

2. Examples • SF State Tuition calculation: • http://bulletin.sfsu.edu/sfstatebulletin/fees/fee/Fees_and_Expenses • Simple condition • PG&E electric charges: • http://www.pge.com/en/myhome/saveenergymoney/plans/tiers/index.page • More than one condition

3. 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 • Taxable income > 10000 15% tax • Complex condition: • If an applicant’s GPA > 3.0 and SAT > 1200: admitted

4. 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.

5. IF Function • =IF(condition, ValueIfTrue,ValueIfFalse) • Example: • Tuition: If total units <= 12, then tuition = 1200 • Otherwise, tuition = 1200 + 200 per additional unit • In Cell C2: IF(B2<=12, 1200, 1200+200*(b2-12))

6. Example: Compute weekly wage. Overtime hours are paid 50% more than the regular pay. • In Cell D2: If(C2<=40, B2*C2, B2*40 + 1.5*B2*(C2-40))

7. Example: Tax rate is based on married status: • Single: 15% • Married: 10% • In D5: If(B5=“S”,$C$1*C5, $C$2*C5)

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

9. Exercises • 1. The average of two exams is calculated by this rule: 60% * higher score + 40% * lower score. • 2. An Internet service provider offers a service plan that charges customer based on the following rules: • The first 20 hours: $10 • Each additional hour: $1.5

10. 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))

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

12. 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), 25 cents per KH • All KH over 300, 30 cents per KH • In C2: If(B2<=100, .2*B2,If(B2<=300,.2*100+.25*(B2-100),.2*100+.15*200+.3*(B2-300))

13. 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

14. 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.

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

16. Date & Time Functions • How Excel handles dates: • Serial #: 1/1/1900 – day 1 • Functions: • Today() – today’s date • Now() – current date and time • Year(a date) • Month(a date) • Weekday(a date)

17. Examples • Tomorrow’s date? • How many days to Christmas? • Compute the age given a date of birth.