Evaluate values from a pure text formulas

[Youchen_Lee]

Hi,

When there is a field with a pure text value “1 + 1”, I would like to dynamically calculate it and put the result in to the other field. For example:

| 1 + 1 | 2 |
| 2 * 3 | 6 |

Thank you.

[W_Vann_Hall]

I guess the big question would be, “how complicated are these formulas?”

If they can all be represented by a single (or one of a few) schema, then it shouldn’t be too hard. On the other hand, if these are an arbitrary algebraic equation with variable groupings, or if they can contain multiple operands implicitly grouped by operator precedence, the task becomes much, much harder.

If the text formulas really are as simple as the examples you gave, here’s how I would define the base:

• Field 1: “Formula” (1-line text) This is the text field that contains the formula, as shown above.

• Field 2: “CleanForm” (formula) Replaces ‘x’ and ‘X’ in formula with ‘*’. [optional]
  SUBSTITUTE(SUBSTITUTE(Formula,"x","*"),"X","*")

• Field 3: “Operator” (formula) Extracts the operator character from the ‘CleanForm’ string.
  IF(FIND("+",CleanForm)>0,"+",
IF(FIND("-",CleanForm)>0,"-",
IF(FIND("/",CleanForm)>0,"/",
IF(FIND("*",CleanForm)>0,"*","?"))))

• Field 4: ‘Op1’ (formula) Extracts first operand; makes numeric.
  VALUE(TRIM(LEFT(CleanForm,FIND(Operator,CleanForm)-1)))

• Field 5: ‘Op2’ (formula) Extracts second operand; makes numeric.
  VALUE(TRIM(RIGHT(CleanForm,LEN(CleanForm)-FIND(Operator,CleanForm))))

• Field 6: ‘Result’ (formula) Calculates result of formula.
  IF(Operator="+",Op1 + Op2,
IF(Operator="-",Op1 - Op2,
IF(Operator="/",Op1 / Op2,
IF(Operator="*",Op1 * Op2,
BLANK()))))

This is extremely rudimentary – for one thing, in its current form, it chokes on negative numbers – so it should probably be seen as the beginnings of such code, eagerly awaiting expansion and enhancement into something useful.

(A few notes: Fields 2 through 5 would ordinarily be hidden in a production system. I’ve chosen to include the call to VALUE() in the formulas that extract Op1 and Op2, which makes the resulting fields subject to Airtable’s cell formatting. [For instance, in the demo base that follows, you’ll notice in row 3 the extracted Op1 and Op2 display as rounded integers, even though their true values are preserved and used in the resulting formula.] In this instance, Value() is required to keep calls to “*” and “?” from reporting an error, as they would if either operand was a string. Finally, BLANK() in the final formula ensures ‘Result’ is a numeric value; replacing it with ERROR() or "" would cause it to be cast as a string.)

A link to a demo base containing these formulas can be found here.

[Youchen_Lee]

@W_Vann_Hall Very appreciate your help. That was a cool implementation.
Unfortunately, my formulas are a lot complicated. I might need to have a parser just like in Field 3, 4, 5, 6 but do in a recursive way.

So, eventually, I fallback it to Microsoft Excel to do that with the “evaluate()” with a macro. :sleepy:

[W_Vann_Hall]

Youchen_Lee:

Unfortunately, my formulas are a lot complicated.

Yeah, I figured if you were anything other than an elementary school teacher working on courseware, that example was going to be far too simplistic—

—probably. Years ago, I wrote a book chapter on Natural Language Programming (using a computer language that had no business being used that way), and in it I pointed out that for many applications, AAI – Artificial Artificial Intelligence – would suffice. That is, if one was building a ‘natural language’ interface to, say, a DBMS, it wasn’t necessary for the application even to be as fluent a conversationalist as Eliza, let alone to pass a Turing test. Instead, virtually every ‘natural language’ statement made to the app would be a command or query of some sort, invoking a small number of actions, typically falling into a [command] [subject] [modifier] [object] [modifier] structure that made it child’s play to extract field names and values. Your main driver isn’t the complexity of the formulas you need to evaluate so much as it is the scope and range of complex formulas. If you have a manageable number of formulas to support – or if they can be refactored into a manageable number – and you’re gaining other benefits from Airtable, it might turn out to be worth the effort to hand-build support in your base.

On the other hand, if you have no idea what you will need to solve on any given day, you probably need either a different solution or some way to offload that portion of processing. Towards that end, I spent a little time scanning through the apps and integrations supported by IFTTT and Zapier, expecting to find a ready-made gateway to something like the math.js web service, MathJax, or similar – to no avail. Yes, your Airtable base can talk near-seamlessly with the net-enabled device that warns you when the number of eggs in your fridge drops below a certain threshold, but if you want to calculate how much liquid egg that represents, expressed in cubic centimeters, you’ll have to build the interface and routines yourself. :winking_face: