Supported Excel Functions

Example 2

In this example the lambdaField is created based on the COUNTIFS function that counts numbers that are greater than 60. Its LAMBDA function has one ComputeFields range parameter range_parameter and is named MYCOUNT. It is used by the following formula my_formula:

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

public class LambdaFieldExample2 {
    private static ComputeField createInputField(Object value) {
        ComputeField computeField = new InputField(ComputeFieldType.NUMERIC, value);
        computeField.setRangeName("criteria_range");
        return (computeField);
    }

    public static void main(String[] args) {
        String lambdaFormulaText = "COUNTIFS(range_parameter, \">\", 60)";
        LambdaField lambdaField = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText);
        CalculationContext calculationContext = new CalculationContext("LambdaFieldExample2");

        calculationContext.put("criteria_val1", createInputField(59));
        calculationContext.put("criteria_val2", createInputField(64));
        calculationContext.put("criteria_val3", createInputField(85));
        calculationContext.put("criteria_val4", createInputField(95));

        calculationContext.put("MYCOUNT", lambdaField);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, "MYCOUNT(criteria_range)"));
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);
        System.out.println("Lambda function: " + lambdaField.getLambdaFunction());
        System.out.println("Calculated result: " +  calculationContext.get("my_formula").getDisplayValue());
    }
}

Lambda function: LAMBDA(range_parameter, COUNTIFS(range_parameter, ">", 60))
Calculated result: 3

Example 4

In this example the recursive Lambda function recur_adder() is built based on the example published in the following article. It takes four arguments: iteration - the current iteration of the function; times_to_add - the number of times to add the previous numbe; value - the initial (current) value; the value to add. The recur_adder() function adds the value_to_add to the value for as long as the iteration is less than the times_to_add. To achieve this it uses the second Lambda function adder(), which takes two arguments, the value and the value_to_add. The following iterative formula my_formula performs 1,000,000 recursive iterative invocations of the recur_adder() function in about 10 seconds without experiencing any limit of iterations:

my_formula=recur_adder(iteration, times_to_add, value, value_to_add)

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

public class LambdaFieldExample4 {
    public static void main(String[] args) {
        String lambdaFormulaText1 = "value + value_to_add";
        String lambdaFormulaText2 = "IF(iteration<times_to_add, " +
                "recur_adder(iteration+1, times_to_add, adder(value, value_to_add), value_to_add), value)";
        String formulaTex = "recur_adder(iteration, times_to_add, value, value_to_add)";

        LambdaField lambdaField1 = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText1);
        LambdaField lambdaField2 = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText2);
        CalculationContext calculationContext = new CalculationContext("LambdaFieldExample4");

        calculationContext.put("value", new InputField(ComputeFieldType.NUMERIC, 100));
        calculationContext.put("value_to_add", new InputField(ComputeFieldType.NUMERIC, 1));
        calculationContext.put("times_to_add", new InputField(ComputeFieldType.NUMERIC, 1000000));
        calculationContext.put("iteration", new InputField(ComputeFieldType.NUMERIC, 0));

        calculationContext.put("adder", lambdaField1);
        calculationContext.put("recur_adder", lambdaField2);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, formulaTex));
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);

        System.out.println("adder()       = " + lambdaField1.getLambdaFunction());
        System.out.println("recur_adder() = " + lambdaField2.getLambdaFunction().substring(0, 52));
        System.out.println("                " + lambdaField2.getLambdaFunction().substring(53, 79));
        System.out.println("                " + lambdaField2.getLambdaFunction().substring(80, 161));
        System.out.println("                " + lambdaField2.getLambdaFunction().substring(162));
        System.out.println("Calculated result: " + calculationContext.get("my_formula").getDisplayValue());
    }
}

adder()       = LAMBDA(value, value_to_add, value + value_to_add)
recur_adder() = LAMBDA(iteration, times_to_add, value, value_to_add,
                IF(iteration<times_to_add,
                recur_adder(iteration+1, times_to_add, adder(value, value_to_add), value_to_add),
                value))
Calculated result: 1000100

Example 6

In this example the recursive Lambda function Fibonacci() calculates the Fibonacci numbers as shown in the following formula my_formula:

my_formula=Fibonacci(X)

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

public class LambdaFieldExample6 {
    public static void main(String[] args) {
        String lambdaFormulaText = "IF(X < 2, X, Fibonacci(X - 1) + Fibonacci(X - 2))";
        String formulaTex = "Fibonacci(X)";

        LambdaField lambdaField = new LambdaField(ComputeFieldType.NUMERIC, lambdaFormulaText);
        CalculationContext calculationContext = new CalculationContext("FibonacciFunction");

        calculationContext.put("X", new InputField(ComputeFieldType.NUMERIC, 20));
        calculationContext.put("Fibonacci", lambdaField);
        calculationContext.put("my_formula", new FormulaField(ComputeFieldType.NUMERIC, formulaTex));
        HybridFormulaEvaluator.evaluateFormula("my_formula", calculationContext);

        System.out.println("Fibonacci() = " + lambdaField.getLambdaFunction());
        System.out.println("Calculated result: " + calculationContext.get("my_formula").getDisplayValue());
    }
}

Fibonacci() = LAMBDA(X, IF(X < 2, X, Fibonacci(X - 1) + Fibonacci(X - 2)))
Calculated result: 6765