Level 6 · Missing values, and a reason for each fix

A blank cell is a question, not an answer. The cost wasn’t recorded, or it was lost, or it doesn’t apply. Before anything else, find out how many there are and decide, column by column, what a blank should become. Then write the decision down next to the data.

Find the gaps

Method	How
Count	=COUNTBLANK(E2:E41) counts the empty cells: 8 here
Filter	Data > Filter, open the column’s arrow: a (Blanks) entry means there are empty cells; tick only it to see them
Compare	=COUNTA(E2:E41) gives 32 filled cells out of 40 rows

Run the count on every column before you analyse a new export. It is the fastest part of a first look at data quality: here unit_price has no blanks, unit_cost has 8 (products 101, 111, 115, 119, 120, 203, 206 and 207).

Three ways to treat a blank

Option	When it fits	Here
Drop the row	few blanks, many rows, and the row isn’t needed	no: 8 of 40 is a fifth of the range, and the products sell
A fixed value	the blank has a clear meaning (0, Unknown, not recorded)	no: a cost of 0 makes every margin 100%
An estimate (median, average)	the value exists but is unknown, and a typical value is defensible	yes, with a label

The same store makes different choices in different columns. customers.city is blank for 14 customers: write Unknown and keep them, since an estimated city means nothing. customers.referral_source is blank for 666: that is “not recorded”, a real answer that belongs in its own category, not a gap to fill.

Estimate from a share, with the median

A median cost would be a bad estimate: =MEDIAN(E2:E41) is 200.49, which is more than the $53.99 price of the grill in row 24, and an eighth of the loveseat’s. These products run from $50.99 to $1,659. Cost scales with price, so estimate the share of the price that is cost, then apply it:

F2   =IF(E2="","",E2/D2)
J2   =MEDIAN(F2:F41)
G2   =IF(E2="",D2*$J$2,E2)

F3 shows 54.8% (503.50 / 919). MEDIAN sorts the 32 shares and takes the middle (with 32 values, the average of the two middle ones): 45.1%. It skips the empty text in the eight blank rows, like every function that summarises a range. The average share is close here, 44.9%, but the median is the safer habit: one product with a mistyped cost would drag the average and leave the median almost where it was.

G2, the Vale Velvet Loveseat at $1,659, gets an estimated cost of 748.53. G3 keeps its recorded 503.50, and G24, the $53.99 grill, gets 24.36.

Write down why

Charles has to defend these numbers. The decision column says, row by row, which rule produced the value:

H2   =IF(E2="",$J$4,$J$5)

The wording lives in J4 and J5, so it is the same on every row and can be changed in one place. When finance sends the real costs, sort or filter on column H and replace exactly the eight estimates.

Common mistakes

• Filling the original column. Keep unit_cost as exported and put the filled value next to it, or nobody can tell an estimate from a fact.
• 0 as a placeholder. It looks like data and flows into every total and average.
• A blank that isn’t blank. A cell with a space in it looks empty, but COUNTBLANK doesn’t count it. =COUNTIF(E2:E41," ") finds those.
• Estimating without a label. An estimate with no decision column becomes a fact the moment the sheet is forwarded.