17.3: Conditional Statements
Beyond searching, Boolean logic is commonly used in spreadsheet applications like Excel to do conditional calculations. A statement is something that is either true or false. A statement like 3 < 5 is true; a statement like “a rat is a fish” is false. A statement like “\(x < 5\)” is true for some values of \(x\) and false for others. When an action is taken or not depending on the value of a statement, it forms a conditional .
A statement is either true or false.
A conditional is a compound statement of the form
"if \(p\) then \(q\)" or "if \(p\) then \(q\), else \(s\)"
In common language, an example of a conditional statement would be “If it is raining, then we’ll go to the mall. Otherwise we’ll go for a hike.”
The statement “If it is raining” is the condition – this may be true or false for any given day. If the condition is true, then we will follow the first course of action, and go to the mall. If the condition is false, then we will use the alternative, and go for a hike.
As mentioned earlier, conditional statements are commonly used in spreadsheet applications like Excel or Google Sheets. In Excel, you can enter an expression like
\(=\mathrm{IF}\left(\mathrm{A} 1<2000, \mathrm{A} 1+1, \mathrm{A} 1 \times 2\right)\)
Notice that after the IF, there are three parts. The first part is the condition, and the second two are calculations. Excel will look at the value in cell A1 and compare it to 2000. If that condition is true, then the first calculation is used, and 1 is added to the value of A1 and the result is stored. If the condition is false, then the second calculation is used, and A1 is multiplied by 2 and the result is stored.
In other words, this statement is equivalent to saying “If the value of A1 is less than 2000, then add 1 to the value in A1. Otherwise, multiply A1 by 2”.
The expression \(=\mathrm{IF}\left(\mathrm{A} 1>5,2 \times \mathrm{A} 1,3 \times \mathrm{A} 1\right)\) is used. Find the result if A1 is 3, and the result if A1 is 8.
Solution
If \(A 1>5,\) then calculate \(2 \times A 1 .\) Otherwise, calculate \(3 \times A 1\).
If \(A 1\) is \(3,\) then the condition is false, since \(3>5\) is not true, so we do the alternate action, and multiply by \(3,\) giving \(3 \times 3=9\)
If \(A 1\) is \(8,\) then the condition is true, since \(8>5,\) so we multiply the value by \(2,\) giving \(2 \times 8=16\)
An accountant needs to withhold 15% of income for taxes if the income is below $30,000, and 20% of income if the income is $30,000 or more. Write an expression that would calculate the amount to withhold.
Our conditional needs to compare the value to 30,000. If the income is less than 30,000, we need to calculate 15% of the income: 0.15*income. If the income is more than 30,000, we need to calculate 20% of the income: 0.20*income.
In words we could write “If income < 30,000, then multiply by 0.15, otherwise multiply by 0.20”. In Excel, we would write:
\(=\mathrm{IF}\left(\mathrm{A} 1<30000,0.15 \times \mathrm{A} 1,0.20 \times \mathrm{A} 1\right)\)
As we did earlier, we can create more complex conditions by using the operators and , or , and not to join simpler conditions together.
A parent might say to their child “if you clean your room and take out the garbage, then you can have ice cream.”
Here, there are two simpler conditions:
1) The child cleaning her room
2) The child taking out the garbage
Since these conditions were joined with and , the combined conditional will be true only if both simpler conditions are true; if either chore is not completed, then the parent’s condition is not met.
Notice that if the parent had said “if you clean your room or take out the garbage, then you can have ice cream”, then the child would need to complete only one chore to meet the condition.
Suppose you wanted to have something happen when a certain value is between 100 and 300. To create the condition “A1 < 300 and A1 > 100” in Excel, you would need to enter “AND(A1<300, A1>100)”. Likewise, for the condition “A1=4 or A1=6” you would enter “OR(A1=4, A1=6)”
In a spreadsheet, cell A1 contains annual income, and A2 contains number of dependents.
A certain tax credit applies if someone with no dependents earns less than $10,000, or if someone with dependents earns less than $20,000. Write a rule that describes this.
There are two ways the rule is met:
income is less than 10,000 and dependents is 0, or
income is less than 20,000 and dependents is not 0.
Informally, we could write these as
\((A 1<10000 \text { and } A 2=0)\) or \((A 1<20000 \text { and } A 2>0)\)
In Excel’s format, we’d write
\(\text { IF }(\mathrm{OR}(\mathrm{AND}(\mathrm{A} 1<10000, \mathrm{A} 2=0), \mathrm{AND}(\mathrm{A} 1<20000, \mathrm{A} 2>0)),\text { "you qualify", "you don't qualify") }\)