150 likes | 248 Views
If Function. Determine action based on condition. 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
E N D
If Function Determine action based on condition
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
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:
IF Function • =IF(condition, ValueIfTrue,ValueIfFalse) • Example: • Tuition: If total units <= 12, then tuition = 1200 • Otherwise, tuition = 1200 + 200 per additional unit
Example: Tax rate is based on married status: • Single: 15% • Married: 10%
Example: Compute weekly wage. Overtime hours are paid 50% more than the regular pay.
Example: A restaurant charges service fee based on this rule: 15% of the check amount with a minimum of $2.
Exercises • 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
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))
1 JobCode= 1 Or <> =1 2 JobCode=2 Or <> 2 3
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), • (100 < KH <= 300), 30 cents per KH • All KH over 300, 40 cents per KH
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
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.
Units <= 12 or Not Resident or Not Units <= 9 or Not