One formula per row in Sheets, one line in R

A column the data did not come with

The orders sheet records amount and units, but the question on the desk is what was the price per unit? and how much HST did each order carry? Neither is stored. A derived variable is a new column computed from existing ones, and building it is the single most common data-preparation step: CRISP-DM calls it construct data.

Worked example — unit price and HST, in Sheets

With amount in column D and units in column E, click G2 and type

=D2/E2

Order 1001 shows 140 (420 / 3). Now fill it down to G13: every reference is relative, so row 3 computes D3/E3, row 4 D4/E4, and so on. That is the whole method — one formula in the first data row, filled down — and it depends on Module 3's rule that a relative reference moves with the copy. Write =$D$2/$E$2 instead and every row repeats 140.

The HST column is the same move with one extra function. Ontario's HST is 13 %, and money is reported to the cent, so in H2:

=ROUND(D2*0.13, 2)

Filled down: 54.60 · 23.40 · 98.80 · 32.50 · 171.60 · 66.30 · 11.70 · 83.20 · 39.65 · 127.40 · 27.95 · 61.10. ROUND takes the value and the number of decimal places; ROUND(x, 0) gives a whole number and ROUND(x, -2) rounds to the nearest hundred.

The same two columns in R

R has no fill handle and needs none. A column is a vector, and arithmetic on a vector is already element-by-element:

> orders$unit_price <- orders$amount / orders$units
> orders$hst <- round(orders$amount * 0.13, 2)
> head(orders, 3)
  order_id prov channel amount units order_date unit_price  hst
1     1001   ON  Online    420     3 2025-01-06        140 54.6
2     1002   QC   Store    180     1 2025-01-08        180 23.4
3     1003   ON   Store    760     5 2025-01-15        152 98.8

Assigning to orders$unit_price — a column that did not exist — creates it. One line does what the fill-down did, and a loop over the rows would be the beginner's mistake, not the idiom.

Where the tools disagree: rounding a .5

ROUND in Sheets and Excel rounds a half away from zero: =ROUND(2.5, 0) is 3, =ROUND(-2.5, 0) is −3. R and Python round a half to the nearest even digit (the IEC 60559 rule, because it does not bias a long column of sums upward):

> round(2.5)
[1] 2
> round(3.5)
[1] 4
> round(0.5)
[1] 0

None of the twelve HST values falls on a half-cent, so the two columns agree to the cent here — but a quiz that asks what round(2.5) returns in R is asking whether you know the rule, and the answer is 2.

Percent change is not percentage points

One more derived quantity turns up on every paper. Monthly sales went from 1610 in January to 2560 in February: the percent change is (2560 − 1610) / 1610 = 59.0 %. Ontario's share of online orders is 30.4 % and its share of the grand total 32.5 %: the gap is 2.1 percentage points, not 2.1 %. A change in a percentage is measured in points; a change in a quantity is a percentage of the starting value.

Three cousins force the direction instead of following the half. ROUNDUP(2.01, 0) → 3 and ROUNDDOWN(2.99, 0) → 2 always move away from or toward zero; INT(2.99) → 2 drops the fraction. R's twins are ceiling(2.01) → 3 and floor(2.99) → 2, and signif(1234.5, 2) → 1200 rounds to significant digits rather than decimal places — the call for a headline figure, not for a ledger.

Type both pairs, then compute the HST

Type the spreadsheet ROUND and the two R lines. The numeric item asks for order 1005's HST; the code block builds the HST column in Python, where round follows R's half-to-even rule — which is why no shipped answer ever sits on a .5.

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