Overview
You can evaluate Excel formulas in the Assert Data Rules task, but not all Excel functions are supported.
WTAB supports modern Excel formats (.xlsx, Excel 2007+),
but the formula engine implements only a subset of Excel functions.
Background
The application relies on a third-party library to read and evaluate Excel formulas.
Although this library supports modern Excel files, it does not implement the full Excel function set.
As a result, a formula may work in Microsoft Excel but fail to parse or evaluate in the application.
Typical Error
When WTAB encounters an unsupported function, the error can be:
This formula works:
IF(IFERROR(VLOOKUP(EDATE([MONTH(Order Date)], -1), A$1:B$10000, 2, FALSE), TRUE)=TRUE, TRUE,
ABS(([SUM(Sales)]/VLOOKUP(EDATE([MONTH(Order Date)], -1), A$1:B$10000, 2, FALSE))-1)<=1)
Refactored version using LET() (fails):
LET(priorDate,EDATE([@Order Date],-1),target,IFERROR(VLOOKUP(priorDate,A$1:B$10000,2,FALSE),NA()),
IF(ISNA(target),TRUE,IF(target=0,TRUE,ABS([@Sales]/target-1)<=1)))
Reason: the LET() function is not
implemented in the third-party formula engine.
Supported Excel Functions
The list of supported functions comes from the engine documentation (not all functions have been individually tested).