Quick Navigation
SUMXMY2 Function
Summary
The Excel SUMXMY2 function calculates the sum of the squares of differences between corresponding values in two arrays. This is a statistical function commonly used in regression analysis and data comparison tasks.
Syntax
SUMXMY2(array_x, array_y)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array_x | Array/Range |
Yes | The first array or range containing numeric values. Required. |
| array_y | Array/Range |
Yes | The second array or range containing numeric values. Must have same number of elements as array_x. Required. |
Using the SUMXMY2 Function
SUMXMY2 is particularly useful in statistical analysis for calculating the sum of squared differences between observed and expected values, forming the basis for many regression calculations and error analysis.
Common SUMXMY2 Examples
Basic Range Calculation
=SUMXMY2(A2:A8,B2:B8)Calculates sum of squared differences between two columns of data (result: 79)
Array Constants Example
=SUMXMY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})Uses inline array constants to compute sum of squared differences (result: 79)
Regression Error Analysis
=SUMXMY2(observed_range, predicted_range)Measures total squared error between observed and predicted values in regression analysis
Frequently Asked Questions
Common Errors and Solutions
#N/A Error
Cause: Arrays have different numbers of elements
Solution: Ensure both arrays contain exactly the same number of values
#VALUE! Error
Cause: Arrays contain incompatible data types
Solution: Verify both arrays contain only numeric values or acceptable references
Unexpected results
Cause: Text or logical values in arrays
Solution: Clean data or use numeric-only ranges
Notes
- Calculates Σ(array_x[i] - array_y[i])² for corresponding pairs
- Zeros are included in calculations
- Perfect companion to functions like SLOPE, INTERCEPT for regression
- Available since Excel 2007
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+