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 less than 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.

Example 2

As mentioned earlier, conditional statements are commonly used in spreadsheet applications like Excel or Google Sheets. In Excel, you can enter an expression like

$= I F (A 1 < 2000, A 1 + 1, A 1 \times 2)$

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

Example 4

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.

# Excel:  =IF(A1<30000, 0.15*A1, 0.20*A1)
CUTOFF = 30000        # EDIT
LOW_RATE = 0.15       # EDIT
HIGH_RATE = 0.20      # EDIT

def withhold(income):
    return LOW_RATE * income if income < CUTOFF else HIGH_RATE * income

print(f"=IF(A1<{CUTOFF}, {LOW_RATE:.2f}*A1, {HIGH_RATE:.2f}*A1)\n")
print(f"{'income':>10}{'condition A1<' + str(CUTOFF):>20}{'branch taken':>16}{'withhold':>12}")
for income in (12000, 29999, 30000, 30001, 52000):
    cond = income < CUTOFF
    branch = f"{LOW_RATE:.2f}*A1" if cond else f"{HIGH_RATE:.2f}*A1"
    print(f"{income:>10,}{str(cond):>20}{branch:>16}{withhold(income):>12,.2f}")
print(f"\nwatch the cliff at the cutoff: earning $2 more at {CUTOFF:,} costs "
      f"{withhold(CUTOFF) - withhold(CUTOFF - 2):,.2f} extra in withholding")

# Excel:  =IF(OR(AND(A1<10000,A2=0), AND(A1<20000,A2>0)), "you qualify", "you don't qualify")
print("\na tax credit rule built from AND and OR")
def qualifies(income, dependents):
    return (income < 10000 and dependents == 0) or (income < 20000 and dependents > 0)

print(f"{'income':>10}{'dependents':>12}{'A1<10000 and A2=0':>20}"
      f"{'A1<20000 and A2>0':>20}   verdict")
for income, deps in ((8000, 0), (12000, 0), (12000, 2), (25000, 3), (9000, 1)):
    a = income < 10000 and deps == 0
    b = income < 20000 and deps > 0
    verdict = "you qualify" if qualifies(income, deps) else "you do not qualify"
    print(f"{income:>10,}{deps:>12}{str(a):>20}{str(b):>20}   {verdict}")
print("\nOR means either branch is enough; AND means both halves of a branch must hold.")
# Try it: change the parent's rule from AND to OR - "clean your room OR take out the
# garbage, then you can have ice cream" - and see how many more rows qualify.

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:

$= I F (A 1 < 30000, 0.15 \times A 1, 0.20 \times A 1)$

As we did earlier, we can create more complex conditions by using the operators and, or, and not to join simpler conditions together.

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)”

Example 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

$open parenthesis A 1 less than 10000 and A 2 equals 0 close parenthesis$ or $open parenthesis A 1 less than 20000 and A 2 greater than 0 close parenthesis$

In Excel’s format, we’d write

$IF open parenthesis O R open parenthesis A N D open parenthesis A 1 less than 10000, A 2 equals 0 close parenthesis, A N D open parenthesis A 1 less than 20000, A 2 greater than 0 close parenthesis close parenthesis, "you qualify", "you don't qualify")$

Adapted from Math in Society by David Lippman, hosted on LibreTexts (math.libretexts.org) and licensed under CC BY-SA 3.0. Changes were made. License: CC-BY-SA-3.0.