Choose which formula errors to handle
Read actual error types, keep unexpected failures visible and preserve the denominator of a numeric summary.
| Source observation | Numeric count contribution | Error count contribution |
|---|---|---|
| 10 | 1 | 0 |
| 0 | 1 | 0 |
| true #DIV/0! | 0 | 1 |
| text "100" | 0 | 0 |
| TRUE in the source range | 0 | 0 |
Start with the cell type
The string "#N/A" is ordinary text. A genuine #N/A is an error value. ISNA, ISERR and ISERROR read that distinction; comparing a displayed spelling does not substitute for those type checks. The classification task returns a category and diagnostic code for every source row.
ERROR.TYPE identifies the classic error class. It does not locate the faulty reference or explain why a calculation failed. Use the formula and its source cells to investigate the cause.
Approve a fallback for a specific failure
IFNA replaces only genuine #N/A. In the recovery task, missing values receive the literal fallback from G2, while #DIV/0!, #VALUE! and other errors remain errors. The preserved error at the first output can be the correct answer.
IFERROR catches all errors. That is useful when the approved rule really applies to every error, but it can also conceal broken references or invalid inputs. Neither function repairs the original problem. Keep a blank normalization rule explicit.
=IF(ISBLANK(B2:B13),"",IFNA(B2:B13,$G$2))Keep the population behind an average
A referenced range may contain numbers, text, booleans, blanks and errors. The summary task counts only numeric observations; measured zero and signed amounts remain observations. It uses AGGREGATE with option 6 to ignore error values in the direct reference.
With observations 10, 0, -2.5 and 5.25, the mean is 12.75 divided by four, or 3.1875. Replacing ignored errors with zero before averaging adds observations and asks a different question. When the numeric count is zero, the task gives an explicit label instead of a zero mean.
Check values and types together
The exercises compare complete outputs and error types across eight retained sources plus edited current inputs. A literal fallback that looks like an error stays text; a preserved error stays an error. Use the source type editor to switch between them and observe the new assessment.
Formula and fixed-value workbook downloads retain these types. Native Excel and Google Sheets recalculation remain separate compatibility checks. Hidden rows, nested subtotal rules and connected-service errors are not part of these teaching sources.
Check the idea
The two numeric observations are 8 and 0; another row is a genuine #DIV/0!. What is their mean when errors are ignored?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.