Dashpivot formulas can help streamline the way your teams fill out their forms, saving time and eliminating human error (ensuring that your data is accurate). Dashpivot formulas follow the same syntax as Microsoft Excel, and support most of Excel's built-in functions. So if you're familiar with MS Excel, you'll pick up formulas very quickly.
Note: A full list of supported formulas can be found at the bottom of this article. For current formula limitations, see Current formula limitations in Dashpivot.
Where can formulas be used?
Formula cells can be used in default tables or prefilled tables. In default tables, your formulas will reference columns, so each time you add a new row, the same formula applies to each cell in that column.
In prefilled tables, formulas reference cells (not columns) and can reference any cell in the table, not just the cells in the same column or row, which means calculations can occur across all rows & columns within the prefilled table.
đ Note: Storm In-Editor can help you write formulas for supported template types. See How to Edit Dashpivot Templates with Storm in the Template Editor for the full list.
Which field types work in formulas?
Not all field types in a Dashpivot table can be used as inputs in a formula. Using an unsupported field type won't cause an error â the formula will simply treat it as empty.
â Supported field types
These field types can be referenced in formula cells and will return a usable value:
Number â works in all calculations (add, subtract, multiply, divide, SUM, AVERAGE, etc.)
Formula â a formula cell can reference another formula cell
Date and Date (plain) â can be used in date difference calculations with
DATEDIF()Time and Time (plain) â can be used in time difference calculations with
TIMEDIF()List â the selected value can be referenced in
IF()statements (e.g. show a score based on a dropdown selection). Note: list values are treated as text, not numbers, so they cannot be used in math calculations directly.List Property â supported when the property type is text, number, or date. Not supported when the property is a file/attachment.
â Unsupported field types
These field types cannot be used as formula inputs â referencing them will return an empty result:
Text and Prefilled Text
Photo and Image
Attachment / File
Signature (manual and approval)
Location, Latitude, and Longitude
Map
Person
Basic Formulas
The simplest formulas in Dashpivot include:
Add
Subtract
Multiply
Divide
Percentage
SUM
Average
Counting table rows
Advanced Formulas
The more advanced formulas can allow you to:
Calculate time differences
Calculate overtime
Populate a number based on a dropdown list selection (and calculating an overall score)
Populate a PASS or FAIL based on a number entered (which could be above/below a specified value, or within a specified tolerance range)
One final note
The formulas in Dashpivot are highly flexible and can be configured/expanded to cover more use cases beyond the examples in this article. We have covered the most common formulas here, but feel free to build your own and experiment with what works best for your team.
Frequently Asked Questions
Why is my formula returning an empty result even though the cell has a value?
The cell you're referencing may be an unsupported field type such as a text, photo, or signature field. Formulas can only read values from number, date, time, formula, and list cells. Check that the source cell is one of the supported types listed above.
Can I use a dropdown (list) field value in a formula calculation?
Yes, but only in IF() statements â not in math calculations. List field values are treated as text, so you can use them to check a condition (e.g. =IF(A1="Yes", 1, 0)), but you cannot add or multiply them directly.
Can I reference a formula cell from another table?
Yes â formula cells support cross-table referencing. Use the format =Table#!CellReference (e.g. =Table2!A1). See How to Cross-Reference Tables in Formulas on Dashpivot Web for a full walkthrough.
Can I use a Person field or a Signature field in a formula?
No. Person, Signature, and Manual Signature fields are not supported as formula inputs and will return an empty result if referenced.
Can Storm write formulas for me?
Yes â Storm In-Editor can write and edit formulas in supported template types. Not all template types are covered. See How to Edit Dashpivot Templates with Storm in the Template Editor for the full list of supported types.