V4 V5

A formula error is a value, not an exception. The cell displays the error, and it propagates into any formula referencing it.

The common errors and formulas that produce them

The list

DisplayedEnumUsual cause
#DIV/0!Div0Division by zero
#VALUE!ValueAn argument of the wrong type, or too few arguments
#REF!RefThe referenced cell was deleted, the reference is outside the sheet, or a name’s range was deleted
#NAME?NameAn unknown function, an undefined name, or a syntax error in the formula
#N/ANAA lookup found nothing
#NUM!NumThe result cannot be represented as a number
#NULL!Null#NULL! written directly in a formula (calculation never produces it)
#CIRCULAR!CircularA circular reference

#CIRCULAR! is not something Excel displays — Excel warns with a dialog. V5 has to work headless, so it returns it as a value. Saved to XLSX, the cached value is 0, as in Excel.

The enum is unvell.ReoGrid.Core.Formula.FormulaError.

Propagation

When any argument is an error, that error is generally what comes back.

A1 = 1/0        -> #DIV/0!
A2 = A1 + 10    -> #DIV/0!   (propagates)
A3 = SUM(A1:A2) -> #DIV/0!

The exceptions are the functions whose job is to receive an error — IFERROR, IFNA, ISERROR, ISERR and ISNA.

Handling them

ws.SetFormula(0, 0, "1/0");                  // #DIV/0!
ws.SetFormula(1, 0, "IFERROR(A1, 0)");       // replace an error with a default
ws.SetFormula(2, 0, "IFNA(VLOOKUP(\"x\", A1:B9, 2, FALSE), \"not found\")");

string shown = ws.GetDisplayText(0, 0);      // "#DIV/0!"
FunctionCatches
IFERROR(value, fallback)every error
IFNA(value, fallback)#N/A only

When you only mean to handle “VLOOKUP found nothing”, use IFNA. IFERROR also hides genuine mistakes such as an argument of the wrong type.

Testing from code

An error cell’s CellValue has Kind set to Error.

ws.GetValue(r, c).Kind == CellValueKind.Error

For the displayed text, GetDisplayText(r, c) returns a string like "#DIV/0!".

GetObjectValue(r, c) returns the error’s string form. Treating it as a number will fail, so check Kind in aggregation code.

ISERROR versus ISERR

FunctionTrue for #N/A?
ISERRORyes (every error)
ISERRno (everything except #N/A)

Same as Excel.

Was this article helpful?