Solved

Standard error of Y (linear regression) calculation in Excel

Posted on 2010-11-12
5
1,255 Views
Last Modified: 2012-05-10
Hello Experts,

I was wondering if there is an excel function or add in that makes it easy to calculate the SE of Y?
I have been unable to find one online but I imagine that a macro function to calculate it would be possible.

Thanks.
0
Comment
Question by:NAB015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 3

Expert Comment

by:DEVLitehouse
ID: 34122873
I was able to find a tutorial on this. Hope it's what you're looking for.
http://phoenix.phys.clemson.edu/tutorials/excel/regression.html
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 34123265
If you look at the LINEST function in the on-line help, it is capable of returning both the regression coefficients as well as the standard error of the y estimate.

1) Select a block of cells with 5 rows and n+1 columns, where n is the number of independent variables. The next step assumes you select two columns (1 independent variable).
2) Array-enter a formula like:
=LINEST(B2:B20,A2:A20,TRUE,TRUE)                  B2:B20 is the y-values, A2:A20 is the x-values
To array-enter the formula, hold the Control and Shift keys down, then hit Enter. Release all three keys. Excel will respond by adding curly braces around the formula.

The second value in the third row is the standard error of the y-estimate.

The STEYX function will return the standard error of the y-estimate directly.

The sample workbook shows an example of both LINEST and STEYX
StandardErrorQ26611423-.xlsx
0
 

Author Comment

by:NAB015
ID: 34128481
DEV: Your link was not very helpful. But thank you for your effort.

Byundt. I can't seem to get this to work with multiple X's.
Maybe you could provide an example that uses multiple x's.

Thank you for both of your work.

0
 

Author Closing Comment

by:NAB015
ID: 34131070
Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question