Documentation/Calc Functions/RAWSUBTRACT

    From The Document Foundation Wiki


    Function name:

    RAWSUBTRACT

    Category:

    Mathematical

    Summary:

    Subtracts numbers without eliminating small roundoff errors.

    Note pin.svg

    Note
    For many users, Calc's minus (-) subtraction operator does exactly what they need, cancelling out any small roundoff errors that arise because not every decimal number can be exactly represented as a binary floating-point number. Use RAWSUBTRACT if you do not want those small roundoff errors to be eliminated.

    Syntax:

    RAWSUBTRACT(Minuend; Subtrahend 1[; Subtrahend 2[; … ;[ Subtrahend 254]]])

    Returns:

    Returns a real number that is the result of subtracting the various subtrahend arguments from the minuend, without eliminating roundoff errors.

    Arguments:

    Minuend is a real number, or a reference to a cell containing a real number, from which other numbers are to be subtracted.

    Subtrahend 1, Subtrahend 2, …, Subtrahend 254 give the set of real numbers that are to be subtracted from Minuend. Each argument may be a real number, an expression that evaluates to a real number, or a reference to a single cell containing a real number.

    • If any argument is a string in quotation marks, then RAWSUBTRACT reports a #VALUE! error.
    • If any argument is a reference to a cell that contains non-numeric data, then RAWSUBTRACT reports a #VALUE! error.
    • If any argument is a reference to a cell that is empty, then RAWSUBTRACT treats it as containing 0.

    Additional details:

    • Consider the simple Calc formula =0.3-0.2-0.1 which evaluates to 0. Most people would intuitively expect this formula to return 0. However, behind the scenes, things aren’t so clear-cut because not every decimal number can be exactly represented as a binary floating-point number. This means that small roundoff errors can creep into the calculations but, to meet the needs of most users, the subtraction operator eliminates any such errors that arise.
    • RAWSUBTRACT uses the IEEE Standard for Floating-Point Arithmetic (IEEE 754) subtraction operation that the platform/compiler provides. The formula =RAWSUBTRACT(0.3; 0.2; 0.1) does not return 0 but instead returns the value -2.77555756156289E-17. This demonstrates that a small roundoff error has arisen that would be of no significance to many users. However, for some applications visibility of this error could be useful, for example, when determining whether two values are identical.
      Note pin.svg

      Note:
      To get this result you may need to uncheck the Limit decimals for general number format option in the General Calculations area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS).

      • RAWSUBTRACT can be used to compare whether a result exactly matches an expected number, that is, if =RAWSUBTRACT(result; expected) is equal to 0.0.
      • RAWSUBTRACT processes arguments from left to right. For example, =RAWSUBTRACT(1; 2; 3; 4) calculates 1-2-3-4 or ((1-2)-3)-4 in "natural" order.

      Examples:

      Formula Description Returns
      =RAWSUBTRACT(10; 3; 2; 1) Here the function produces the same result as the subtraction operator. 4
      =RAWSUBTRACT(D1; D2; D3) where cells D1, D2 and D3 contain the numbers 1.6, 1.2, and 0.4 respectively. Here the function produces a non-zero result that shows a small roundoff error. 1.11022302462516E−16
      =RAWSUBTRACT(0.987654321098765; 0.9876543210987) Here the function is used to determine the difference between two numbers, producing the same result as the subtraction operator. 6.50590692430342E−14

      Related LibreOffice functions:

      None.

      ODF standard:

      None.

      Related (or similar) Excel functions:

      None.