Including Error on Data in Linear Regression

%3CLINGO-SUB%20id%3D%22lingo-sub-1014047%22%20slang%3D%22en-US%22%3EIncluding%20Error%20on%20Data%20in%20Linear%20Regression%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014047%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20attempting%20to%20do%20a%20statistical%20analysis%20on%20a%20set%20of%20(x%2Cy)%20data%20points%20with%20varying%20horizontal%20and%20vertical%20error%20bars%20in%20Excel%202010.%20I%20need%20a%20line%20of%20best%20fit%20(linear%20regression).%20I%20attempted%20to%20do%20it%20with%20LINEST%2C%20however%20LINEST%20only%20accepts%20x%20and%20y%20values%20as%20arguments%2C%20and%20does%20not%20consider%20error%20bars%20on%20those%20data%20points.%20As%20such%2C%20the%20data%20points%20with%20wide%20error%20bars%20end%20up%20skewing%20the%20line%20of%20best%20fit%20more%20than%20they%20should.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20different%20Excel%202010%20function%2C%20set%20of%20functions%2C%20or%20a%20manual%20input%20that%20will%20allow%20me%20to%20perform%20a%20linear%20regression%20analysis%2C%20that%20gives%20output%20similar%20to%20LINEST%2C%20that%20considers%20the%20error%20on%20the%20data%20points%20it%20accepts%20as%20inputs%2C%20that%20yields%20slope%20and%20intercept%20errors%20that%20are%20calculated%20using%20these%20errors.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1014047%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

I am attempting to do a statistical analysis on a set of (x,y) data points with varying horizontal and vertical error bars in Excel 2010. I need a line of best fit (linear regression). I attempted to do it with LINEST, however LINEST only accepts x and y values as arguments, and does not consider error bars on those data points. As such, the data points with wide error bars end up skewing the line of best fit more than they should. 

 

I need a different Excel 2010 function, set of functions, or a manual input that will allow me to perform a linear regression analysis, that gives output similar to LINEST, that considers the error on the data points it accepts as inputs, that yields slope and intercept errors that are calculated using these errors. 

 

Thank you!

1 Reply
Highlighted
Sounds as if you have multiple measuerments per x-value and you would like the LINEST to work with the data averaged by x-value, rather than with all data is that correct?