Purpose

Return value

Syntax

=SUMXMY2(array_x,array_y)
  • array_x - The first range or array containing numeric values.
  • array_y - The second range or array containing numeric values.

Using the SUMXMY2 function

The Excel SUMXMY2 function returns the sum of squares of differences between corresponding values in two arrays. The “m” in the function name stands for “minus”, as in “sum x minus y squared”.

SUMXMY2 takes two arguments , array_x and array_y . Array_x is the first range or array or range of numbers, and array_y is the second range or array of numbers. Both arguments can be provided as an array constant or as a range .

Examples

=SUMXMY2({0,1},{1,2})// returns 2
=SUMXMY2({1,2,3},{1,2,3}) // returns 0

In the example shown above, the formula in E5 is:

=SUMXMY2(B5:B12,C5:C12)

which returns 28 as a result.

Equation

The equation used to calculate the sum of squares is:

This formula can be created manually in Excel with the exponentiation operator (^) like this:

=SUM((range1-range2)^2)

With the example as shown, the formula below will return the same result as SUMX2MY2:

=SUM((B5:B12-C5:C12)^2) // returns 28

Notes

  • Arguments can be a mix of constants, names, arrays, or references that contain numbers.
  • Text values are ignored, but cells with zero values are included.
  • SUMXMY2 returns #N/A if the arrays contain different numbers of cells.

Purpose

Return value

Syntax

=TRUNC(number,[num_digits])
  • number - The number to truncate.
  • num_digits - [optional] The precision of the truncation (default is 0).

Using the TRUNC function

The TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.

The TRUNC function takes two arguments : number and num_digits . Number is the numeric value to truncate. The num_digits argument is optional and specifies the place at which number should be truncated. Num_digits defaults to zero (0).

Examples

By default, TRUNC will return the integer portion of a number:

=TRUNC(4.9) // returns 4
=TRUNC(-3.5) // returns -3

To control the place at which number is truncated, provide a value for num_digits .

=TRUNC(3.141593) // returns 3
=TRUNC(3.141593,0) // returns 3
=TRUNC(3.141593,1) // returns 3.1
=TRUNC(3.141593,2) // returns 3.14
=TRUNC(3.141593,3) // returns 3.141

When num_digits is negative, the TRUNC function will replace the number at a given place with zero:

=TRUNC(999.99,0) // returns 999
=TRUNC(999.99,-1) // returns 990
=TRUNC(999.99,-2) // returns 900

TRUNC vs. INT

The TRUNC function is similar to the INT function because they both can return the integer part of a number. However, TRUNC simply truncates a number, while INT actually rounds a number down to an integer. With positive numbers, and when TRUNC is using the default of 0 for num_digits, both functions return the same results. With negative numbers, the results can be different. INT(-3.1) returns -4, because INT rounds down to the lower integer. TRUNC(-3.1) returns -3. If you simply want the integer part of a number, you should use TRUNC.

Rounding functions in Excel

Excel provides a number of functions for rounding:

  • To round normally, use the ROUND function .
  • To round to the nearest multiple, use the MROUND function .
  • To round down to the nearest specified place , use the ROUNDDOWN function .
  • To round down to the nearest specified multiple , use the FLOOR function .
  • To round up to the nearest specified place , use the ROUNDUP function .
  • To round up to the nearest specified multiple , use the CEILING function .
  • To round down and return an integer only, use the INT function .
  • To truncate decimal places, use the TRUNC function .