DIVIDE
Category: Math & numeric
Overview
The DIVIDE function extends the / operator with optional validation for missing rows. It produces the same result as 'Node1' / 'Node2' when validation passes. When validation fails, it produces an error with a detailed explanation of which rows are missing.
Use this function when you want the result of regular division but need explicit mismatch validation and clearer error messages.
Syntax
DIVIDE('Node1', 'Node2' [, "Validation"])
Example usage: DIVIDE('Profit', 'Revenue', "FailOnMissing")
Parameters
| Parameter | Description | Type | Required | Default |
|---|---|---|---|---|
| Node1 | Dividend node, specified using the node name in single quotes (e.g.'Profit') | Node reference | Yes | — |
| Node2 | Divisor node, specified using the node name in single quotes (e.g.'Revenue') | Node reference | Yes | — |
| Validation | Controls how mismatches between the two input nodes are handled. Case-insensitive. | Keyword | No | NoValidation |
Validation options:
"NoValidation": No mismatch check. Behaves exactly as the/operator. This is the default."FailOnMissingFirst": Fails if any row in Node1 has no matching row in Node2."FailOnMissingSecond": Fails if any row in Node2 has no matching row in Node1."FailOnMissing": Fails if either node has rows not matched by the other (combines both checks).
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Union of all dimensions from both inputs (same as /). Per shared dimension, the finest level is used. |
| Level values | Matched on shared dimensions. Non-shared dimensions are applied across all matching rows. |
| Row count | Equal or reduced. Where the dividend is missing, the result is0. Where the divisor is missing or zero, the cell is dropped (N/A). |
Watch Out
- Using validation has a performance impact. Only use it when you need to detect mismatches.
- Without validation (or with
NoValidation), DIVIDE is identical to/. - Missing values are handled asymmetrically: a missing dividend produces 0, but a missing or zero divisor produces N/A (the cell is dropped). This means the result can have fewer rows than either input.
- Division by zero returns N/A (the cell is silently dropped). If you need explicit handling, wrap in an IF condition:
IF('Divisor' != 0,DIVIDE(‘Dividend’, ‘Divisor’), 0).
Examples
Validation behavior
This example shows the standard division result and how the different validation modes behave when rows are missing on one side. The underlying numeric result matches regular division as long as the selected validation passes.
Input node: Node1
| Year | Value |
|---|---|
| 2026 | 6 |
| 2027 | 4 |
| 2028 | 10 |
Input node: Node2
| Year | Value |
|---|---|
| 2025 | 2 |
| 2026 | 3 |
| 2027 | 1 |
Formula: 'Node1' / 'Node2' = DIVIDE('Node1', 'Node2') = DIVIDE('Node1', 'Node2', "NoValidation")
| Year | → DIVIDE Result |
|---|---|
| 2025 | N/A / 2 = 0 |
| 2026 | 6 / 3 = 2 |
| 2027 | 4 / 1 = 4 |
Year 2028 is not in the result because the divisor is missing (N/A).
Formula: DIVIDE('Node1', 'Node2', "FailOnMissing")
Error: Year 2028 from Node1 has no matching value in Node2 and year 2025 from Node2 has no matching value in Node1.
Formula: DIVIDE('Node1', 'Node2', "FailOnMissingFirst")
Error: Year 2028 from Node1 has no matching value in Node2.
Formula: DIVIDE('Node1', 'Node2', "FailOnMissingSecond")
Error: Year 2025 from Node2 has no matching value in Node1.
Related Functions
| Function | When to use instead |
|---|---|
| DIVISION (/) | When you want standard division without additional validation handling or detailed mismatch errors. |
| RATIO | When you want to divide two nodes while retaining only the dimensions shared by both inputs instead of using DIVIDE’s regular division-with-validation behavior. |