• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1279
  • Last Modified:

Standard error of Y (linear regression) calculation in Excel

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
NAB015
Asked:
NAB015
  • 2
1 Solution
 
DEVLitehouseCommented:
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
 
byundtCommented:
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
 
NAB015Author Commented:
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
 
NAB015Author Commented:
Thank you.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now