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

Další funkce
Vrátí arkustangens čísla
Zaokrouhlí číslo na nejbližší celé číslo nebo na nejbližší násobek zadané hodnoty Číslo bude zaokrouhleno nahoru bez ohledu na jeho znaménko
Přiřadí názvy výsledkům výpočtu a umožní tak ukládání mezilehlých výpočtů, hodnot nebo definování názvů ve vzorci
Vrátí nejmenší společný násobek
Vrátí přirozený logaritmus čísla
Vrátí dekadický logaritmus čísla
Vrátí logaritmus čísla při zadaném základu