Compound conditions: functions in Sheets, operators in R

Two conditions at once

"Flag the Ontario orders over $400." That is two tests on one row, and both must hold. The spreadsheet expresses both with a function, AND, placed where IF's condition goes; R expresses it with an operator, &, between two logical vectors. Same idea, different syntax — and the syntax is what the quiz tests.

Worked example — ON and over $400

In G2, filled down:

=IF(AND(B2="ON", D2>400), 1, 0)

AND takes any number of conditions and is TRUE only when all of them are; IF turns that into a 1 or a 0 — a numeric flag that SUM can total. Three orders qualify: 1001, 1003, 1006, with amounts 420 + 760 + 510 = 1690. OR is TRUE when any condition holds; NOT flips one. In R the comparison alone produces the flag:

> orders$prov == "ON" & orders$amount > 400
 [1]  TRUE FALSE  TRUE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE FALSE
> orders$order_id[orders$prov == "ON" & orders$amount > 400]
[1] 1001 1003 1006
> sum(orders$amount[orders$prov == "ON" & orders$amount > 400])
[1] 1690

& is and, | is or, ! is not, each working element-by-element across the column. The second line uses the logical vector as a row selector — Module 9 makes that the main way of subsetting — and the third sums the selected amounts. There is no IF in sight: a logical vector is the flag, and sum() counts its TRUEs.

Either ON or QC

"Orders from Ontario or Quebec" is an OR: =IF(OR(B2="ON", B2="QC"), 1, 0) — seven orders. In R, orders$prov=="ON"|orders$prov == "QC" works, and for a list of allowed values %in% is shorter:

> orders$prov %in% c("ON", "QC")
 [1]  TRUE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE FALSE
> sum(orders$prov == "ON" | orders$prov == "QC")
[1] 7

Writing AND(B2="ON", B2="QC") for this is the classic slip: no cell can equal both, so the flag is 0 everywhere.

&& is not a longer &

R has a second pair, && and ||. They are for single TRUE/FALSE values in program control, and on a column they stop:

> orders$prov == "ON" && orders$amount > 400
Error in orders$prov == "ON" && orders$amount > 400 : 
  'length = 12' in coercion to 'logical(1)'

Since R 4.3 that is an error, not a silent use of the first row. On columns, always the single & and |.

Type both, then sum the flagged amounts

Type the IF(AND()) and the R condition. The code block sums the amounts where both tests hold.

CRISP-DM: this is data preparation → construct data; when the flag is then used to keep rows, it becomes select data.