Package org.apache.poi.ss.util

Class ExcelArithmetic

java.lang.Object
org.apache.poi.ss.util.ExcelArithmetic
public final class ExcelArithmetic extends Object
Arithmetic helpers that reproduce how Excel computes with IEEE 754 doubles.

Excel stores and calculates in double precision, but only ever exposes 15 significant decimal digits, and it compensates for binary representation error in a few well-defined places. These helpers implement those places so that formula evaluation matches what a user sees in Excel.

Since:
6.0.0
See Also:

  • Method Summary

    Modifier and Type
    Method
    Description
    static double
    approxAdd(double a, double b)
    Excel 97 and later compensate for the error introduced by converting decimal operands to binary when an addition or subtraction lands at or very close to zero: if the two magnitudes are equal to Excel (same 15 significant digits, see NumberComparer), the result is exactly zero.
    static double
    approxSub(double a, double b)
     
    static double
    approxValue(double d)
    Returns the value as Excel sees it: rounded to 15 significant decimal digits.
    static BigDecimal
    toBigDecimal(double d)
    Returns the value as Excel sees it, as an exact decimal: the 15 significant digits of approxValue(double), so that decimal arithmetic on it (rounding to a number of places, or to a multiple of a step) gives the answer Excel gives.
    static double
    truncate(double d)
    Truncates towards zero on the 15-digit view of the value, which is what Excel does with the non-integer arguments of QUOTIENT, GCD, LCM, FACT, FACTDOUBLE, COMBIN, ISEVEN, ISODD and friends ("if number is not an integer, it is truncated"): 2.9999999999999996 and -2.9999999999999996 become 3 and -3, 2.5 becomes 2.

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Method Details

    • approxValue

      public static double approxValue(double d)
      Returns the value as Excel sees it: rounded to 15 significant decimal digits. Functions that truncate (INT, FLOOR, CEILING, MOD, ...) act on this view rather than on the raw binary value - e.g. INT(2490399.9999999995) is 2490400 in Excel. Integers and values with few fractional bits are exactly representable and are returned unchanged.

    • toBigDecimal

      public static BigDecimal toBigDecimal(double d)
      Returns the value as Excel sees it, as an exact decimal: the 15 significant digits of approxValue(double), so that decimal arithmetic on it (rounding to a number of places, or to a multiple of a step) gives the answer Excel gives. 0.1*3 is 0.3 rather than 0.30000000000000004, and 2.675 is exactly that rather than 2.67499999999999982236431605997495353221893310546875.
      Parameters:
      d - a finite double
      Throws:
      NumberFormatException - if d is NaN or infinite
      Since:
      6.0.0

    • truncate

      public static double truncate(double d)
      Truncates towards zero on the 15-digit view of the value, which is what Excel does with the non-integer arguments of QUOTIENT, GCD, LCM, FACT, FACTDOUBLE, COMBIN, ISEVEN, ISODD and friends ("if number is not an integer, it is truncated"): 2.9999999999999996 and -2.9999999999999996 become 3 and -3, 2.5 becomes 2.
      Returns:
      the truncated value as a double, so that magnitudes beyond the long range work
      Since:
      6.0.0
      See Also:

    • approxAdd

      public static double approxAdd(double a, double b)
      Excel 97 and later compensate for the error introduced by converting decimal operands to binary when an addition or subtraction lands at or very close to zero: if the two magnitudes are equal to Excel (same 15 significant digits, see NumberComparer), the result is exactly zero.
      Returns:
      a + b, or exactly 0 when Excel would cancel the operands

    • approxSub

      public static double approxSub(double a, double b)
      Returns:
      a - b, or exactly 0 when Excel would cancel the operands
      See Also: