least squares method in excel

[airsick]

I have a set of y values and their corresponding x values.
I want a function that will take those values as input and return the a and b values of the y=ax+b equasion of the straight line that occurs using the least squares method.

I tried to use both TREND and LINEST functions but with no success.

Thank you very much

 

[Nikita6003]

Hi,

maybe this link is of some use

http://www.sccj.net/publications/JCCJ/v1n2/a12/document.pdf

Hoe it helps

Cheers
Nikki

 

[Zathras]

Here is a set of User Defined Functions that can probably do the job you require (paste into a module):

Option Explicit

Function LinRegSlope(xValues As Range, yValues As Range) As Double
' Returns the "a" value from the formula Y = aX + b for the
' "best fit" of a straight line to pairs of X and Y values.
Dim S As Double, I As Double, RegressCoeff As Double
LinReg xValues, yValues, S, I, RegressCoeff
LinRegSlope = S
End Function

Function LinRegIntercept(xValues As Range, yValues As Range) As Double
' Returns the "b" value from the formula Y = aX + b for the
' "best fit" of a straight line to pairs of X and Y values.
Dim S As Double, I As Double, RegressCoeff As Double
LinReg xValues, yValues, S, I, RegressCoeff
LinRegIntercept = I
End Function

Function LinRegCoefficient(xValues As Range, yValues As Range) As Double
' Returns the coefficient of correlation ("R Squared&quot for
' the formula Y = aX + b for the "best fit" of a straight line
' to pairs of X and Y values.
' Perfect correlation is 1, total non-correlation is 0.
Dim S As Double, I As Double, RegressCoeff As Double
LinReg xValues, yValues, S, I, RegressCoeff
LinRegCoefficient = RegressCoeff
End Function

Private Sub LinReg(xValues As Range, yValues As Range, _
Slope As Double, Intercept As Double, Coefficient As Double)
Dim I As Double
Dim N As Double
Dim XArray() As Double
Dim YArray() As Double
Dim X As Double
Dim Y As Double
Dim SumX As Double
Dim SumY As Double
Dim SumXSquared As Double
Dim SumYSquared As Double
Dim SumXY As Double
Dim C As Range

I = 0
ReDim XArray(xValues.Count - 1)
For Each C In xValues
XArray(I) = C.Value
I = I + 1
Next
I = 0
ReDim YArray(yValues.Count - 1)
For Each C In yValues
YArray(I) = C.Value
I = I + 1
Next

N = 0
For I = LBound(XArray) To UBound(XArray)
N = N + 1
X = XArray(I)
Y = YArray(I)
SumX = SumX + X
SumY = SumY + Y
SumXSquared = SumXSquared + X * X
SumYSquared = SumYSquared + Y * Y
SumXY = SumXY + X * Y
Next
Slope = (SumXY - SumX * SumY / N) / (SumXSquared - SumX * SumX / N)
Intercept = (SumY - Slope * SumX) / N
Coefficient = Slope * ((SumXY - SumX * SumY / N) / (SumYSquared - SumY * SumY / N))
End Sub