Gnumeric #VALUE!: Distinguish Missing Logical Input from FALSE

Scope: Gnumeric desktop, English 1.12-series manual checked October 21, 2026. Distribution builds and translations may differ. These new exercises have not been executed in Gnumeric; results below are documentation-supported expectations and independently checked arithmetic or logic, not native test observations. Work in a disposable workbook, not your original.

An error is not a failed condition

A condition that evaluates to FALSE can be a perfectly valid result. A #VALUE! result means the calculation did not produce that ordinary answer. In Gnumeric’s AND and OR documentation, text and empty values are ignored; when no usable logical values remain, the functions return #VALUE!. Numeric zero is treated as FALSE and other numbers as TRUE.

This matters when a checklist contains words such as “ready” and “pending.” Those labels do not automatically express the Boolean condition you intended. Diagnose the input contract before converting an error to FALSE.

Reproduce the all-text case

In a new sheet put ready in A2 and pending in B2, as plain text. Enter =AND(A2:B2) in C2. Under the documented rule, both inputs are text and the expected result is #VALUE!.

Suppose the actual business rule is “both labels equal ready.” Replace C2 with =AND(A2="ready",B2="ready"). The two comparisons supply logical results, so the expected answer is FALSE. Change B2 to ready and verify TRUE. Change A2 to pending and verify FALSE again.

These three states test the rule directly. They do not prove that every spelling, blank, imported whitespace or localized label will be accepted. Decide which labels are allowed and reject ambiguous inputs before relying on the result.

Compare a different type of argument failure

Gnumeric’s Lookup documentation also specifies #VALUE! when ADDRESS receives a row or column below one. In a spare cell, =ADDRESS(0,2) is an intentionally invalid request. =ADDRESS(1,2) is the corrected control and should return the address text $B$1 with its default absolute-reference setting.

This case is not fixed by converting text to numbers: zero already is numeric. The argument is outside the permitted range. Read the failing function’s requirements rather than applying a universal “make everything numeric” repair.

Preserve unknown and failed states

For a real checklist, blank or unrecognized labels may mean “review required,” not FALSE. Keep that status separate from a valid rejection. Replacing every error with FALSE can silently approve a workflow that interprets FALSE as “no issue,” or silently reject a record without explaining missing evidence.

Likewise, do not replace a source error with a plain label before investigating it. Gnumeric documents AND and OR as strict with respect to error arguments: an upstream error can remain the reason the final check fails. Inspect the first error-producing input and the formulas feeding it.

Use AND, OR and ADDRESS for full argument references. For imported numbers rather than checklist labels, see CSV typing. Choose another symptom in the error selector.