Kako koristiti funkciju SUMXMY2 u Excelu
Detailed Guide on the SUMXMY2 Function for Calculating Sum of Squared Differences
The SUMXMY2 function is an excellent tool in Excel and Google Sheets for calculating the sum of squared differences between corresponding values in two data sets. This function is particularly useful when you need to analyze differences between two sets of data, such as forecasted versus actual results.
Syntax and Examples
The syntax for the function is as follows:
SUMXMY2(array1, array2)
Where array1 and array2 are arrays or ranges of data you wish to compare.
Example: Consider two data arrays, A1:A3 and B1:B3, with the values:
- A1:A3 = {2, 3, 4}
- B1:B3 = {5, 6, 2}
The command for computation would look like this:
SUMXMY2(A1:A3, B1:B3)
In this case, the function computes ((2-5)^2 + (3-6)^2 + (4-2)^2), yielding a result of 26.
Practical Applications
Comparing Actual and Forecasted Sales
Imagine you have data on forecasted and actual sales for the last quarter. The range A2:A5 contains the forecasted sales and B2:B5 contains the actual values:
| Month | Forecast (A) | Actual (B) |
|---|---|---|
| January | 200 | 180 |
| February | 150 | 160 |
| March | 130 | 125 |
| April | 170 | 180 |
The calculation using SUMXMY2 would look like this:
SUMXMY2(A2:A5, B2:B5)
The result (200-180)^2 + (150-160)^2 + (130-125)^2 + (170-180)^2 = 400 + 100 + 25 + 100 = 625, which shows the total extent of discrepancies between forecasts and actual figures.
Analyzing Changes in Pollution Levels
Suppose you have data from two pollution measurements at different locations. The data for times T1 and T2 are in ranges C2:C6 and D2:D6:
| Location | T1 (C) | T2 (D) |
|---|---|---|
| 1 | 23 | 25 |
| 2 | 18 | 15 |
| 3 | 30 | 35 |
| 4 | 22 | 20 |
| 5 | 25 | 25 |
By applying the SUMXMY2 function, we get:
SUMXMY2(C2:C6, D2:D6)
The calculation (23-25)^2 + (18-15)^2 + (30-35)^2 + (22-20)^2 + (25-25)^2 = 4 + 9 + 25 + 4 + 0 = 42. This result provides a quantitative measure of changes in pollution levels between the two measurements.
This HTML code provides a comprehensive guide on using the SUMXMY2 function in Excel and Google Sheets, detailing syntax, examples, and practical applications for two different scenarios. The clear, structured format enhanced by HTML tags facilitates user engagement and understanding.
Více informací: https://support.microsoft.com/cs-cz/office/funkce-sumxmy2-9d144ac1-4d79-43de-b524-e2ecee23b299