Weighted least squares linear regression in excel? - GoFuckYourself.com

Rhesus · 03-17-2007, 02:19 PM

I want to draw a best fitting straight line through a cloud of data points by least squares linear regression, but I want to weigh the error between the straight line and actual data points for more distant points exponentially less heavily than for the most recent point.

As far as I know, excel doesn't have this function built-in, does anyone have any creative ideas on how to overcome this manco?

Thanks a lot!

uno · 03-17-2007, 02:23 PM

Graphing calculator?

Rhesus · 03-17-2007, 02:27 PM

If excel doesn't support it, my graphing calc definitely won't. Furthermore, I want to see actual numbers and coefficients so I'm afraid I'm looking for something else. Or isn't that what you mean?

uno · 03-17-2007, 02:31 PM

I may have passed all my economics classes and business Calculus II but the terms are all still greek to me.

Rhesus · 03-17-2007, 02:32 PM

To make things clearer: TREND and LINEST are functions that can be used to be used for least sq. linear regression, but in the classic way errors at all points in the x axis are given equal weight. (the sum of the squares all these errors is optimised (/minimised) to find the best coefficient)

WiredGuy · 03-17-2007, 02:32 PM

Reading that brought back some horrid memories of statistical analysis classes back from University.
WG

Rhesus · 03-17-2007, 05:34 PM

Any bright ideas?

OzMan · 03-17-2007, 07:17 PM

Try the curve fitting toolbox from MathWorks

woj · 03-17-2007, 07:26 PM

Quote:

Originally Posted by OzMan

Try the curve fitting toolbox from MathWorks

matlab rules for technical stuff like that

bonkerz2007 · 03-17-2007, 09:14 PM

Constructing a Least-Squares Graph Using Microsoft Excel

Rhesus · 03-18-2007, 03:33 AM

I have matlab yes, and undoubtedly I can just manually program it, but I was looking to do it in excel.

Bonkerz, I know how to draw a line, but I want to weigh recent points more heavily...

03-17-2007, 02:19 PM	#1
Rhesus Confirmed User Join Date: Aug 2004 Posts: 2,009	Weighted least squares linear regression in excel? I want to draw a best fitting straight line through a cloud of data points by least squares linear regression, but I want to weigh the error between the straight line and actual data points for more distant points exponentially less heavily than for the most recent point. As far as I know, excel doesn't have this function built-in, does anyone have any creative ideas on how to overcome this manco? Thanks a lot!

03-17-2007, 02:23 PM	#2
uno RIP Dodger. BEST.CAT.EVER Industry Role: Join Date: Dec 2002 Location: NYC Area Posts: 18,450	Graphing calculator? __________________ -uno icq: 111-914 CrazyBabe.com - porn art MojoHost - For all your hosting needs, present and future. Tell them I sent ya!

03-17-2007, 02:31 PM	#4
uno RIP Dodger. BEST.CAT.EVER Industry Role: Join Date: Dec 2002 Location: NYC Area Posts: 18,450	I may have passed all my economics classes and business Calculus II but the terms are all still greek to me. __________________ -uno icq: 111-914 CrazyBabe.com - porn art MojoHost - For all your hosting needs, present and future. Tell them I sent ya!

03-17-2007, 02:32 PM	#6
WiredGuy Pounding Googlebot Industry Role: Join Date: Aug 2002 Location: Canada Posts: 34,456	Reading that brought back some horrid memories of statistical analysis classes back from University. WG __________________ I play with Google.

03-17-2007, 02:27 PM	#3
Rhesus Confirmed User Join Date: Aug 2004 Posts: 2,009	If excel doesn't support it, my graphing calc definitely won't. Furthermore, I want to see actual numbers and coefficients so I'm afraid I'm looking for something else. Or isn't that what you mean?

03-17-2007, 05:34 PM	#7
Rhesus Confirmed User Join Date: Aug 2004 Posts: 2,009	Any bright ideas?

03-17-2007, 07:17 PM	#8
OzMan Confirmed User Join Date: Sep 2003 Location: Los Begas Posts: 9,162	Try the curve fitting toolbox from MathWorks

03-17-2007, 09:14 PM	#10
bonkerz2007 Confirmed User Join Date: Sep 2005 Posts: 794	Constructing a Least-Squares Graph Using Microsoft Excel

03-18-2007, 03:33 AM	#11
Rhesus Confirmed User Join Date: Aug 2004 Posts: 2,009	I have matlab yes, and undoubtedly I can just manually program it, but I was looking to do it in excel. Bonkerz, I know how to draw a line, but I want to weigh recent points more heavily...