The STEYX function in Google Sheets is useful when you need to find the standard error of the predicted y-value for each x in a regression of a dataset.
The standard error of the regression gives you an absolute measure of the typical distance the data fall from the regression line. It is a type of goodness-of-fit measure that can help determine how well your regression models your data.
The rules for using the
STEYX function in Google Sheets are as follows:
- The function requires only two arguments: data_y and data_x. These correspond to the dependent and independent variables of your regression.
- The function outputs a numerical value corresponding to the standard error of your regression.
Regression is the most common way to find trends in your data. Using regression on two variables allows you to model their relationship and predict future values. But how do we find out if our model fits our data well?
The goodness of fit test is designed to measure how well your model fits your observed data. Two of the most common tests for linear regression include the standard error test and the r-squared test. The former is an absolute measure, while the latter is relative.
The standard error of a regression is computed by finding the average distance between the actual observed values and the values predicted by the regression model.
In the chart below, you can see how the regression aims to minimize the average distance between the data points and the regression line. If a linear regression returns a high standard error, then you might want to consider using another type of statistical model.
STEYX function, it becomes quite easy to perform this check and determine how useful or accurate your regression model might be.
Let’s learn how to write the
STEYX function ourselves in the next section.
The Anatomy of the STEYX Function
The syntax of the
STEYX function is as follows:
Let’s look at each term in this formula to understand what they mean.
- = the equal sign signals the start of a function in Google Sheets.
- STEYX() is our
STEYXfunction. It computes the standard error for a prediction y for all values of x in a regression.
- data_y refers to the range that holds our dependent data.
- data_y refers to the range that holds our independent data.
- All text encountered in the arguments will be ignored in the computation.
A Real Example of Using STEYX Function
Let’s look at an example of the
STEYX function being used in a Google Sheets spreadsheet.
Our first example shows how we can use the standard error to determine whether a linear regression is appropriate for our dataset. The standard error for our first dataset is 2.04, while our standard error for the second dataset is 8.64.
The standard error is helpful if you already want to know how accurate your model can be in terms of absolute units. We can use the output of
STEYX to create a prediction interval in terms of units rather than percentages. The prediction interval is the expected range where a future value may fall.
The regression of the second dataset will be rejected if our requirement is a prediction interval of less than 3 units.
To get the values in Column C2, we just need to use the following formula:
You can make your copy of the spreadsheet above using the link attached below.
If you’re ready to try out the
STEYX function in Google Sheets, let’s start writing it in the next section.
How to Use STEYX Function in Google Sheets
We’ll go through each step needed to start using the
STEYX function in Google Sheets. This section will explain how we can find the standard error of regression when given a dataset of dependent and independent variables.
- First, select the cell that will hold the result of our
STEYXfunction. We’ll be using cell D2 in this example.
- To start our function, we must type the equal sign ‘=‘ , followed by ‘STEYX(‘.
- Our first argument requires the range that contains the dependent variable or the y variable. Column B contains our dependent variable in this dataset.
- Next, we must then select the range that contains our independent variable.
- After typing our arguments, we can hit the Enter key to evaluate the function. We can see now that the standard error of our regression is 1.88 units.
That’s all you need to remember to start using the
STEYX function in Google Sheets. Finding out the standard error of your regression is an important metric when performing a regression.
STEYX function is one of the dozens of statistical functions you can use in Google Sheets. If you’re interested in another form of regression analysis, you can look into the
CORREL functions as well. With so many other Google Sheets functions out there, you can surely find one that suits your use case.
Are you interested in learning more about what Google Sheets can do? Stay notified of new guides like this by subscribing to our newsletter!