STEYX function

The STEYX function in Excel calculates the standard error of the predicted y-value for each x in a regression analysis. It is used in the context of linear regression to measure the accuracy of the predicted y-values from a linear trendline based on a set of known x and y values.

The standard error is a measure of the spread or variability of the residuals (the differences between the observed y-values and the predicted y-values), and the STEYX function helps to assess how well a regression line fits the data.

Syntax

=STEYX(known_y's, known_x's)

Parameters

1. known_y’s (required): An array or range of dependent (y) values.
2. known_x’s (required): An array or range of independent (x) values.

Key Points

• Linear Regression: The STEYX function is based on linear regression, where the relationship between x and y is modeled by a straight line.
• Error Measurement: The result tells you the typical distance that the observed y-values deviate from the regression line (predicted y-values). A smaller value indicates a better fit.
• Residuals: The difference between an actual y-value and the predicted y-value is called a residual. The STEYX function calculates the standard error of these residuals.

Formula

The STEYX function is calculated using the formula for the standard error of the regression line:

$$SE = \sqrt{\frac{\sum (y_i – \hat{y}_i)^2}{n – 2}}$$

Where:

• $y_i$ are the actual y-values,
• $\hat{y}_i$ are the predicted y-values from the regression line,
• $n$ is the number of data points.

Example

1. Basic Regression Standard Error

Given a set of data in columns A (x-values) and B (y-values), where:

• A1:A5 contains {1, 2, 3, 4, 5} (x-values),
• B1:B5 contains {2, 4, 5, 4, 5} (y-values).

To calculate the standard error of the predicted y-values:

=STEYX(B1:B5, A1:A5)

Result: 0.8944 (approx.)

• This value represents the standard error of the y-values predicted by the linear regression line. It indicates how much the actual y-values deviate from the predicted values.

2. Using a Larger Dataset

For a larger dataset, you can use STEYX in the same way. For example, if the x-values are in C1:C10 and the corresponding y-values are in D1:D10, the formula would be:

=STEYX(D1:D10, C1:C10)

Result: The calculated standard error will reflect the variability of the predicted y-values for this larger dataset.

3. Visualizing with a Scatter Plot

After calculating the standard error using STEYX, you can visualize the data and the regression line in a scatter plot. The standard error gives you an indication of how closely the points follow the regression line.

Notes

• Interpretation: A smaller standard error indicates that the predicted y-values are very close to the actual y-values (good fit). A larger standard error indicates a worse fit.
• Error Handling: If there is only one data point or if the x and y ranges are not of equal length, the STEYX function will return an error (#DIV/0!).
• Use Cases:
  • STEYX is useful in regression analysis and linear trend forecasting, where you need to evaluate how much error or variability exists between the predicted and actual y-values.
  • It is commonly used in statistical analysis, business forecasting, and scientific research where linear relationships between variables are being studied.

Related Functions

• LINEST: Calculates the statistics for a linear regression, including slope, intercept, and more.
• TREND: Returns values along a linear trend based on known x and y values.
• RSQ: Calculates the square of the correlation coefficient for the regression line, indicating how well the data fits the model.
• SLOPE: Returns the slope of the linear regression line.
• INTERCEPT: Returns the y-intercept of the regression line.

The STEYX function is valuable when performing regression analysis to understand the precision and accuracy of your predictions. It provides a numerical measurement of the error in the prediction, which can help to assess the reliability of the regression model.