STEYX

Fits a straight line to data using linear regression and returns the standard error of y values.

Syntax:

STEYX(yvalues, xvalues)

yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.

STEYX in effect fits a straight line through these data points, using the linear regression method (least squares). It then returns the standard error of actual y values compared to y values on the straight line found.

The equation of a straight line may be given as y = a + bx. The linear regression method calculates:

$b = \frac{\sum ( x _{i} - x ) ( y _{i} - y )}{\sum ( x _{i} - x ) ^{2}}$

and

$a = \overline{y} - b \overline{x}$

STEYX calculates:

$\frac{\sum ( y _{i} - y _{i}^{^{'}} ) ^{2}}{n - 2}$

where $y_{i}$ are the actual values and $y_{i}^{^{'}}$ are the computed values on the line.

Example:

STEYX(B2:B6, A2:A6)

where the x values in A2:A6 are 1, 2, 3, 4, 5 and the y values in B2:B6 are 2, 4, 7, 8, 10 returns approximately 0.51. The equation of the straight line found is very nearly y = 2x, and would be if B4 contained 6. The standard error is therefore quite small.

Application:

Scenario: A company that sells ice cream wants to understand the relationship between daily high temperatures and the number of ice cream cones sold. They have collected data for two weeks and want to predict future sales based on temperature.

Data:

	Day	Temperature (°F)	Ice Cream Cones Sold
	A	B	C
1	1	70	120
2	2	75	150
3	3	80	180
4	4	72	130
5	5	78	165
6	6	82	190
7	7	85	200
8	8	76	155
9	9	81	185
10	10	84	195
11	11	79	170
12	12	73	140
13	13	83	192
14	14	77	160

Analysis:

The company wants to perform a linear regression to predict the number of ice cream cones sold (the y-variable) based on the temperature (the x-variable). The STEYX function will help them determine how accurate this prediction model is.

Let's assume the data is in a spreadsheet:

Column B: Temperature (x-values)
Column C: Ice Cream Cones Sold (y-values)

The formula would be: STEYX(C1:C14, B1:B14)

Calculation:

After entering the formula in a cell, the spreadsheet program will calculate the standard error of the regression. The result is approximately 2.43.

Interpretation:

The STEYX value of 2.43 means that on average, the predicted number of ice cream cones sold will deviate by about 2.43 from the actual number of cones sold. This value represents the standard deviation of the error term in the regression analysis.

A low STEYX value (like 2.43 in this case) indicates that the temperature is a good predictor of ice cream sales. The data points are tightly clustered around the regression line, and the model's predictions are relatively accurate. If the STEYX value were much higher, say 25, it would suggest that the model is a poor fit and the predictions are not very reliable.

Result for STEYX(C1:C14, B1:B14):

2.427381721

	Day	Temperature (°F)	Ice Cream Cones Sold
	A	B	C
1	1	70	120
2	2	75	150
3	3	80	180
4	4	72	130
5	5	78	165
6	6	82	190
7	7	85	200
8	8	76	155
9	9	81	185
10	10	84	195
11	11	79	170
12	12	73	140
13	13	83	192
14	14	77	160

	Day	Temperature (°F)	Ice Cream Cones Sold
	A	B	C
1	1	70	120
2	2	75	150
3	3	80	180
4	4	72	130
5	5	78	165
6	6	82	190
7	7	85	200
8	8	76	155
9	9	81	185
10	10	84	195
11	11	79	170
12	12	73	140
13	13	83	192
14	14	77	160

	Day	Temperature (°F)	Ice Cream Cones Sold
	A	B	C
1	1	70	120
2	2	75	150
3	3	80	180
4	4	72	130
5	5	78	165
6	6	82	190
7	7	85	200
8	8	76	155
9	9	81	185
10	10	84	195
11	11	79	170
12	12	73	140
13	13	83	192
14	14	77	160