# Linear Regresion In Spreadsheed Or Statistics

### #1

Posted 15 January 2007 - 07:43 PM

### #2

Posted 16 January 2007 - 01:31 AM

### #3

Posted 16 January 2007 - 05:54 PM

I need Linear reg passing a certain (x,y) not only (0,0).

### #4

Posted 16 January 2007 - 07:13 PM

Sorry, but I don't understand your question. Could you explain more?

Ok you imagin that we have some points (x,y) and we want to make a linear regresion with this points, you obtain y = 6,9 x + 9*10^-2 and you know that this function f(x) necessarily for x=0 -> y=0. This function muts pass trought point (0,0).

9*10^-2 it's small value but some times it's better obtain a regresion like: y=a*x; a=cte; with out b (origin of coordinates), I know that it's possible with exel, but with CP?

One solution is add much points (0,0) to the list of regresion, but I look for a more pretty solution.

### #5

Posted 16 January 2007 - 10:21 PM

Girdeux, you know how least squares method works (minimos cuadrados )?, seems to be you not.... You can read it on a Numerical Methods book (As Chapra-Canale or Burden-Faires). Well, PAP is the pro of the numerical methods here, wait for him

### #6

Posted 17 January 2007 - 08:04 AM

Wow, I know something about numerical methods, but I never thought I'm a "pro"....Well, PAP is the pro of the numerical methods here, wait for him

First of all, I have to say that I don't use built-in linear regression much (in fact, not at all). However, linear regression uses the least squares method in order to obtain a "best" fitting curve f(x)=a*x+b. In other words, you don't have control over the fitting function, so you can't make it pass from 0,0 (b=0). Adding a data point (0,0) will not help in most cases, since the fitting curve don't necessarily pass from a data point.Ok you imagin that we have some points (x,y) and we want to make a linear regresion with this points, you obtain y = 6,9 x + 9*10^-2 and you know that this function f(x) necessarily for x=0 -> y=0. This function muts pass trought point (0,0).

There is a fast solution, but you need to use CPLua and LNA for this: use my LNA function LMFit, with f(x)=a*x as a model function, instead of f(x)=a*x+b. LMFit will return the correct value for the slope, a, and the fitting curve will pass from 0,0 (not near this point). If using CPLua and LNA is not a problem for you, try it (it's easy, just modify the example program XLMFit). If you have problems, send me your data, and I will post a small CPLua program that uses LNA to do what you want .

### #7

Posted 17 January 2007 - 01:33 PM

### #8

Posted 22 January 2007 - 08:31 PM

It's all ok, but I get a bad R^2, for lines that cross the origin. I use this ecuations, can someone tell me if they are bad or if I should use other ecuations?

n = number of points mx=mean(xi) my=mean(yi) mxy=mean(xi*yi) mx2=mean(xi^2) sy2=sum( (yi-my)^2 )/n sxy2=sum( (xi-mx)*(yi-my) )/n b=mxy/mx2 R^2=b*sxy2/sy2

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users