Operators Functions APIPricelist Company Blog Contact Us

FormulaIterator Class

The Adaptive Calculation Engine enabler of iterative calculations.

Introduction

Microsoft admits that one of the big missing pieces in Excel formulas has been the ability to loop, to repeat over a set of logic at a dynamically defined interval. There are ways that you can manually configure the interval at which Excel recalculates to mimic this to an extent, but it's not inherent to the formula language. That changed with recursive Excel Lambda functions. With the Adaptive Calculation Engine implementation of recursive Lambda functions its formulas also gained the ability to loop. However it is noticeable that in both cases recursive formulas are bulky, lack flexibility, and are not easy to write.

The Adaptive Calculation Engine FormulaIterator substantially improves these drawbacks.

Suppose we need to calculate the factorial of six, 6! The below code shows how this can be done easily with the FormulaIterator:

String myExpressionText = "NEXT()";
double initValue = 1, increment = 1, maxValue = 6;
FormulaIterator formulaIterator = new FormulaIterator(initValue, increment, maxValue, LoopOperator.MULTIPLY);
FormulaField myExpressionField = new FormulaField(ComputeFieldType.NUMERIC, myExpressionText);

myExpressionField.setFormulaIterator(formulaIterator);
HybridFormulaEvaluator.evaluateExpression(myExpressionField);
System.out.printf("Calculated result: %s", myExpressionField.getDisplayValue());

Calculated result: 720

FormulaIterator Allows Formulas to

  • fiber_manual_record : Initiate the explicit iteration loop for calculations;
  • fiber_manual_record : Use the prior knowledge of the number of times the calculation will have to repeat;
  • fiber_manual_record : Have flexible termination criteria;
  • fiber_manual_record : Declare a loop level operator by means of which the n-th approximation is derived from the previous ones
  • fiber_manual_record : Effectively use four special functions, NEXT(), VALUE(), DIFF(), and STOP().

LoopOperator

The FormulaIterator uses the following loop operators:

  • fiber_manual_record LoopOperator.VOID
    • stop The n-th approximation should be derived from the previous ones without using the loop operator.
    • stop Default values for uninitialized formulas: 0 - for NUMERIC, an empty string for ALPHANUMERIC, and FALSE for BOOLEAN formulas.
  • fiber_manual_record LoopOperator.ADD
    • stop The n-th approximation is calculated as a sum of the previous ones.
    • stop The default value for uninitialized formulas is 0.
  • fiber_manual_record LoopOperator.MULTIPLY
    • stop The n-th approximation is calculated as a multiplication of the previous ones.
    • stop The default value for uninitialized formulas is 1.
  • fiber_manual_record LoopOperator.CONCAT
    • stop The n-th approximation is a concatenation of the previous ones.
    • stop The default value for uninitialized formulas is an empty string.

NEXT(), VALUE(), DIFF(), and STOP() Functions

The NEXT() function obtains the value of the FormulaIterator loop variable at the beginning of each iteration.
The VALUE() function obtains the value of the formula approximation at the beginning of each iteration.
The DIFF() function obtains the absolute value of the difference between the n+1-th and n-th formula approximations at the beginning of each iteration.
At the beginning of the FormulaIterator loop the DIFF() function is equal to the formula value.
The STOP() function terminates the iterative process such that the last approximation becomes the formula value.

Iterative Process with Termination Criteria

Example 6 explains how to find an approximate solution to the equation x2 - 3x + 1 = 0.

The approximating formula in this example uses the FormulaIterator convergent iterative calculations as shown in the bellow flow chart:

Overview

The FormulaIterator class has two constructors:

FormulaIterator(double initValue, double increment, double maxValue, LoopOperator loopOperator)

FormulaIterator(double initValue, double increment, LoopOperator loopOperator)

The first constructor creates a FormulaIterator object that will initiate a loop for a formula field when the prior knowledge of how many times the calculation will have to repeat is available. It throws the IllegalArgumentException if initValue <= maxValue and increment <= 0.

The second constructor creates a FormulaIterator object that will initiate an infinite loop, which requires some termination criteria.

This class offers the following methods:

double getInitValue()

setInitValue(double initValue)

double getIncrement()

setIncrement(double increment)

double getMaxValue()

setMaxValue(double maxValue)

LoopOperator getLoopOperator()

setLoopOperator(LoopOperator loopOperator)

Integer getPrecision()

setPrecision(Integer precision)

This method sets the precision of the n-th approximation calculation to the number of decimal places equal to the precision. This setting is used for the LoopOperator.ADD and LoopOperator.MULTIPLY.

double getLastValueOfLoopVariable()

This method obtains the value of the FormulaIterator loop variable at the beginning of the last iteration.

Example 1

The total_cash formula in this example is built based on the example published in the following article. Let's assume that we have an initial cash value (the initial_cash) of $10,000 and want to invest this money in a cash deposit (CD) account. Let's also assume a monthly interest rate (the interest_rate) of 1.25%. To calculate the total value at the end of the 24th month (the total_cash), we're going to calculate the principal for each month using the VALUE() function, and add the interest to the previous month. The NEXT() function retrieves the loop variable. In this example the condition NEXT()=0 is true only at the beginning of the formulaIterator loop. At this time its loop variable is equal to the loop initValue and the value of the total_cash is set to the initial_cash value:

total_cash=ROUND(IF(NEXT()=0, initial_cash, VALUE() * (1 + interest_rate)), 3)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample1 {
    public static void main(String[] args) {
        String myFormulaText = "ROUND(IF(NEXT()=0, initial_cash, VALUE() * (1 + interest_rate)), 3)";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.NUMERIC, myFormulaText);
        double initValue = 0, increment = 1, maxValue = 24;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment, maxValue, LoopOperator.VOID);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample1");

        calculationContext.put("interest_rate", new InputField(ComputeFieldType.NUMERIC, 0.0125));
        calculationContext.put("initial_cash", new InputField(ComputeFieldType.NUMERIC, 10000));
        calculationContext.put("total_cash", myFormulaField);

        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("total_cash", calculationContext);
        System.out.println("Total cash at the end of the 24th month: " + myFormulaField.getDisplayValue());
    }
}

Total cash at the end of the 24th month: 13473.511

Example 2

This example is a simplified version of the previous example. It uses the LoopOperator.ADD as opposed to the LoopOperator.VOID. This means that the principal for each month can be removed from the total_cash formula because it will be added by the formulaIterator itself. The total_cash rounding to a scale of 3 will be done by the formulaIterator.setPrecision() setter:

total_cash=IF(NEXT()=0, initial_cash, VALUE() * interest_rate)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample2 {
    public static void main(String[] args) {
        String myFormulaText = "IF(NEXT()=0, initial_cash, VALUE() * interest_rate)";
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.NUMERIC, myFormulaText);
        double initValue = 0, increment = 1, maxValue = 24;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment, maxValue, LoopOperator.ADD);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample2");

        calculationContext.put("interest_rate", new InputField(ComputeFieldType.NUMERIC, 0.0125));
        calculationContext.put("initial_cash", new InputField(ComputeFieldType.NUMERIC, 10000));
        calculationContext.put("total_cash", myFormulaField);

        formulaIterator.setPrecision(3);
        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("total_cash", calculationContext);
        System.out.println("Total cash at the end of the 24th month: " + myFormulaField.getDisplayValue());
    }
}

Total cash at the end of the 24th month: 13473.511

Example 3

In this example the first column of the the TableArray my_table contains the amounts which correspond to fruit names in its second column. The expression in this example iterates over these amounts using the NEXT() function. Each iteration invokes the VLOOKUP() function to retrieve the corresponding fruit name and pass it to the formulaIterator's LoopOperator.CONCAT. At the end of the the loop initiated by the formulaIterator the expression calculated value will contain all retrieved fruit names concatenated in a text string:

VLOOKUP(NEXT(), "my_table", 2)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample3 {
    public static void main(String[] args) {
        Object[][] tableArrayBase = {{10, " Apple "},
                                     {30, " Pear "},
                                     {50, " Orange "},
                                     {70, " Banana "}};
        TableArray tableArray = new TableArray(tableArrayBase);
        String myExpressionText = "VLOOKUP(NEXT(), \"my_table\", 2)";
        FormulaField myExpressionField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myExpressionText);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample3");
        double initValue = 10, increment = 20, maxValue = 70;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment,  maxValue, LoopOperator.CONCAT);

        myExpressionField.setFormulaIterator(formulaIterator);
        calculationContext.put("my_table", tableArray);

        HybridFormulaEvaluator.evaluateExpression(myExpressionField, calculationContext);
        System.out.println("Calculated result: " + myExpressionField.getDisplayValue());
    }
}

Calculated result:  Apple  Pear  Orange  Banana

Example 4

Defining a factorial function n! via a recursive Lambda function is not a trivial task. Nevertheless, defining the factorial via the FormulaIterator is a straightforward task, as it shown in the following example. It uses the LoopOperator.MULTIPLY to multiply the NEXT() function values (1, 2, 3, 4, 5, ...) after each iteration. If n = 0, the LoopOperator.MULTIPLY will set the factorial_formula value to its default value of 1. In this example n = 5, so n! = 120:

factorial_formula=IFS(n<0,error,  NEXT()<=n,NEXT(),  NEXT()>n,STOP())

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample4 {
    public static void main(String[] args) {
        String myFormulaText = "IFS(n<0,error,  NEXT()<=n,NEXT(),  NEXT()>n,STOP())";
        double initValue = 1, increment = 1;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment,
                LoopOperator.MULTIPLY);
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.NUMERIC, myFormulaText);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample4");

        calculationContext.put("n", new InputField(ComputeFieldType.NUMERIC, 5));
        calculationContext.put("factorial_formula", myFormulaField);
        calculationContext.put("error", new InputField(ComputeFieldType.ALPHANUMERIC, "?"));

        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("factorial_formula", calculationContext);
        System.out.println("Calculated result: " + myFormulaField.getDisplayValue());
    }
}

Calculated result:  120

Example 5

This example shows a simplified version of the recursive Lambda function REPLACECHARS() published on our website. This version is not recursive. It iterates over the list of "illegal" characters (in our example '1', '2', and '3') to be removed using the FormulaIterator functionality. At the beginning of the formulaIterator infinite loop the IFS statement sets the my_formula field value to the text string of the input field text_string. Then the IFS statement provides that if the NEXT() function value is less than or equal to the length of the illegal_chars text string, the next "illegal" character will be removed from the current approximation of the my_formula text string by the SUBSTITUTE() function using the VALUE() function as an argument, and otherwise invoke the STOP() function to break out of the loop with the expected result:

my_formula=REPLACECHARS(text_string, illegal_chars)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample5 {
    public static void main(String[] args) {
        double initValue = 0, increment = 1;
        FormulaIterator formulaIterator = new FormulaIterator(initValue, increment, LoopOperator.VOID);
        String textToClean = "1ONE1 2TWO2 3THREE3";
        String myLambdaText =
                "IFS(NEXT()=0, text_string, " +
                    "NEXT()<=LEN(illegal_chars), SUBSTITUTE(VALUE(), MID(illegal_chars, NEXT(), 1), \"\"), " +
                    "NEXT()>LEN(illegal_chars), STOP())";
        String myFormulaText = "REPLACECHARS(text_string, illegal_chars)";
        LambdaField myLambdaField = new LambdaField(ComputeFieldType.ALPHANUMERIC, myLambdaText);
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.ALPHANUMERIC, myFormulaText);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample5");

        calculationContext.put("text_string", new InputField(ComputeFieldType.ALPHANUMERIC, textToClean));
        calculationContext.put("illegal_chars", new InputField(ComputeFieldType.ALPHANUMERIC, "123"));
        calculationContext.put("REPLACECHARS", myLambdaField);
        calculationContext.put("my_formula", myFormulaField);

        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Calculated result: " + myFormulaField.getDisplayValue());
    }
}

Calculated result: ONE TWO THREE

Example 6

This example uses the iterative processes to find the approximate solution for the equation x2 - 3x + 1 = 0. This equation can be rewritten as x = 3 - 1/ x. The formula my_formula will try to solve the equation by using the APPROXIMATOR() Lambda function defined by the formula xn+1 = 3 - 1/xn. When the difference between xn+1 and xn is less than 0.00001 my_formula will invoke the STOP() function to finish the iterative process. This example also uses the formulaIterator.getLastValueOfLoopVariable() method to determine how many iterations were performed by the my_formula to find the desired approximate solution to the given equation:

my_formula=ROUND(APPROXIMATOR(initial_approximation), 5)

import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.usermodel.*;

public class FormulaIteratorExample6 {
    public static void main(String[] args) {
        double initValue = 0, increment = 1;
        FormulaIterator formulaIterator = new FormulaIterator(initValue,  increment,  LoopOperator.VOID);
        String myLambdaText = "IFS(NEXT() = 0, initial_approximation, " +
                                  "DIFF() <= change_between_iterations, STOP(), " +
                                  "DIFF() > change_between_iterations, 3 - 1/VALUE())";
        String myFormulaText = "ROUND(APPROXIMATOR(initial_approximation, change_between_iterations), 5)";
        LambdaField myLambdaField = new LambdaField(ComputeFieldType.NUMERIC, myLambdaText);
        FormulaField myFormulaField = new FormulaField(ComputeFieldType.NUMERIC, myFormulaText);
        CalculationContext calculationContext = new CalculationContext("FormulaIteratorExample6");

        calculationContext.put("initial_approximation", new InputField(ComputeFieldType.NUMERIC, 2));
        calculationContext.put("change_between_iterations", new InputField(ComputeFieldType.NUMERIC, 0.00001));
        calculationContext.put("APPROXIMATOR", myLambdaField);
        calculationContext.put("my_formula", myFormulaField);

        myFormulaField.setFormulaIterator(formulaIterator);
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Calculated result: " + myFormulaField.getDisplayValue());
        System.out.println("Number of iterations: " + (int) formulaIterator.getLastValueOfLoopVariable());
    }
}

Calculated result: 2.61803
Number of iterations: 8