Mathematical Options for Formulas

The formula engine in the App can handle some pretty complex computations, but it is limited. This document describes the format that a formula can use and all of the options for functions that are available to you.

In general, a formula can use any standard mathematical operation (add, subtract, multiple, divide, power) and constants (numbers expressed as doubles).  For example:

  • “+” – Adds two numbers
  • “-” – Subtracts the second number from the first (5-2=3)
  • “*” – Multiplies two numbers
  • “/” – Divides the first number by the second (8/2=4)
  • “^” – Raises the first number to the power specified by the second number (3^2 = 9)

You can also create complex operations using parenthesis to control the order of operations For example:

is different from:

Of course, none of this is very useful if you can’t use inputs or variables!  You can use existing input data, or other formulas by referencing their name.  For example, if you had an attribute called “MAOP” in your events table, you would reference it in a formula by surrounding it in square brackets:

Variable names like the one above can be any combination of characters and spaces, the following are all valid: [maop] , [max pressure] , [wall thickness] , [ rem. wall thickness]

Variables must be surrounded by square brackets as shown.

These can be used in place of constants, and mixed with constants:

You also have access to the following functions when building formulas:

  • ln – ln(<input>) – Natural Log – Returns the natural log of the argument, accepts only one argument ( ln(2) = 0.693 )
  • exp – exp(<input>) – the number “e” raised to the specified power ( exp(4) = e ^ 4 )
  • sqr – sqr(<input> – The square root of the specified value (sqr(4) = 2 )
  • min – min(<input1>, <input2>) – The minimum of two numbers ( min(4, 5) = 4 )
  • smaller – smaller(<input1>, <input2>, <input3>) – The smaller or minimum of three numbers ( smaller(6,1,8) = 1 )
  • max – max(<input1>, <input2>) – The maximum of two numbers (max(4,5) = 5 )
  • larger – larger(<input1>, <input2>, <input3>) – The larger or maximum of three numbers ( larger(6,1,8) = 8 )
  • iif – iif(<logic expression>, <expression if true>, <expression if false> – Evaluates a logic expression and returns a value if the express is true or a different value if the expression is false.  Each expression may be any combination of operations or functions available.
  • now – now() – Returns the current date/time as a single date/time object.  Only use this in conjunction with the year or age function.
  • year – year(<input>) – Returns the year of a specified date.  The date must come from either the now()  function or from an input value formatted as a Date correctly in Excel.
  • age – age(<input>) – Returns the the difference between now and the specified input in years.  The input can be an input from a formatted date in Excel, or a date format of the form: YYYY, YYYYMM, YYYYMMDD.
  • left – left(<input>) – Returns the left four characters of the input.  If the input is a number, the first four digits are returned ( left(1234567) = 1234 ) if the input is a date, the year is returned.  This is often used to get the date for a data formatted as: YYYY, YYYYMM or YYYYMMDD.
  • int – int(<input>) – Converts the specified input to an integer by rounding.  ( int(4.4) = 4 )  This is the same as round(<input>, 0).
  • round – round(<input1>, <number of places>) – Rounds the input to the number of places specified. ( round(847.45628, 2) = 847.46 )
  • nz – nz(<input1>, <input2>) – Returns <input1> if <input1> is greater than zero, otherwise returns <input2>.  This is often used to protect formulas from zero values or avoiding divide by zero errors.  ( nz(0, 5) = 5 )

The special case of IIF

The iif function provides additional functionality by allowing you to change the result of a function based on a conditional statement, or an “if statement.”  This function works similarly to the iif function in Excel.  The function takes three arguments:

1. A logic expression – This expression must evaluate to true or false.  There are additional operations that you can use to facilitate this: less than, greater than, less than or equal to, greater than or equal to, and equals.  These can be combined with any other function or operation that you could normally use.  The following are all valid logic expressions because the can only be either true or false:

  • 1 > 3
  • [MAOP] < 1000
  • [MAOP] / 2 > [Min maop]
  • age([pipe install year]) > 50


2. An expression to evaluate if the logic expression is true.

3. An expression to evaluate if the logic expression is false.

For items 2 and three above, any valid expression can be used, i.e. a formula.  Things can get a little complicated, but any standard equation or formula that you would but into a variable can be used here.

Example expressions (formulas):

  • min((0.1 * [depth of cover]), .999)
  • [effective wall thickness] / [exposure2] * (1-[CRS_mitigation]) * (1-[cp_mitigation])