Dashpivot formulas are powered by HyperFormula and cover most common use cases — but there are constraints to know before you build. This article covers unsupported functions, calculation behaviours that differ from Excel, and how to work around them.
For a full overview of how formulas work in Dashpivot, see Dashpivot Formulas Overview.
Quick reference
Limitation | Summary |
Nested parentheses | Max 6 levels |
VLOOKUP / MATCH | Exact match not supported |
Volatile functions (RAND, RANDBETWEEN) | Recalculate on every edit — cannot be frozen |
Division by zero | Silently accepted; no result at runtime |
Cross-table references | #REF! if table deleted, renamed, or cell is blank |
Unsupported field types | Attachment, photo, signature return null |
Unsupported functions | Engineering, statistical, financial, some math |
1. Nested parentheses limit
Dashpivot supports up to 6 levels of nested parentheses in a formula. Formulas with 7 or more levels will return an error.
Example of an unsupported formula (7 levels):
=IF(A1>0, IF(B1>0, IF(C1>0, IF(D1>0, IF(E1>0, IF(F1>0, IF(G1>0, 1, 0), 0), 0), 0), 0), 0), 0)
Workarounds:
Break the formula into separate calculations across multiple table cells.
Remove unnecessary parentheses where possible.
Use
IFS()instead of deeply nestedIF()logic, for example:
=IFS(Condition1, Value1, Condition2, Value2, Condition3, Value3)
2. VLOOKUP and MATCH exact match limitation
Dashpivot does not currently support exact match lookups with VLOOKUP() or MATCH() in templates using table cross-referencing. When building templates that rely on lookups, consider simplifying logic or restructuring data to avoid exact-match dependencies.
3. Unsupported functions
The following function categories are not available in Dashpivot formulas:
Category | Unsupported functions |
Engineering |
|
Statistical |
|
Financial |
|
Math |
|
Other |
|
4. Dashpivot-specific functions
The following custom functions are available in Dashpivot and are not part of the standard formula library:
DATEDIF(Date1, Date2)
Returns the absolute difference in whole days between two date values.
TIMEDIF(A1, B1)
Returns the difference in hours between two time values.
5. Field types that cannot be used in formulas
Not all field types can be used as formula inputs. Referencing an unsupported type returns null — no error is shown.
Field type | Can be used in formulas? |
Number | ✅ Yes |
Formula | ✅ Yes |
Date / Date (plain) | ✅ Yes |
Time / Time (plain) | ✅ Yes |
List | ✅ Yes (in |
List Property | ✅ Yes (text, number, or date properties only) |
Text / Prefilled Text | ❌ No |
Photo / Image | ❌ No |
Attachment / File | ❌ No |
Signature | ❌ No |
Location / Latitude / Longitude | ❌ No |
Map | ❌ No |
Person | ❌ No |
6. Cross-table reference rules
When referencing cells from another table in a formula:
The table being referenced must exist in the template. Referencing a deleted or renamed table will produce a
#REF!error. Update the formula to point to the correct table name.References to blank or empty cells in another table will also produce a
#REF!error.Cross-table formulas use the format
=TableName!ColumnLetter(e.g.=Table1!A1).
For more on cross-table referencing, see How to Cross-Reference Tables in Formulas on Dashpivot Web.
7. Division by zero
Dividing by zero (#DIV/0!) does not surface as an error in the template editor — it is silently accepted. However, the formula will not return a valid result at runtime if the divisor evaluates to zero.
Where possible, use an IF() guard to handle zero denominators:
=IF(B1=0, 0, A1/B1)
8. Volatile functions always recalculate (RAND, RANDBETWEEN)
RAND() and RANDBETWEEN() are volatile functions — they generate a new value every time any field in the same table is edited, or when a row is added, deleted, or reordered. This is how Dashpivot's formula engine (HyperFormula) works: any change to a non-formula cell triggers a full recalculation across the sheet, including all volatile functions.
What this means in practice:
A random number generated for a table row will change whenever a user edits any field in that table — even an unrelated column or a different row.
There is no way to lock or freeze a formula-generated value once it has been calculated. Moving a form to a different workflow stage does not freeze formula values.
Workarounds:
Approach | How it works | Trade-off |
Export to PDF | Captures all current values at export time | Values are fixed in the PDF only; the live form continues to recalculate |
Manual entry | Type the value directly into the cell instead of using a formula | Requires users to enter values manually; no calculation errors |
Deterministic substitute | Replace | Produces a stable result tied to that field — but requires careful design and does not produce true randomness |
Frequently Asked Questions (FAQs)
Why is my formula showing a #REF! error?
A #REF! error usually means your formula is referencing a table that has been deleted or renamed, or is pointing to a blank cell in another table. Check that the table name in your formula matches exactly what appears in the template editor, and that the referenced cell contains a value.
Why isn't my formula function working in Dashpivot?
Dashpivot does not support all HyperFormula functions. Engineering, statistical distribution, and most financial functions are not available. If a function isn't working, check the unsupported functions list above. For date and time calculations, use Dashpivot's custom DATEDIF() and TIMEDIF() functions instead of standard alternatives.
How many levels of nested IF statements can I use?
Dashpivot supports up to 6 levels of nested parentheses. If you need more complex logic, break the formula into smaller parts across multiple cells, or use IFS() to flatten nested IF() chains into a single formula.
Can I stop a formula from recalculating after it's been generated?
No. Dashpivot formulas recalculate automatically whenever any field in the table is edited, or when a row is added or removed. Volatile functions like RAND() and RANDBETWEEN() are particularly affected — they return a new value on every recalculation. There is no lock or freeze option for end users. To preserve a value, either export to PDF, enter the value manually, or replace the volatile function with a deterministic formula derived from a stable input field.
What Dashpivot-specific formula functions are available?
Dashpivot includes two custom functions not in standard Excel: DATEDIF(Date1, Date2) returns the absolute difference in whole days between two dates, and TIMEDIF(A1, B1) returns the difference in hours between two time values.