Formula Calculations

What Are Formula Calculations?

A formula calculation refers to a mathematical operation or expression that you can build to perform specific tasks within your formulas. These calculations allow you to add, subtract, multiply, divide, and perform other mathematical or logical operations directly within your formulas.

TraceGains has recently introduced advanced formula calculation abilities, offering more powerful and versatile mathematical and logical tools. These enhancements make it easier to create complex and customized formulas to meet your unique needs. Using the in-site tool can also be more efficient than relying on traditional Excel spreadsheets and workbooks running in the background.

Below are the key features available within TraceGains Formula Calculations.

TraceGains Calculations Key Features

Complex Equations

Calculations enable you to create intricate mathematical and/or logical expressions within your formulas. You can combine data sheet attributes, calculations, constants, operators, subtotals, totals, and more to design highly customized formulas.

Conditional Statements 

You can incorporate conditional statements into your formulas, allowing you to create dynamic expressions that adapt to specific conditions or dependencies. This flexibility supports evolving requirements and ensures precise calculations.

Order of Operations 

The Formula Calculations tool allows you to use calculations or conditional statements inside other calculations to control the order of operations in an equation. The expression area of the configuration tool visually groups statements for evaluation.

Category Definitions

Before creating a formula calculation, it is helpful to understand the available categories. Categories are determined by the Use selected when adding any column to a formula.

• Attributes: Choose between a Float or Integer script. Integers are whole numbers, and Floats contain decimal points. When creating a Conditional calculation, you can also include Text attributes.
• Value: A user-added string or numeric value. String values can be used as comparisons inside conditional statements. Numeric values can be used inside calculations, as comparisons in conditionals, or as results of a conditional statement.
• Calculations: Add pre-existing calculations to a new calculation to create more complex expressions.
• Conditionals: Add a conditional to your expression to work with specific subsets of data.
• Subtotals: Add a subtotal calculation or column to the new expression.
• Totals: Add a total calculation or column to the new expression.

How to Create a New Calculation

Creating a new calculation involves three 3 main steps:

1. Fill out the basic information
2. Use the Expression Builder
3. Determine the Order of Operations, starting with Input and ending with Output.

Fill Out Basic Information

Step 1: Go to Configuration > Calculations.

Step 2: Click New. 

Step 3: Complete the following fields:

• Name: Enter an easily referenced name that indicates what action the calculation will perform. For example, “Percentage Calculation”.
• Calculation Type: Choose between Formula Calculation and Conditional
• Active: Active or inactive
• Use: Select from the drop-down
• Group: Select a Calculation Group
• Script Type: Float or integer
• Description: Enter a description that gives further context for the calculation, if necessary. For example: “For calculating volume percentages for liquid formulas”

• Total Row Options: 

  Choose between: 

  • Sum all the values from each row
  • Use this calculation on the total row
  • Use this calculation on the total row - show total only

Step 4: Click Save. 

After completing the Basic Information section, proceed to build your expression.

Expression Builder

After completing the Basic Information section, scroll down on the same screen to access the Expression Builder. The Expression Builder is located directly below the Basic Information fields.

Step 1: Select a category to find objects (formula columns) within. 

The Expression Builder can only account for attributes and configurations already established in your system. 

For example, selecting “Subtotals” will only show subtotal columns you have already created. “Attributes” is limited to Float and Integer types.

Step 2: Narrow down the category by choosing an attribute, if applicable.

Step 3: Use the search text box to refine results, if necessary.

Step 4: Click Add or double-click the row to add the object to your expression.

Step 5: Proceed to determine the Order of Operations.

Order of Operations

After creating your initial expression, you can use it as-is by adding it to a formula column, or use it within another calculation to control the order of operations in more complex statements.

The expression area visually groups statements to show how they will be evaluated.

Important Notes

• If a calculation will be used in a formula column, select that formula column for use in further calculations.
• If a calculation doesn’t need to be displayed on a formula, use those for building more complex calculations. 
• Conditionals can also be used in formula columns, or as building blocks of new calculations.