New formula syntax

June 2, 2016

We’ve made a few changes to the way you write formulas in our upcoming release, mainly to improve compatibility with Microsoft Excel and other spreadsheet applications. With these changes, it is far more likely that you will be able to carry over formulas simply by copying them directly. These are the changes:

• Upper-case letters for function names. The current Calcapp Creator release expects you to write function names using lower-case letters. The upcoming release relaxes this requirement, enabling you to use any case you like, including upper case. We have adapted all our examples to use upper-case letters for function names, keeping with the tradition established by spreadsheets. Again, you can use any case you like, meaning that you can write the ISBLANK function as “isblank”, “isBlank”, “IsBlank”, “ISBLANK” or any other case combination.

• Formulas can start with =. An equals sign (“=”) can now start formulas, just like in spreadsheets, enabling you to carry over formulas directly. Calcapp ignores the leading equals sign, so you can leave it out if you like.

• Function changes. The “ceil” function is now known as CEILING. CEILING, FLOOR and ROUND now accept additional parameters to give you more control. The ROUND function, for instance, allows you to specify the number of decimal places that should be used. Also, a space between a function name and the opening parenthesis is now permissible. (Again, to make it more likely that you can copy formulas directly from your spreadsheets.)

• Logical and text values. So-called literals for logical values and text strings are now supported. Type TRUE or FALSE for the two logical values and enclose text strings in quotations marks. The (admittedly contrived) formula IF(FALSE, "won't be selected", "will be selected") is thus now legal.

• Operator changes. The Calcapp operators have been overhauled to improve compatibility with spreadsheets. (An operator is a symbol that typically appears between numbers to signify what to do with them. Addition — “+” — is a typical example.)

  • The percentage operator (“%”) is new and enables you to work with percentages easily. It simply divides a number by 100, making the formulas 50% * Weight and Discount% * Price legal. This means that the old modulus operator (which returns the remainder after division) has been removed. Use the MOD function instead (though its semantics are slightly different; refer to the documentation in the function browser for more information).

  • The logical operators “and”, “not” and “or” have been renamed to “&&”, “!” and “||” to make space for the AND, NOT and OR functions. Whether you use the operators or the functions is a matter of preference. We generally prefer the formula Field1 && Field2 && Field3 to the formula AND(Field1, Field2, Field3), but you’re free to use either construct.

  • The exclusive or (“xor”) operator has been removed to make space for the XOR function.

  • The equals operator (“==”) can now also be written as “=” and the inequality operator (“!=”) can be written as “<>”.

  • The concatenation operator (“&”) is new and allows you to join text strings together. (You can also use the CONCATENATE function.) The formulas Field1 & Field2 and Weight & " lbs" are now legal.

Your existing formulas may need to be updated

Some of these changes mean that your existing formulas will no longer work. In particular, if you use the “ceil” function, you need to replace it with the new CEILING function. Similarly, if you use the “defined” function, you need to replace it with the new ISDEFINED function. Also, if you use the logical operators “and”, “not” and “or”, you need to replace them with the “&&”, “!” and “||” symbols (alternatively, you can use the AND, NOT and OR functions). The “xor” operator must be replaced with the XOR function. Finally, if you use the “%” modulus operator, you need to replace it with the MOD function.

Also, Calcapp no longer ignores errors that result from calculating your formulas. That means that a calculation that previously did not produce a result now may produce, say, a “division by zero” error. If this happens, you need to use a formula that hides the error, if that is what is desired.