The following article introduces you to the STEYX function - one of the functions in the statistical function group is very popular in Excel.
Description: The function returns the standard error of the predicted value of y for each value of x in the regression.
Syntax: STEYX (known_y's, known_x's)
Inside:
- known_y's: Array or range of dependent data points, is a required parameter.
- known_x's: An independent data point or range, which is a required parameter.
Attention:
- Arguments can be numbers, names or arrays that contain numbers.
- When typing logical values and presenting text numbers into function arguments -> these values are still counted, but cells with 0 value will still be counted.
- Function error occurs when arguments are text or error values cannot be converted.
- If known_y's and known_x's have a different number of data points -> the function returns the # N / A error value .
- If known_y's and known_x's are empty or have fewer than three data points -> the function returns the # DIV / 0 error value .
- The equation for standard errors is:
[sqrt {frac {1} {{left ({n - 2} right)}} left [{sum {{{left ({y - overline y} right)} ^ 2} - {{frac {{left [{ sum {left ({x - overline x} right) left ({y - overline y} right)}} right]}} {{sum {{{left ({x - overline x} right)} ^ 2}}} }} ^ 2}}} right]}]
Inside:
+ x is the sample mean AVERAGE (number1, number2, .)
+ n is the sample size.
For example:
Calculate the standard error of the predicted value of y for each value of x in the regression of the values in the following data table:
- In the cell to calculate enter the formula : = STEYX (C6: C12, D6: D12)
- Press Enter -> the standard error of the predicted y value for each value of x in the regression is:
- If the number of data points of know_y's and know_x's are different -> the function returns the # N / A error value .
- If the number of data points of know_y's and know_x's is smaller than 3 data points -> the function returns the # DIV / 0 error value .
Above are instructions and some specific examples when using the STEYX function in Excel.
Good luck!