FEEL vs Excel Formulas

Last month I showed why Trisotech is a great Low-Code Business Automation platform, based on its use of FEEL and boxed expressions in executable BPMN. How ironic is it, then, that many decision management vendors don’t even include those features in their DMN tools!

The only part of DMN they use is the Decision Requirements Diagram (DRD), but what is the point of using a standard diagram to describe business requirements that are not even testable?
Answer: So they can claim their proprietary tools “follow the standard”.) When pressed, they say that FEEL and boxed expressions are for programmers, “too hard” for business users. In reality, they don’t want business users anywhere near the decision logic.

FEEL vs Excel

So let’s put that aside. A more useful discussion is FEEL vs Excel. Does anyone claim that Excel is for programmers, not for business users? Of course not. But I believe that FEEL and boxed expressions are actually more business-friendly than Excel formulas. Allow me to demonstrate.

First, however, I need to say that I love Excel, and I even use it as part of my Business Automation methodology. What makes Excel formulas truly great is that they are always live. A spreadsheet cell defined as a formula immediately displays the result whenever any cell it depends on changes. You don’t have to click a Submit button; the response is immediate and automatic. The other business-friendly feature of Excel formulas is that their arguments are selected by clicking in a cell, which references the value by its grid location, such as A1. Yes, it’s possible to assign names to cell ranges, but come on, do you do that? No one does that.

I contend that these features make Excel great for creating examples of the business logic, but not so good for generalizing the logic as you would do in an app. Here is an illustration.

In my Business Automation training, the class builds a Stock Trading App. In the methodology we start by whiteboarding the business logic using examples in Excel. With each new Trade event, two rows are added to the PositionBalance table, one for the traded ticker and one for Cash. Without going into the details, the diagram below is an example of that.

Let’s look at the CostBasis column after sale of a stock, such as cell I15. In this table, the value of CostBasis is a simple Excel formula. Whenever a cell definition starts with =, it’s a formula. And this formula references another column of the same row, and some columns from two rows earlier. A subject matter expert can easily create logic in this way based on examples. But why does it reference values from two rows earlier? The answer in this case is that it is the most recent row with the same value of column D, the Ticker. It won’t always be two rows earlier. To generalize the logic, you need a lookup formula.

No problem. Excel has many built-in functions in its Formulas ribbon. The modern lookup formula is called XLOOKUP, with the explanation below:

Using this, for cell I15, we can generalize the formula H15*I13/H13, where row 13 is the last row preceding row 15 in which the column D value is the same, as:

It’s always live, but is it business-friendly?

Hardly. And that’s not the half of it, because the simple Excel formula in cell I15 only applies to tickers other than Cash when TradeShares is negative. Other formulas apply for Cash and positive TradeShares! So to create the general case of the PositionBalance table in Excel would be extremely difficult.

Let’s compare FEEL and boxed expressions. The complexity of XLOOKUP was related simply to finding the last PositionBalance record for the ticker in question. In Low-Code BPMN, following a query of the PositionBalance table of records matching the ticker name, the variable Last position is a simple filter expression, looking for maximum ID value. It’s much simpler than XLOOKUP.

And remember, we need a different CostBasis formula depending on Cash or non-Cash, and Buy or Sell trade. In Excel formulas, you would need to wrap all that XLOOKUP business inside IF functions:

IF([cond], [complex XLOOKUP formula], IF([cond2], [complex XLOOKUP formula2], [else formula]))

What a mess! FEEL and boxed expressions handle that easily as well:

Here we have a decision table inside a context, where each context entry models a column of PositionBalance. The only complexity here is accounting for the possibility that there is no previous PositionBalance record for the ticker, and even that is a simple if..then..else.

And this illustrates a major advantage of FEEL over Excel formulas. Except for arithmetic operators (+, *, etc.) and comparison operators (=, >, etc.), Excel formulas rely entirely on nested functions. They don’t even have logical operators like and. FEEL allows nested functions as well, but it relies on a long list of operators that greatly simplify the syntax: if..then..else, for..in..return, filters, logical operators, and so forth. There is simply no comparison; FEEL is much more business-friendly than Excel formulas!

Here is another example, published by Microsoft to show off the Excel formula language, which is being rebranded Power FX, the Low-Code language behind Microsoft PowerApps. It’s actually very cool, but what’s cool is the always live part, not the formula language behind it.

The formula in cell A2 displays the text following the last space in cell A1, and it does this live as you type in cell A1. Take a look at the Power Apps version:

Doing this in the Excel formula language is impressive, but I could not decode the deeply nested function logic without instructions from StackOverflow:

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) Count of spaces in the original string
SUBSTITUTE(A1," ","|", ... ) Replaces just thefinalspace with a|
FIND("|", ... ) Finds the absolute position of that replaced|(that was the final space)
Right(A1,LEN(A1) - ... )) Returns all characters after that|

Now Trisotech currently does not have the always live feature – I hope that it will someday! – but FEEL in a boxed context certainly can implement the Excel formula logic in a much more business-friendly way.

You don’t really even need the context, as the single literal expression is simple enough:

split(Input string, " ")[-1]

Now you tell me, which is more business-friendly, FEEL or Excel formula language, Power FX?