Excel averages at Singapore workshops: blanks versus zeros

The problem

Before a Singapore analytics workshop, your practice range contains a recorded zero and an empty observation, and filling the blank changes the average even though the sum stays the same.

Decide what the average is intended to describe

Keep a recorded zero distinct from an observation whose value is missing. Before filling a practice table, state whether you want the mean of its recorded measurements or a result for every intended observation. Those questions can need different information. A tidy column and a working formula do not establish what an unrecorded value means. Use one small Excel range to inspect that decision, then check beginner session fit and the population any wider report includes separately. Write one row’s meaning, the quantity and unit, the intended observations and the missing-value question before editing the input. Use invented material rather than an employer’s workbook for an open exercise.

Inspect actual contents in the referenced range

Microsoft’s Excel AVERAGE documentation says that a referenced range ignores empty cells and includes numeric zero. Its display caution also distinguishes empty cells from zeros hidden by worksheet settings. Inspect actual contents instead of classifying a cell from its appearance. The exercise here uses only plain numeric cells and one genuinely empty cell in a range reference. Text placeholders, numeric-looking text, logical values, errors, formula-generated empty strings and directly supplied arguments are outside this example. If your actual input contains one of those states, identify it and consult the documentation applicable to that state before extending the exercise. Preserve the original input and record the range being examined so an accidental change of cells does not become an explanation of missingness.

• Microsoft Support: Excel AVERAGE function, referenced-range behavior ↗ · Source checked 21 October 2026

Make an inclusion record before comparing results

Use seven columns: row identifier; intended observation; original cell state and value; interpretation actually established; contribution to the sum; contribution to the numeric count; and unresolved question. For this limited exercise, a numeric zero contributes zero to the sum and one to the count; the genuinely empty cell supplies no numeric observation. Keep an interpretation such as “recorded no activity” separate from a zero that merely appears after someone fills a gap. Beside the rows, list the number of intended positions, recorded numeric observations and missing observations. This seven-column inclusion record makes the calculation inspectable; it does not select a statistical treatment or establish that the available measurements represent the wider group you hoped to describe.

Compare two wholly invented outputs with the same sum

Imagine four planned observations in one invented quantity and unit. Cells A1 to A4 contain numeric 10, numeric 0, a genuinely empty cell and numeric 20. The original recorded values sum to 30 across 3 numeric observations, so their arithmetic mean is 30 divided by 3, or 10. One of the 4 intended observations remains missing. Now make a separately labelled hypothetical version that replaces the empty cell with numeric 0. Its sum is still 30, but its 4 numeric entries give 30 divided by 4, or 7.5. The lower number results from a changed denominator; the fourth value has been asserted as zero. Neither output establishes what the absent observation really was. The example is arithmetic under stated invented contents, not a tested workbook, a real dataset finding or evidence that either treatment suits your task.

Resolve the meaning through the actual source and question

Ask what the source records when an observation is absent and what definition the requested report uses. A documented absence meaning no activity could support a different decision from an observation never collected; the empty cell alone does not distinguish them. Keep the definition, its source and the responsible clarification beside the relevant row. If a transformation is justified for the actual task, make it separately with its reason while retaining the original state. If the meaning remains unknown, say so and label any result by the observations it actually uses. Do not promote a plausible replacement into a measured value or assume that an observed-value mean answers the question for all intended observations. This guide provides no imputation method or missingness diagnosis.

Choose a workshop that can examine the inclusion decision

Prepare the small invented range, the two labelled outputs and one unresolved question: “Which observations does this result describe, and what evidence would justify the proposed replacement?” Ask whether participants can inspect cell states and the calculation, whether the host uses Excel or another tool, and what setup is required. An advanced model demonstration may leave this beginner interpretation task untouched. A host accepting the invented exercise has not accepted your real records or decided their reporting policy. Confirm the actual material-sharing route. If the issue instead concerns acquiring a changing API response, record its retrieval and version separately from this fixed range comparison. Keep the question small enough that the session can examine the original row states rather than silently repairing a large private workbook.

Leave with a labelled result and the missing question visible

A completed fictional note can say: “Four intended positions; 3 recorded numeric observations, including a recorded zero; sum 30; observed-value mean 10; 1 missing observation with meaning unresolved. The zero-filled hypothetical gives 7.5 and is not adopted as a measurement.” Keep the range, row record and any later clarification together so the result can be reconstructed. If the session changes your understanding, add the evidence and the revised decision instead of overwriting the original. Use Boardzip’s free data-analysis search to find a suitable practical learning format, then confirm its software, prerequisites and opportunity to examine this question. When no suitable session is established, retain the invented exercise and open source-definition question; search access supplies neither an individual review nor a validated real-world conclusion.