IF and ifelse, and the case-sensitivity conflict

A flag is a derived variable with two values

Management wants orders split into Large (at least $500) and Small. That is a new column whose value depends on a condition — a comparison that is TRUE or FALSE for each row. The tool for it is IF in the spreadsheet and ifelse in R, and both take the same three things in the same order: the condition, the value when it is true, the value when it is false.

Worked example — the size flag

With amount in column D, in G2:

=IF(D2>=500, "Large", "Small")

filled down. The text results sit in quotes; without them Sheets looks for a named range called Large and returns #NAME?. Five orders are Large — 1003, 1005, 1006, 1008, 1010 — and seven Small. The comparison operators are =, <> (not equal), <, >, <=, >=. In R:

> orders$size <- ifelse(orders$amount >= 500, "Large", "Small")
> table(orders$size)

Large Small 
    5     7 

ifelse(test, yes, no) is vectorised: the test is evaluated for all twelve rows at once and the result is a twelve-element vector. R's operators are == (two signs, because a single = is assignment), !=, <, >, <=, >=. Writing orders$amount = 500 inside a condition does not compare anything — it tries to assign.

The classic R error

R also has a plain if, and it is the wrong tool here:

> if (orders$amount > 500) "Large" else "Small"
Error in if (orders$amount > 500) "Large" else "Small" : 
  the condition has length > 1

Base if takes one TRUE or FALSE and chooses one branch; it is for program control, not for columns. Since R 4.2 a condition of length 12 is an error rather than a warning. if (orders$amount[1] > 500) … works — a single value — and returns a single "Small". On a column, always ifelse.

Where the tools disagree: case

The channel column holds "Online" and "Store". Compare it against lowercase text:

• Sheets / Excel: =C2="online" → TRUE. Text comparison with = ignores case, as COUNTIF did in Lesson 4.1. EXACT(C2, "online") is the case-sensitive version, and it returns FALSE.
• R: orders$channel == "online" → FALSE, all twelve times.

> orders$channel == "online"
 [1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
> sum(orders$channel == "Online")
[1] 6

The second line is the useful idiom: sum() of a logical vector counts the TRUEs, so it is R's COUNTIF. A flag that works in the spreadsheet and silently returns all-FALSE in R is nearly always a case mismatch — clean with toupper (Lesson 5.2) or match the exact spelling.

Type both, then count the flags

Type the IF and the ifelse. The code block builds the flag column in Python with a conditional expression and counts the Large orders.

CRISP-DM: this is data preparation → construct data.