Logic Basics: Learn It 2

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 [latex]3 < 5[/latex] is true; a statement like “a rat is a fish” is false. A statement like “[latex]x < 5[/latex]” is true for some values of [latex]x[/latex] and false for others. When an action is taken or not depending on the value of a statement, it forms a conditional.

statements and conditionals

A statement is either true or false.

 

A conditional is a compound statement of the form: “if [latex]p[/latex] then [latex]q[/latex]” or  “if [latex]p[/latex] then [latex]q[/latex], else [latex]s[/latex] .”

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.

Conditional Statements and Excel

Conditional statements are commonly used in spreadsheet applications like Excel. In Excel, you can enter an expression like:

[latex]=\text{IF}(A1<2000, A1+1, A1*2)[/latex]

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 [latex]A1[/latex] and compare it to [latex]2000[/latex]. If that condition is true, then the first calculation is used, and [latex]1[/latex] is added to the value of [latex]A1[/latex] and the result is stored. If the condition is false, then the second calculation is used, and [latex]A1[/latex] is multiplied by [latex]2[/latex] and the result is stored.

In other words, this statement is equivalent to saying “If the value of [latex]A1[/latex] is less than [latex]2000[/latex], then add [latex]1[/latex] to the value in [latex]A1[/latex]. Otherwise, multiple [latex]A1[/latex] by [latex]2[/latex].”

Given the Excel expression:

[latex]\text{IF}(A1 > 5, 2*A1, 3*A1)[/latex]

Find the following:

  1. the result if [latex]A1[/latex] is [latex]3[/latex], and
  2. the result if [latex]A1[/latex] is [latex]8[/latex]

Re-representing expressions in helpful ways:

In the example below, you’ll need to convert numerical information from a real-world situation into a mathematical model of the situation.

Ex. Recall that to identify [latex]15\%[/latex] of [latex]$30,000[/latex] is to multiply [latex]30,000[/latex] by [latex]0.15[/latex].

First convert the percentage to decimal form by moving the decimal point [latex]2[/latex] places to the left and dropping the [latex]\%[/latex] symbol. Then multiply [latex]30,000 \cdot 0.15[/latex].

As we did earlier, we can create more complex conditions by using the operators and, or, and not to join simpler conditions together. Let’s try a simple example before we apply this to Excel.

A parent might say to their child “if you clean your room and take out the garbage, then you can have ice cream.” Under what circumstances will this conditional be true?

Suppose you wanted to have something happen when a certain value is between [latex]100[/latex] and [latex]300[/latex]. To create the condition “[latex]A1 < 300 \text{ and } A1 > 100[/latex]” in Excel, you would need to enter “[latex]\text{AND}(A1<300, A1>100)[/latex]”. Likewise, for the condition “[latex]A1=4 \text{ or } A1=6[/latex]” you would enter “[latex]\text{OR}(A1=4, A1=6)[/latex].”

In a spreadsheet, cell [latex]A1[/latex] contains annual income, and [latex]A2[/latex] contains number of dependents. A certain tax credit applies if someone with no dependents earns less than [latex]$10,000[/latex] and has no dependents, or if someone with dependents earns less than [latex]$20,000[/latex]. Write a rule that describes this.