Syntax. STEYX(known_y’s,known_x’s)
Definition. This function returns the standard error of the predicted y-values for each x in the regression. The standard error is a measure of the amount of error in the prediction of a y-value associated with an x-value.
Arguments
known_y’s (required). An array or a range of dependent data points
known_x’s (required). An array or a range of independent data points
Note
Arguments can be numbers, names, arrays, or references containing numbers.
If an array or a reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value 0 are included.
If known_y’s and known_x’s are empty or have a different number of data points, the STEYX() function returns the #N/A error value.
Background. The standard error is the variance of the sample values around the actual value of the calculated parameter in the population. The higher the standard error is, the larger the confidence interval containing the parameter with a given probability will be.
The size of the standard error depends on the variance of the values in the population. The lower the variance is, the lower the standard errors in the sample will be. In general, the standard error decreases proportional to the square root of the sample size. This means that you have to quadruple the sample size to divide a standard error in half.
The STEYX() function returns the estimated standard error of a linear regression and indicates the reliability of the linear regression. The higher the standard error is, the higher the deviation of the estimated values will be from the values in the population.
Usually you specify a cell range for the known_y’s and known_x’s arguments. The known_y’s argument contains dependent variables, and the known_x’s argument contains independent variables.
This function expects known_y’s first and then known_x’s—not the other way around.
If the population is normal distributed with μ and σ, the following is true for the estimate function from the means of multiple samples n:
It is normal distributed.
The mean is μ.
The variance of the sample distribution in a standard deviation is called the standard error of the mean.
The standard error defines the standard deviation of all sample means from the assumed mean of the population. If the variance of different sample means is approximately 0, the standard error is also approximately 0. If the variance is large, the standard error will also be large. The larger the sample size, the lower the standard error.
The equation to calculate the standard error of an expected y-value is:
The values x and y are the sample means AVERAGE(array1) and AVERAGE(array2), and n is the sample size.
Example. We use the example of the software company to explain the STEYX() function. The company sells all its products through its internal website. The company was founded 10 years ago. Although the website and online order are also available for 10 years, the number of website visits has been recorded only for the past eight years. The online orders are not recorded in a separate database.
You are the marketing manager and have the numbers for the last 2.5 years. You want to further analyze this data. You have entered the number of website visits and online orders per month in Excel.
The online orders depend on the website visits. The means that the higher the number of website visits the more online orders are placed. You also calculated the mean for both components. For the dependent online orders (y-values), the mean is 1,121 between July 2007 and June 2008 (see Figure 12-137).
Now you want to know how realistic the calculated sample mean is for the expected online orders. The question is: How high is the standard error of the estimated online orders for all website visits? To answer this, you use the STEYX() function. Figure 12-138 shows the result.
As you can see in the figure, the standard error is 210.07.
Based on these results, you can draw the following conclusion: The result of 210.07 indicates that the sample mean of the online orders for all website visits is dispersed by 210 orders around the mean.
The result of 210.07 is the size of the error of the expected online orders depending on the website visits.