What Is STEYX Function in Google Sheets?
The STEYX function in Google Sheets calculates the standard error of the predicted y-values for each value of x in a regression analysis. Analysts use it when using linear regression to understand the accuracy of the predictions. The function STEYX is an abbreviation of the Standard Error of the Y-estimate. In simple terms, it measures the accuracy of the predictions made using a linear regression model. Suppose you have the daily temperature of a city. You wish to predict how the temperature might vary based on records. Here we use STEYX to find how accurate those predictions might be.
One uses STEYX to measure the average distance between the predicted y-values and the actual y-values on the regression line. It is often used in statistical analysis to understand how accurate the regression model is. We have calculated the standard error for the predicted values below using the STEYX function. In Column A, we have the dates and in Column B we have the scores scored. A higher value shows more variability between the actual scores and the predicted scores.
Key Takeaways
- STEYX in Google Sheets is a statistical function that calculates the standard error of the predicted y-values for each value of x in a regression analysis.
- The syntax of the function is as follows: =STEYX(y_values, x_values)
y_values: The range of dependent Y values.
x_values: The range of independent X values.
- We use it when performing linear regression analysis to assess the reliability of a regression model’s predictions.
- Usually, a lower standard error means a better fit for the regression model.
- STEYX is most used in linear regression analysis where one usually has two sets of data points.
Syntax
The syntax for the STEYX function is as follows:
=STEYX(y_values, x_values)
Let us look at the parameters
- y_values: The range of cells containing the dependent variables.
- x_values: The range of cells containing the independent variables.
The output of the function is the standard error of the predicted y-value for each x value in the regression which means that it finds how far
The function returns the standard error of the predicted y-value for each x in the regression. Essentially, it measures how far off the predicted values are from the actual values based on the regression model.
How To Use STEYX Function in Google Sheets?
The STEYX function can be manually entered as a formula in Google Sheets. It can also be entered through the menu bar at the top.
Entering the STEYX Function Manually.
Let us begin by entering our data in Columns A and B. Here, column A represents the independent variable called X, and Column B represents our dependent variable (Y). Here, we enter some scores over days, the day is our X variable, and the temperature would be Y.
- Column A: Contains the dates
- Column B: Contains the temperature.
Step 1: Let us choose the cell where we want the output. Type =STEYX( into the cell.
Step 2: Select the Y range for the first argument. Here, we select B2:B8.
Type a comma and select the X range. For us, that’s A2:A8. Close the parenthesis.
Step 3: Press Enter.
It’s as simple as that! The cell will now display the standard error of the Y-estimate.
Entering the STEYX Through the Menu Bar.
- Go to the Insert tab in Google Sheets and select the option Function and then Statistical.
- From the list of functions, select the STEYX function.
Examples
STEYX in Google Sheets gives an estimate of the average distance that the predicted points deviate from the actual points in the regression line. To understand the practical uses of the function better, let us look at some examples.
Example #1
Here’s a STEYX in Google Sheets example for using the STEYX function in Google Sheets with a student’s results. We’ll calculate the standard error of the predicted scores based on the hours of study.
- Column A has the number of hours studied (independent values).
- Column B has the exam scores of students (dependent values).
Step 1: We want to calculate the standard error of the predicted exam scores based on the number of hours studied. Select an empty cell where you want to calculate the standard error.
=STEYX(C2:C6, B2:B6). Press Enter.
The result will appear in cell B7.
The result is the standard error of the predicted exam scores based on the hours studied. A lower value means the scores are close to the predicted values from the linear regression.
Step 2: With this STEYX result, we can create a scatter plot with a trendline. It will help us view the relationship between both variables.
Here are the steps:
- Select the data range. Here, we select A1:B6.
- Click on Insert in the top menu. Choose Chart.
- In the Chart Editor panel on the right, select Scatter Chart as the chart type.
To add a trendline, go to the Customize tab, select Series, and check the box for Trendline.
This helps you understand the STEYX analysis, to see how well the line fits the data points.
Example #2 – Predict Sales Based on Past Data
A businessman runs a toy shop and wishes to predict his sales based on past sales data.
Step 1: We enter the data in Google Sheets where we enter the independent variables or the month in Column A and the dependent variable or sales value in Column B.
Let us use STEYX to check how accurate the sales predictions are.
Step 2: Let us enter the function in cell B7 as follows:
=STEYX(B2:B6,A2:A6)
Step 3: Press Enter. We get the standard error for your sales forecast.
If the standard error is low, it means that the predictions are very reliable. This helps us plan various aspects like inventory. Ahigh standard error value means that you we have to consider things like promotional events and discounts to improve sales.
Example #3 – Calculate the Standard Error of Stock Returns Based on Market Returns
In this example, we find the standard error of stock returns based on market returns using STEYX. Given below is the table containing the data. Let us look at how to implement the same.
Step 1: Organize the data into two columns,
- Column B: Market returns (x-values)
- Column C: Stock returns (y-values)
Step 2: The STEYX function calculates the standard error of the predicted stock returns based on the market returns. For this, we type the following:
=STEYX(C2:C7, B2:B7)
Here:
- C2:C7 represents the range of stock returns (y-values).
- B2:B7 represents the range of market returns (x-values).
Step 3: Press Enter. We get the standard error of the predicted stock returns based on the market returns. As the formula returns 0.005, this means that on average, the stock returns deviate from the regression line by 0.5%.
Important Things To Note
- One can use STEYX with time series data if the relationship between the independent (x) and dependent (y) variables is somewhat linear.
- A smaller output value from STEYX indicates that the regression line prediction is more correct, while a larger value suggests more unevenness in the predictions.
Frequently Asked Questions (FAQs)
In simpler terms, suppose we have some scattered data points and want to fit them through a straight line to predict future points. Using STEYX, you can estimate how much the predictions will vary from the actual data points. The smaller the standard error calculated using STEYX, the closer are out predictions are to the real data points.
Suppose we have information on the number of hours studied and the corresponding test scores (y-values). Using STEYX, we can determine how much the test scores will for each hour of study vary based on the regression model. For a low standard error, the model’s predicted scores may be close to the actual scores.
A higher STEYX result means that the predicted values are further away from the actual y-values in the regression model. A lower STEYX result shows that the predicted values are closer to the actual values, thereby indicating that the regression model predictions are reliable.
Here are a few functions related to STEYX in Google Sheets.
LINEST – It performs linear regression and returns values such as the slope, intercept, and standard errors.
SLOPE – It returns the slope of the linear regression line based on the given x and y values. Thus, you can calculate the relationship between the independent and dependent variables.
INTERCEPT – The INTERCEPT Function in Google Sheets determines the point where a linear regression line intersects the y-axis. In other words, the intercept is the value of the dependent variable when all the independent variables are zero.
Download Template
This article must help understand STEYX Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is STEYX Function in Google Sheets. We learn the how to use it in linear regression to understand the accuracy of predictions. You can learn more from the following articles. –
Leave a Reply