AFC - Abacus Formula Compiler for Java

Exceptions Thrown By Computations

In spreadsheet applications, error situations are flagged by the affected cells showing special error values, like #VALUE!. This is very much like throwing an exception in Java, so this is what AFC does in compiled computations.

Errors (#VALUE! etc.)

In AFC, computations that result in errors like #VALUE!, #NUM!, #DIV/0!, etc. do:

  • throw a FormulaException where AFC tries to give a little more information about the cause of the error in the message,
  • throw Java’s own ArithmeticException, or
  • return one of Java’s own Double.POSITIVE_INFINITY, Double.NEGATIVE_INFINITY, or Double.NaN.

This means AFC uses Java’s own arithmetic semantics wherever possible. So when we bind cells from this sheet:

A B C
3 Bad number #NUM!
=ASIN(C3)		 2
4 Bad value #VALUE!
=CHOOSE(C4,12.0)		 0
5 Division by zero #DIV/0!
=1.0/C5		 0

B3 yields (using a bunch of test-specific helper methods whose name should make clear what they do – if not, look at the org.formulacompiler.tutorials.Exceptions test class):

setupOutputCell( "B3" );
setupInputCell( "C3" );
// This works:
assertRunsWithInputReturning( 1.0, Math.asin( 1.0 ) );
// Invalid argument to ASIN() fails with a special return value for doubles:
assertRunsWithInputReturning( 2.0, Double.NaN );
// But with an exception for BigDecimals:
useNumericType( FormulaCompiler.BIGDECIMAL128 );
assertFailsWhenRunWithInput( 2.0, FormulaException.class, "#NUM! (value is NaN)" );

B4, likewise, yields:

setupOutputCell( "B4" );
setupInputCell( "C4" );
assertRunsWithInputReturning( 1.0, 12.0 );
assertFailsWhenRunWithInput( 0.0, FormulaException.class, "#VALUE! because index to CHOOSE is out of range" );

B5 yields:

setupOutputCell( "B5" );
setupInputCell( "C5" );
assertRunsWithInputReturning( 1.0, 1.0 );
// Division by zero fails with a special return value for doubles:
assertRunsWithInputReturning( 0.0, Double.POSITIVE_INFINITY );
// But with an exception for BigDecimals:
useNumericType( FormulaCompiler.BIGDECIMAL128 );
assertFailsWhenRunWithInput( 0.0, ArithmeticException.class, null );
Compile-time

If we compile B3 without any bound inputs, meaning the result will always be #NUM!, AFC still raises the error when the computation is run, not during compilation of the engine (which in principle it could):

setupOutputCell( "B3" );
assertRunsWithInputReturning( 2.0, Double.NaN );
// The exception for BigDecimals is raised at *runtime*:
useNumericType( FormulaCompiler.BIGDECIMAL128 );
Outputs computation = newOutputs( null ); // Compilation succeeds.
try {
  double have = computation.result(); // Execution fails.
  fail( "Exception expected, but got " + have );
}
catch (FormulaException err) {
  assertEquals( "#NUM! (value is NaN)", err.getMessage() );
}
Caching

If we enable caching, repeated executions still repeatedly throw the error:

setupOutputCell( "B3" );
setupInputCell( "C3" );
useNumericType( FormulaCompiler.BIGDECIMAL128 );
b.setFullCaching( true );
Outputs computation = newOutputs( new ValueInput( 2.0 ) );
for (int i = 0; i < 2; i++) {
  try {
    double have = computation.result(); // Execution fails repeatedly.
    fail( "Exception expected, but got " + have );
  }
  catch (FormulaException err) {
    assertEquals( "#NUM! (value is NaN)", err.getMessage() );
  }
}

However, AFC makes no guarantee that the failure is cached, nor that it always returns the same error instance. But it will always return the same error type and description. In fact, in the current version, AFC will not cache it but re-compute faulting expression(s) on every access.

Error Propagation

As in spreadsheet applications, errors are propagated to other cells referencing them (which, of course, is what you’d expect from Java exceptions), so in:

A B
6 Error is propagated #NUM!
=1.0+B3

B6, like cell B3 before, yields:

setupOutputCell( "B6" );
setupInputCell( "C3" );
assertRunsWithInputReturning( 1.0, 1.0 + Math.asin( 1.0 ) );
assertRunsWithInputReturning( 2.0, Double.NaN );
useNumericType( FormulaCompiler.BIGDECIMAL128 );
assertFailsWhenRunWithInput( 2.0, FormulaException.class, "#NUM! (value is NaN)" );

Handling Errors In Expressions

Errors can be detected and handled in spreadsheet formulas using the ISERR(exp) function, so in:

A B
7 Handle error 4712
=1.0+IF(ISERR(B3),4711.0,B3)

B7 yields:

setupOutputCell( "B7" );
setupInputCell( "C3" );
assertRunsWithInputReturning( 1.0, 1.0 + Math.asin( 1.0 ) );
assertRunsWithInputReturning( 2.0, 4712 );

// The above failed when accessing B3 directly.
useNumericType( FormulaCompiler.BIGDECIMAL128 );
assertRunsWithInputReturning( 2.0, 4712 );

which compiles to:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.FormulaException;
import org.formulacompiler.runtime.NotAvailableException;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.tutorials.Exceptions;

final class $Root implements Computation, Exceptions.Outputs
{
    private final Exceptions.Inputs $inputs;
    final Environment $environment;
    
    $Root(Exceptions.Inputs inputs, Environment environment) {
        $environment = environment;
        $inputs = inputs;
    }
    
    final double get$0() {
        return 1.0 + (get$3() != 0.0 ? 4711.0 : get$1());
    }
    
    public final double result() {
        return get$0();
    }
    
    final double get$3() {
        double d;
        try {
            double d_0_;
            d = (Double.isInfinite(d_0_ = get$1()) || Double.isNaN(d_0_) ? 1.0
                 : 0.0);
        } catch (FormulaException formulaexception) {
            d = 1.0;
        } catch (ArithmeticException arithmeticexception) {
            d = 1.0;
        } catch (NotAvailableException notavailableexception) {
            d = 0.0;
        }
        return d;
    }
    
    final double get$1() {
        return Math.asin(get$2());
    }
    
    final double get$2() {
        return $inputs.value();
    }
}

Throwing Errors

You can also throw your own errors in input getter methods:

public static final class ThrowingInput implements Inputs {
  public double value() {
    throw new FormulaException( "My error" );
  }
}

which yields:

setupOutputCell( "B3" );
setupInputCell( "C3" );
assertFailsWhenRunWithInput( new ThrowingInput(), FormulaException.class, "My error" );

You can, in fact, throw any unchecked exception. Throwing checked exceptions is currently not supported (because AFC would have to make sure that all bound output methods support all of the checked exceptions thrown by any bound input method).

Missing Values (#N/A)

Spreadsheets also have a special function, NA(), that is similar to throwing an exception in Java code. It returns #N/A, which propagates to other cells like errors. You can also enter #N/A directly in cells, which AFC also supports. AFC throws a NotAvailableException when encountering any of these forms of #N/A. So in:

A B C
9 Not available using NA() #N/A
=NA()
10 Not available using #N/A #N/A
11 Not available thrown by getter 1
=C11		 1
12 Not available if #N/A
=IF(C12=1.0,NA(),C12)		 1

B9 and B10 yield:

for (int row = 9; row <= 10; row++) {
  setupOutputCell( "B" + row ); // B9, B10
  Outputs computation = newOutputs( null ); // Compilation succeeds.
  try {
    double have = computation.result(); // Execution fails.
    fail( "Exception expected, but got " + have );
  }
  catch (NotAvailableException err) {
    assertEquals( "#N/A", err.getMessage() );
  }
}

Throwing #N/A

Like errors, you can throw NotAvailableException in input getter methods to signal data that is not available:

public static final class InputNotAvailable implements Inputs {
  public double value() {
    throw new NotAvailableException( "My message" );
  }
}

which yields:

setupOutputCell( "B11" );
setupInputCell( "C11" );
assertFailsWhenRunWithInput( new InputNotAvailable(), NotAvailableException.class, "My message" );

Handling #N/A In Expressions

Again like errors, you can handle #N/A, but using ISNA(exp), as in:

A B
13 Handle N/A 4712
=1.0+IF(ISNA(B12),4711.0,B12)

B7 yields:

setupOutputCell( "B13" );
setupInputCell( "C12" );
assertRunsWithInputReturning( 2.0, 3.0 );
assertRunsWithInputReturning( 1.0, 4712 );
// The above failed when accessing B11 directly.

which compiles to:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.FormulaException;
import org.formulacompiler.runtime.NotAvailableException;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.runtime.internal.Runtime_v2;
import org.formulacompiler.tutorials.Exceptions;

final class $Root implements Computation, Exceptions.Outputs
{
    private final Exceptions.Inputs $inputs;
    final Environment $environment;
    
    $Root(Exceptions.Inputs inputs, Environment environment) {
        $environment = environment;
        $inputs = inputs;
    }
    
    final double get$0() {
        return 1.0 + (get$3() != 0.0 ? 4711.0 : get$1());
    }
    
    public final double result() {
        return get$0();
    }
    
    final double get$3() {
        double d;
        try {
            get$1();
            d = 0.0;
        } catch (NotAvailableException notavailableexception) {
            d = 1.0;
        } catch (FormulaException formulaexception) {
            d = 0.0;
        } catch (ArithmeticException arithmeticexception) {
            d = 0.0;
        }
        return d;
    }
    
    final double get$1() {
        double d;
        if (get$2() == 1.0) {
            Runtime_v2.fun_NA();
            d = (double) -1;
        } else
            d = get$2();
        return d;
    }
    
    final double get$2() {
        return $inputs.value();
    }
}

Interplay

Precedence

When you evaluate something like:

A B
15 Error referenced before N/A #NUM!
=B3+B9
16 N/A referenced before error #N/A
=B9+B3

the result depends on the order in which the arguments to the functions or operators are evaluated. While mostly compatible with the way spreadsheet applications behave for this, AFC makes no guarantee that this is always so. This is because the constant folder can change the order in which operands are evaluated.

Handling #N/A And Errors In Parallel

The ISERROR(exp) function is like OR(ISNA(exp), ISERR(exp)), but evaluates exp only once. So in:

A B
18 IsError() on N/A true
=ISERROR(B12)
19 IsError() on error true
=ISERROR(B3)
20 IsErr() on N/A false
=ISERR(B12)
21 IsNA() on error false
=ISNA(B3)

B16 and B17 yield:

for (int row = 18; row <= 19; row++) {
  setupOutputCell( "B" + row ); // B18, B19
  assertRunsWithInputReturning( null, 1.0 ); // 1.0 means true
  useNumericType( FormulaCompiler.BIGDECIMAL128 );
  assertRunsWithInputReturning( null, 1.0 ); // 1.0 means true
}

This is implemented by handling ComputationException, from which both FormulaException and NotAvailableException are derived. The Excel function ERROR.TYPE(exp) is currently not supported by AFC.

And, for the sake of completeness, for B18 and B19 we get:

setupOutputCell( "B20" );
setupInputCell( "C12" );
assertRunsWithInputReturning( 1.0, 0.0 ); // 0.0 means false

setupOutputCell( "B21" );
setupInputCell( "C3" );
assertRunsWithInputReturning( 2.0, 0.0 ); // 0.0 means false

Effect on COUNT() and COUNTA()

In spreadsheet applications, the function COUNT() ignores error values and #N/A (does not count them), while COUNTA() does. Both do not propagate the exception.

In AFC, this is similar, except that because of a known limitation, COUNT() behaves exactly like COUNTA(), meaning it counts exception values. So in:

A B
23 COUNT() ignores errors and N/A 1
=COUNT(15.0,B3,B12)
24 COUNTA() counts errors and N/A 3
=COUNTA(15.0,B3,B12)

B21 and B22 yield:

for (int row = 23; row <= 24; row++) {
  setupOutputCell( "B" + row ); // B23, B24
  assertRunsWithInputReturning( null, 3.0 );
}