Blank, NA, −999, and why they must be declared

Two tools, opposite defaults

Blank one amount — say order 1005's $1,320 — and ask each tool for the average.

Sheets / Excel: =AVERAGE(D2:D13) returns 438.18. No warning. The function quietly averaged the eleven cells that had a number and ignored the blank. That is convenient and dangerous in equal measure: the number looks like an average of twelve orders and is not.

R: mean(orders$amount) returns

[1] NA

R's position is that the mean of twelve values, one unknown, is unknown. To get the average of the eleven you must say so: mean(orders$amount, na.rm = TRUE) returns [1] 438.1818. The argument name is na.rm ("remove NA") and its default is FALSE — for mean, sum, sd, median alike. This is the first R gotcha in the course, and it is a design choice, not a bug: R makes the missing value impossible to overlook; the spreadsheet makes it impossible to notice.

Count the missing before you remove them. is.na(orders$amount) gives a logical vector; by Lesson 2.5's idiom, sum(is.na(orders$amount)) is the number of missing values — [1] 1 here. That number belongs in the data-quality note.

The sentinel that both tools average

Some systems do not leave a missing value blank. They write a placeholder — −999, 0, 9999, "n/a" — a sentinel. Put −999 in place of the blank and ask again. =AVERAGE(D2:D13) returns 318.42. mean(orders$amount) returns [1] 318.4167. Neither tool objects: −999 is a perfectly good number, and both averaged it. The true average of the known orders is 438.18; the sentinel dragged it down by $120 and nothing on screen says so.

The only defence is the dictionary. The missing code field in Lesson 2.4 exists for this: the row for amount says missing = −999, and the preparation step that follows replaces −999 with a blank (Sheets) or NA (R: orders$amount[orders$amount == -999] <- NA) before any average is taken. A −999 that is not declared is not a missing value; it is a data error waiting for a pivot.

Type both calls

The two snippets are the R lines that make a missing value explicit: the average with na.rm = TRUE, and the count of NAs. Then answer cold: what dictionary entry prevents the −999 mistake? The missing code for the column, written before any summary is computed.

CRISP-DM: finding and counting the missing values is data understanding → verify data quality; replacing a sentinel with a blank or NA is data preparation → clean data.