Skip to main content
All CollectionsSupport GuideDashpivot - WebTemplate Functions
How to use Dashpivot Formulas in your templates
How to use Dashpivot Formulas in your templates

Learn how to use the Formula cell in table fields for simple and complex calculations and automation

Sam avatar
Written by Sam
Updated over a week ago

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 supports most of Excel's inbuilt functions. So if you're familiar with MS Excel then you'll pick up formulas very quickly.

Note: A full list of supported formulas can be found at the bottom of this article

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.

Formulas can reference number cells, time cells, date cells, formula cells, and list cells (but only for IF statements, since the values are text not numbers).

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 above/below a specified value, or within a specified tolerance range)

Date and Time Calculations

To calculate the number of days between 2 date cells (A1 and B1), use the DATEDIF function:
​

=DATEDIF(A1,B1)

To calculate the number of hours between 2 time cells (A1 and B1), use the TIMEDIF function:

=TIMEDIF(A1,B1)

Both the DATEDIF & TIMEDIF functions will always return a positive number.

For formulas that cross-reference multiple tables:

  • Dates are stored numerically and need to be surrounded by TEXT() to show a date format e.g. 

=TEXT(Table1!A1,"DD/MM/YYYY")
=TEXT(Table1!A2,"MM/DD/YYYY")

  • Time is also stored numerically and will need to be surrounded by TEXT() to show a time format e.g.

=TEXT(Table1!B1-Table1!A1,"H:MM")

Overtime Calculations

To calculate the ordinary hours only (if more than 8 hours, just display 8). This formulas includes a break (C1).

=IF(TIMEDIF(A1,B1)-C1>8,8,TIMEDIF(A1,B1)-C1)

Then to calculate the overtime hours only (ie. anything above 8 hours):

=IF(TIMEDIF(A1,B1)-C1>8,TIMEDIF(A1,B1)-C1-8,0)

Populating a number based on dropdown selection

Where A1 is a list cell, and "YES" is a list item name in the dropdown. This will populate 1 if Yes is selected, otherwise a 0 will be populated.

=IF(A1="YES",1,0)

Please note that the dropdown options TRUE and FALSE are reserved words used for our supported Boolean operations. To correctly use TRUE and FALSE options in the list dropdown, you will need to cast them as a string using the formula: TEXT(CELL_REFERENCE, "0")
​

Populating text based on number entered eg. PASS/FAIL

Where text is based on the number entered being below a value, where A1 is a number cell.

eg. if the number entered is less than 50, display "FAIL" (otherwise display "PASS")

=IF(A1<50,"FAIL","PASS")

Where text is based on the number entered being above a value, where A1 is a number cell.

eg. if the number entered is above 200, display the text "PASS" (otherwise display "FAIL")

=IF(A1>20,"PASS","FAIL")

Where text is based on the number being between two values , where A1 is a number cell.

eg. if the number is less than -0.05 or greater than 0.05 then display "FAIL" (otherwise display "PASS")

=IF((or(A1<(-0.05),A1>0.05),"FAIL","PASS")

Combining cells together

Where the contents of A1 and B1 are the cells you want to combine.

=A1&B1

If you need a space between the values (for example if you are combining first and last names), be sure to add a space in quotations, like so:

=A1&" "&B1

This way a name will read as 'John Smith', rather than 'JohnSmith'

Calculating a daily cost

To calculate a daily cost, you might want to SUM values from multiple tables eg. internal labour, external labour, plant, and materials.

In this example, the formula references three tables (Table1, Table2 and Table3), and sums all the values in the A column from each table to arrive at a total.

=SUM(Table1!A:A)+SUM(Table2!A:A)+SUM(Table3!A:A)

Table cross-referencing

Here are a few other things to take note of while using a formulas that cross-reference other tables:

  • Dashpivot Mobile app users will need to use versions v23.2 and later to complete forms using the table cross-referencing feature.

  • Sitemate Mobile app users will need to use versions v23.1 and later to complete forms using the table cross-referencing feature.

Helpful tips

When cross-referencing other tables:

  • To reference all the values in an entire column, use A:A, B:B, ect. notation from the table in the formula.

  • Default tables can automatically increment the cell ID =A1+1, if this is not the desired behaviour, use $A1 within your formula.

  • You can store constants using formula cell =1 or =”TEXT”, as a pre-filled text/number cell.

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.

Current Limitations

Templates using table cross-referencing will not be able to nest more than one level of parentheses. This means formulas nested with more than one level of parentheses are not supported at this time. For example:

=FUNCTION((FUNCTION2), (FUNCTION3(FUNCTION4)))

The current workaround for this is:

  • Using different table cells to break down the formula

  • Remove any unnecessary sets of parenthesis

  • Use the 'IFS' function - e.g.

=IFS(Something is True1, Value if True1, Something is True2, Value if True2, Something is True3, Value if True3)

Templates using table cross-referencing will also not be able to find exact matches with VLOOKUP and MATCH functions.

- - -

Full list of supported Formulas:

ABS
​ACCRINT
​ACOS
​ACOSH
​ACOT
​ACOTH
​ADD
​AGGREGATE
​AND
​ARABIC
​ARGS2ARRAY
​ASIN
​ASINH
​ATAN
​ATAN2
​ATANH
​AVEDEV
​AVERAGE
​AVERAGEA
​AVERAGEIF
​AVERAGEIFS
​BASE
​BESSELI
​BESSELJ
​BESSELK
​BESSELY
​BETA.DIST
​BETA.INV
​BETADIST
​BETAINV
​BIN2DEC
​BIN2HEX
​BIN2OCT
​BINOM.DIST
​BINOM.DIST.RANGE
​BINOM.INV
​BINOMDIST
​BITAND
​BITLSHIFT
​BITOR
​BITRSHIFT
​BITXOR
​CEILING
​CEILINGMATH
​CEILINGPRECISE
​CHAR
​CHISQ.DIST
​CHISQ.DIST.RT
​CHISQ.INV
​CHISQ.INV.RT
​CHOOSE
​CHOOSE
​CLEAN
​CODE
​COLUMN
​COLUMNS
​COMBIN
​COMBINA
​COMPLEX
​CONCATENATE
​CONFIDENCE
​CONFIDENCE.NORM
​CONFIDENCE.T
​CONVERT
​CORREL
​COS
​COSH
​COT
​COTH

COUNT
​COUNTA
​COUNTBLANK
​COUNTIF
​COUNTIFS

COUNTUNIQUE
​COVARIANCE.P
​COVARIANCE.S
​CSC
​CSCH
​CUMIPMT
​CUMPRINC
​DATE
​DATEVALUE
​DAY
​DAYS
​DAYS360
​DB
​DDB
​DEC2BIN
​DEC2HEX
​DEC2OCT
​DECIMAL
​DEGREES
​DELTA
​DEVSQ
​DIVIDE
​DOLLARDE
​DOLLARFR
​E
​EDATE
​EFFECT
​EOMONTH
​EQ
​ERF
​ERFC
​EVEN
​EXACT
​EXP
​EXPON.DIST
​EXPONDIST
​F.DIST
​F.DIST.RT
​F.INV
​F.INV.RT
​FACT
​FACTDOUBLE
​FALSE
​FDIST
​FDISTRT
​FIND
​FINV
​FINVRT
​FISHER
​FISHERINV
​FLATTEN
​FLOOR
​FORECAST
​FREQUENCY
​FV
​FVSCHEDULE
​GAMMA
​GAMMA.DIST
​GAMMA.INV
​GAMMADIST
​GAMMAINV
​GAMMALN
​GAMMALN.PRECISE
​GAUSS
​GCD
​GEOMEAN
​GESTEP
​GROWTH
​GTE
​HARMEAN
​HEX2BIN
​HEX2DEC
​HEX2OCT
​HOUR
​HTML2TEXT
​HYPGEOM.DIST
​HYPGEOMDIST
​IF
​IMABS
​IMAGINARY
​IMARGUMENT
​IMCONJUGATE
​IMCOS
​IMCOSH
​IMCOT
​IMCSC
​IMCSCH
​IMDIV
​IMEXP
​IMLN
​IMLOG10
​IMLOG2
​IMPOWER
​IMPRODUCT
​IMREAL
​IMSEC
​IMSECH
​IMSIN
​IMSINH
​IMSQRT
​IMSUB
​IMSUM
​IMTAN
​INT
​INTERCEPT
​INTERVAL
​IPMT
​IRR
​ISBINARY
​ISBLANK
​ISEVEN
​ISLOGICAL
​ISNONTEXT
​ISNUMBER
​ISODD
​ISODD
​ISOWEEKNUM
​ISPMT
​ISTEXT
​JOIN
​KURT
​LARGE
​LCM
​LEFT
​LEN
​LINEST
​LN
​LOG
​LOG10
​LOGEST
​LOGNORM.DIST
​LOGNORM.INV
​LOGNORMDIST
​LOGNORMINV
​LOWER
​LT
​LTE
​MATCH
​MAX
​MAXA
​MEDIAN
​MID
​MIN
​MINA
​MINUS
​MINUTE
​MIRR
​MOD
​MODE.MULT
​MODE.SNGL
​MODEMULT
​MODESNGL
​MONTH
​MROUND
​MULTINOMIAL
​MULTIPLY
​NE
​NEGBINOM.DIST
​NEGBINOMDIST
​NETWORKDAYS
​NOMINAL
​NORM.DIST
​NORM.INV
​NORM.S.DIST
​NORM.S.INV
​NORMDIST
​NORMINV
​NORMSDIST
​NORMSINV
​NOT
​NOW
​NPER
​NPV
​NUMBERS
​OCT2BIN
​OCT2DEC
​OCT2HEX
​ODD
​OR
​PDURATION
​PEARSON
​PERCENTILEEXC
​PERCENTILEINC
​PERCENTRANKEXC
​PERCENTRANKINC
​PERMUT
​PERMUTATIONA
​PHI
​PI
​PMT
​POISSON.DIST
​POISSONDIST
​POW
​POWER
​PPMT
​PROB
​PRODUCT
​PROPER
​PV
​QUARTILE.EXC
​QUARTILE.INC
​QUARTILEEXC
​QUARTILEINC
​QUOTIENT
​RADIANS
​RAND
​RANDBETWEEN
​RANK.AVG
​RANK.EQ
​RANKAVG
​RANKEQ
​RATE
​REFERENCE
​REGEXEXTRACT
​REGEXMATCH
​REGEXREPLACE
​REPLACE
​REPT
​RIGHT
​ROMAN
​ROUND
​ROUNDDOWN
​ROUNDUP
​RRI
​RSQ
​SEARCH
​SEC
​SECH
​SECOND
​SERIESSUM
​SIGN
​SIN
​SINH
​SKEW
​SKEW.P
​SKEWP
​SLN
​SLOPE
​SMALL
​SPLIT
​SPLIT
​SQRT
​SQRTPI
​STANDARDIZE
​STDEV.P
​STDEV.S
​STDEVA
​STDEVP
​STDEVPA
​STDEVS
​STEYX
​SUBSTITUTE
​SUBTOTAL
​SUM
​SUMIF
​SUMIFS
​SUMPRODUCT
​SUMSQ
​SUMX2MY2
​SUMX2PY2
​SUMXMY2
​SWITCH
​SYD
​T
​T.DIST
​T.DIST.2T
​T.DIST.RT
​T.INV
​T.INV.2T
​TAN
​TANH
​TBILLEQ
​TBILLPRICE
​TBILLYIELD
​TDIST
​TDIST2T
​TDISTRT
​TIME
​TIMEVALUE
​TINV
​TINV2T

TODAY
​TRANSPOSE
​TREND
​TRIM
​TRIMMEAN
​TRUE
​TRUNC
​UNICHAR
​UNICODE
​UNIQUE
​UPPER
​VAR.P
​VAR.S
​VARA
​VARP
​VARPA
​VARS
​WEEKDAY
​WEEKNUM
​WEIBULL.DIST
​WEIBULLDIST
​WORKDAY
​XIRR
​XNPV
​XOR
​YEAR
​YEARFRAC

The following functions are not supported:
​COUNTIN
​

Did this answer your question?