Replacing errors with numeric zero
An average of IFERROR(range,0) treats error rows as observations and can change the denominator.
Summarize measurements that contain errors
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return one row with four results in this order: the sum of numeric observations in B2:B13, their count, their arithmetic mean, and the count of genuine source errors. Ignore genuine errors when calculating the numeric statistics. Range text, numeric-looking text, Boolean values and blanks are not numeric observations. Keep real zeroes and negative amounts. If no numeric values exist, return numeric zero for sum and count and the literal No numeric values for mean. Do not add headings.
Edit a source value and its type
Text that looks like an error and a genuine error have different types. Apply an explicit type to the current worksheet, then check the complete report again.
The draft matches the current worksheet value and type.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:J5. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Report a signed total, numeric count, numeric mean and genuine error count without converting other value types to zero.
THE QUICK ANSWER01
Report a signed total, numeric count, numeric mean and genuine error count without converting other value types to zero.
ƒx
=HSTACK(AGGREGATE(9,6,B2:B13),COUNT(B2:B13),IF(COUNT(B2:B13)=0,"No numeric values",AGGREGATE(1,6,B2:B13)),SUMPRODUCT(--ISERROR(B2:B13)))| G | H | I | J |
|---|---|---|---|
| 12.75 | 4 | 3.1875 | 2 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Keep the denominator visible
The demonstration includes 10, 0, -2.5 and 5.25. They sum to 12.75, the numeric count is four, and their mean is 3.1875. The text "100" and TRUE are not range measurements.
Missing is different from measured zero
When every source is an error or nonnumeric value, the sum is zero but the mean is unavailable. When the numeric inputs are genuine zeroes, the mean is a valid numeric zero and the count remains positive.
Use the declared reference form
Amounts accept at most two decimals within ±1,000,000. AGGREGATE uses the direct vertical reference and option 6; the exercise does not certify hidden-row or nested SUBTOTAL behavior. Replacing each error with zero before averaging would enlarge the denominator and change the question.
Read the full step-by-step walkthrough
- Select a numeric population
COUNT reads the referenced range and counts numeric observations. Zero is included; text, booleans, blanks and errors are not numeric range observations.
- Sum while ignoring errors
AGGREGATE function 9 selects SUM; option 6 ignores error values. The reference contains no hidden rows or nested subtotal formulas in these tasks.
- State the empty-population outcome
A numeric count of zero selects the explicit No numeric values label. It does not invent a zero mean.
- Average the actual measurements
AGGREGATE function 1 selects AVERAGE, with the same ignore-error option and numeric range population.
- Count the errors separately
ISERROR tests actual error values. SUMPRODUCT adds the resulting Boolean flags after numeric conversion. Error-looking text contributes zero.
- Return the ordered summary
HSTACK returns sum, count, mean and error count as one complete row.
Things to watch
Coercing range values
Double-unary conversion of every source can turn TRUE or text "100" into numbers and produce a different population.
Using SUM without ignoring errors
A genuine error in the range prevents an ordinary SUM from returning the requested error-ignoring total.
About this example
Original typed teaching inputs, independent source/type rules and complete error/spill checks. Native Excel and Sheets recalculation/edit/save remain separate verification steps.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.