Advanced Expressions

IFERROR & IFNA Functions

View as Markdown

Summary

  • AdapTableQL can catch evaluation errors in Expressions, Excel-style:
    • IFERROR — return a fallback when evaluation throws any error
    • IFNA — return a fallback only when the value is N/A
    • NA — produce an N/A error for IFNA / IFERROR to catch

Most Expressions return a value or a boolean. Sometimes evaluation fails — for example dividing by zero, or a lookup that has no result.

AdapTableQL provides 3 Functions for that case, matching Excel:

FunctionWhat it does
IFERROR(value, fallback)Evaluates value; if that throws an error, returns fallback
IFNA(value, fallback)Evaluates value; if that is N/A, returns fallback. Other errors are not caught
NA()Throws an N/A error (Excel #N/A)
 IFERROR([github_stars] / [open_pr_count], 0)
 IFNA(QUERY("Lookup"), "missing")
 NA()

Note

  • IFERROR and IFNA only evaluate the fallback when it is needed
  • An uncaught error in a Calculated Column cell is shown as #INVALID

IFERROR

Use IFERROR when any evaluation error should become a safe value — typically in a Calculated Column.

A common case is division by zero (DIV throws):

 IFERROR([github_stars] / [open_pr_count], 0)

IFERROR also catches N/A, so it is the wider of the two wrappers.

IFNA and NA

Use NA() when a result is not available (a failed lookup, or a CASE branch with no mapping). Use IFNA when only that sentinel should be replaced:

 IFNA(CASE [language] WHEN 'JavaScript' THEN '.js' WHEN 'TypeScript' THEN '.ts' ELSE NA() END, 'other')

Other evaluation errors (division by zero, a bad DATE, a missing Column) still throw — wrap those with IFERROR instead.

Caution

NULL is a blank value, not an error. IFNA and IFERROR do not treat blank cells as N/A. Use COALESCE or IS_BLANK for empty values.

AdapTableQL: IFERROR and IFNA
Fork
  • This demo contains 2 Calculated Columns that wrap Expressions which can fail:
    • Stars per Open PR uses IFERROR so a row with 0 open PRs returns 0 instead of #INVALID
    • File Type uses IFNA and NA() so an unmapped Language returns "other"
Try It Out
  • Find riot — it has 0 open PRs; Stars per Open PR is 0 (without IFERROR that cell would be #INVALID)
  • Find alpine or polymer — Language is HTML, so File Type is other
Loading demo…

Find Out More

PreviousIF & CASE Logic FunctionsNextFIELD Function