Returns the first non-Null value from the arguments provided.

Usage

Coalesce(argument 1, ...)

argument 1 (required) Arguments can be numbers, equations, column references or functions. If all values are Null, Null is returned.

argument 2+ (optional) Coalesce can evaluate several arguments.

NOTE: Arguments must all be of the the same  type. All inputed arguments must be numbers, dates, or string, without mixing types. If you need to evaluate different types of arguments, you can use text() to force the Coalesce function to read the input as a string.

Example

Coalesce(Null, 1/0, 1/1, 1/2) = 1
  • The third value is the first non-Null value
Coalesce(1/0, 0) = 0
  • The second value is the first non-Null value
Coalesce([Sales], 0)
  • Converts all the null values in [Sales] to 0. 
Coalesce([profit]/[sales], 0)
  • Returns profit per sale for when sales >0; returns 0 if there are no sales. This construction avoids Nulls when the formula could face situations that divide by 0.

Was this page helpful?
Yes No