How to Use STEYX Function in Google Sheets

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.

The STEYX Function in Google Sheets gets the absolute distance from all data points to the regression line

 

With the 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:

STEYX(data_y, data_x)

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 STEYX function. 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. 

Using STEYX Function in Google Sheets to compare two regressions

 

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:

=STEYX(A2:A10,B2:B10)

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.

  1. First, select the cell that will hold the result of our STEYX function. We’ll be using cell D2 in this example.
    Select the cell to place the STEYX function
  2. To start our function, we must type the equal sign ‘=‘ , followed by ‘STEYX(‘. 
  3. Our first argument requires the range that contains the dependent variable or the y variable. Column B contains our dependent variable in this dataset.
    Select the dependent variable as the first argument
  4. Next, we must then select the range that contains our independent variable.
    Select the independent variable for the STEYX Function in Google Sheets
  5. 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.
    Final result shows the standard error

 

 

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. 

The 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 RSQ or 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!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like